learn.fttgsolutions.com · Cheat Sheets
Connect. Transform. Visualize.
| Power BI Desktop | Free download — powerbi.microsoft.com | The Windows desktop application for building reports and data models. Where you connect data, write DAX, and design visuals. |
| Power BI Service | app.powerbi.com | Cloud-based version of Power BI. Used for report publishing, sharing, scheduling refreshes, and collaboration. |
| Power BI Mobile | iOS / Android app | View and interact with published reports on the go. Supports annotations, alerts, and favorites. |
| Report View | Default view — canvas for building visuals | The main canvas where you create, arrange, and format visualizations. Most report-building happens here. |
| Data View | Table icon in the left sidebar | Shows the raw data tables loaded into the model. Use it to inspect values, verify data types, and review calculated columns. |
| Model View | Diagram icon in the left sidebar | Displays all tables and their relationships. Used to create, edit, and manage how tables connect to each other. |
| Get Data | Home tab → Get Data | Entry point for connecting to any data source — Excel, CSV, SQL Server, SharePoint, web pages, APIs, and more. |
| Load dataset | Get Data → choose source → double-click → Load | Imports the data directly into the model without opening Power Query. Click Transform Data instead to shape data first. |
| Transform Data | Get Data → Transform Data | Opens the Power Query Editor before loading. Use this to clean, reshape, and prepare data before it enters the model. |
| Data view inspection | Click the Data View icon after loading | After loading, click Data View to verify the data was loaded correctly — check row counts, data types, and sample values. |
| Refresh data | Home tab → Refresh | Re-executes all queries to pull the latest data from connected sources. Scheduled refresh is configured in Power BI Service. |
| Create relationship | Model View → drag column from one table to another | Link two tables by dragging a key column onto a matching column in another table. Defines how filters propagate between tables. |
| Open (on load) | Home tab → Get Data → Transform Data | Opens Power Query Editor during the initial data connection flow before the data is loaded into the model. |
| Open (already loaded) | Home tab → Transform Data | Opens Power Query Editor for an already loaded dataset. All existing steps are preserved in the Applied Steps pane. |
| Applied Steps | Right pane in Power Query Editor | Every transformation is recorded as a step. Steps can be renamed, reordered, deleted, or inspected. Click any step to see the state of the data at that point. |
| Remove Rows | Home tab → Remove Rows → choose option | Removes rows based on position or condition: Remove Top Rows, Remove Bottom Rows, Remove Duplicates, Remove Blank Rows, Remove Errors. |
| Add Column | Add Column tab → Custom Column | Creates a new column using an M Code formula. Define the name and expression. The column is computed row by row. |
| Replace Values | Select column → Transform tab → Replace Values | Find and replace a specific value with another value in the selected column. |
| Change Data Type | Click type icon in column header → select type | Sets the column type (Whole Number, Decimal, Text, Date, DateTime, True/False, etc.). Always set types explicitly before loading. |
| Promote Headers | Home tab → Use First Row as Headers | Converts the first data row into the column headers. Common after importing raw CSV or Excel files. |
| Split Column | Transform tab → Split Column → By Delimiter / By Number of Characters | Splits a text column into multiple columns or rows based on a delimiter or character count. |
| Pivot / Unpivot | Select columns → Transform tab → Pivot Column / Unpivot Columns | Pivot rotates unique values of a column into new column headers. Unpivot converts column headers back into rows. |
| Group By | Home tab → Group By | Aggregates data by one or more grouping columns. Equivalent to GROUP BY in SQL or SUMMARIZE in DAX. |
| Merge Queries | Home tab → Combine → Merge Queries | Joins two queries on a matching key column. Select the join kind to control which rows are kept. |
| Append Queries | Home tab → Combine → Append Queries | Stacks two or more queries with the same column structure on top of each other. Equivalent to UNION ALL in SQL. |
| Left Outer | JoinKind.LeftOuter | All rows from the left table, with matching rows from the right. Nulls where no match exists on the right. Most common join in Power Query. |
| Right Outer | JoinKind.RightOuter | All rows from the right table, with matching rows from the left. Nulls where no match exists on the left. |
| Full Outer | JoinKind.FullOuter | All rows from both tables. Nulls on both sides where no match exists. |
| Inner | JoinKind.Inner | Only rows that have a matching key in both tables. Non-matching rows are excluded entirely. |
| Left Anti | JoinKind.LeftAnti | Only rows from the left table that have no match in the right table. Used to find unmatched records. |
| Right Anti | JoinKind.RightAnti | Only rows from the right table that have no match in the left table. |
| Bar Chart | Horizontal bars | Compare specific values across categories. Best for comparing many categories or long category names. E.g. Sales by Region. |
| Column Chart | Vertical bars | Compare specific values across categories. Same as Bar Chart but vertical — better for fewer categories and time series comparisons. |
| Line Chart | Points connected by lines | Show trends over time. Best for visualising a numeric value changing continuously (e.g. Revenue over time). |
| Area Chart | Line chart with area below filled in | Same as Line Chart but emphasises the magnitude of the value. The area between the axis and line is filled. |
| Scatter Chart | X-axis + Y-axis + optional size axis | Display one set of values along the X axis and another set along the Y axis to show relationships or outliers (e.g. Age vs Loan amount). |
| Combo Chart | Column chart + Line chart combined | Combines a column chart and a line chart in one visual. Useful for overlaying an actual value vs a target (e.g. Sales performance vs target). |
| Treemap | Nested coloured rectangles | Visualises categories as coloured rectangles sized by their value. Good for hierarchical data and part-to-whole comparisons (e.g. Product category by sales). |
| Pie Chart | Circle divided into proportional slices | Shows proportions of a whole. Use sparingly — hard to read with more than 5 segments. Donut Chart is a cleaner alternative. |
| Donut Chart | Pie chart with hollow centre | Same as Pie Chart but with a blank centre. Slightly easier to read and allows a centre label (e.g. total value). |
| Map | Geographic bubbles on a map | Plot categorical and quantitative data on a map using latitude/longitude or place names (e.g. Sales by State). |
| Card | Single large number display | Displays a single fact or KPI prominently — a sum, count, or any measure as a large number with a label. |
| Table | Grid of rows and columns | Displays data in a logical series of rows and columns. Good for detailed data lookup or when exact values matter more than patterns. |
| Matrix | Cross-tabulation (pivot table) | Like a Table but supports row and column grouping — similar to a pivot table. Supports drill-down on hierarchies. |
| Slicer | Interactive filter widget | An on-canvas filter control that lets report viewers interactively filter other visuals by clicking values. |
| Add a visual | Report View → Visualizations pane → click a visual type | Click any icon in the Visualizations pane to add that chart type to the canvas. Then drag fields onto it from the Fields pane. |
| Fields: Values vs Axis | Values = measures (numbers)
Axis = categories (dimensions) | Drag numeric measures to Values to aggregate them. Drag dimension columns to Axis, Legend, or Rows to group by them. |
| Change aggregation | Click visual → Values pane → dropdown on field → choose SUM, AVERAGE, COUNT, MAX, MIN, etc. | Power BI automatically sums numeric fields. Change the aggregation by clicking the dropdown arrow next to the field in the Values well. |
| Format visual | Visualizations pane → Format (paint roller icon) | Control colours, fonts, borders, titles, data labels, axes, and legend. Every visual element can be customised here. |
| Drill down | Click the drill-down arrow icon on a visual | Navigate into a hierarchy — from Year to Quarter to Month to Day — within a single visual without filtering other visuals. |
| Cross-filter | Click any bar/slice in one visual | Clicking a data point in one visual automatically filters all other visuals on the page by that value. This is the default Power BI interaction. |
| New Measure | Home tab → New Measure (or right-click table) | Creates a DAX measure — a formula evaluated in filter context. Measures don't add a column; they compute dynamically in each visual cell. |
| New Column | Table Tools tab → New Column | Creates a calculated column — a DAX formula evaluated row by row during model refresh. Stored in the table, not computed in visuals. |
| SUM / AVERAGE | Total Sales = SUM('sales_data'[deal_size])
Avg Deal = AVERAGE('sales_data'[deal_size]) | Aggregate a numeric column. These are the most common starting measures in any Power BI report. |
| COUNT / DISTINCTCOUNT | Order Count = COUNT('sales_data'[deal_size])
Customers = DISTINCTCOUNT('sales_data'[customer_name]) | COUNT counts non-blank values. DISTINCTCOUNT counts unique values — use it for customer counts, unique IDs, etc. |
| IF (conditional measure) | large_deal =
IF('sales_data'[deal_size] > 2000, "Yes", "No") | Creates a conditional result. Used in calculated columns for row-level flags, or in measures for conditional aggregations. |
| CALCULATE (context modifier) | North Sales =
CALCULATE(SUM(Sales[Amount]), Region[Name] = "North") | The core DAX function for overriding filter context. Used in almost every non-trivial measure. |
| DIVIDE (safe division) | Margin % = DIVIDE([Profit], [Revenue], 0) | Safe division — returns the third argument (default BLANK) instead of an error when the denominator is zero. |
| LEFT / LOWER / UPPER | LEFT('sales_data'[customer_name], 3)
LOWER('sales_data'[customer_name])
UPPER('sales_data'[customer_name]) | Text manipulation for calculated columns. LEFT extracts characters from the start; LOWER/UPPER changes case. |
| CALENDAR (date table) | DateTable = CALENDAR(DATE(2020,1,1), DATE(2025,12,31)) | Generates a date table. Required for time intelligence functions to work correctly in Power BI. |
| WEEKDAY | week_day = WEEKDAY('sales_data'[date], 2)
// 2 = Monday start (1-7) | Returns the day of the week as a number. Second argument sets week start: 1=Sunday-Saturday, 2=Monday-Sunday. |
| TOTALYTD (time intelligence) | YTD Sales = TOTALYTD(SUM(Sales[Amount]), Dates[Date]) | Calculates a year-to-date value. Requires a proper date table with a continuous date range marked as a date table. |
| Enable Data Profiling | Power Query → View tab → tick options | Data profiling shows quality stats below each column in Power Query Editor. Enable Column Quality, Column Distribution, and Column Profile. |
| Column Quality | View tab → Column Quality | Shows the percentage of valid, error, and empty values for each column. Quickly spot columns with missing or problematic data. |
| Column Distribution | View tab → Column Distribution | Shows a histogram of value frequencies for each column. Reveals the spread and identifies dominant values or outliers. |
| Column Profile | View tab → Column Profile (select a column first) | Shows detailed statistics for the selected column: distinct count, null count, min, max, average, and a value distribution chart. |
| Profile based on entire dataset | Bottom bar in Power Query → 'Column profiling based on top 1000 rows' → click to toggle | By default, profiling samples only the first 1,000 rows. Click the status bar text to switch to profiling the entire dataset. |
| Error inspection | Click 'Errors' link in Column Quality bar | Filters the table to show only rows with errors in that column. Helps identify the cause of type conversion or formula errors. |
| Publish to Service | Home tab → Publish → select workspace | Uploads the .pbix file to Power BI Service. The report and dataset are published separately and can be managed independently. |
| Share a report | Power BI Service → Share button | Share a link with specific people or your organisation. Recipients need a Power BI Pro or Premium Per User licence to view shared content. |
| Schedule refresh | Power BI Service → Dataset settings → Scheduled Refresh | Configures automatic data refresh on a schedule. Requires a gateway for on-premises sources. Up to 8 refreshes/day on Pro. |
| Export to PDF / PowerPoint | File menu → Export → PDF or PowerPoint | Exports a snapshot of the report pages. Dynamic filtering and drill-downs are not preserved — purely for static distribution. |
| Embed in Teams / SharePoint | Power BI Service → File → Embed report → SharePoint Online or Teams | Embed an interactive Power BI report directly inside Microsoft Teams channels or SharePoint pages without requiring a separate app. |
| Row-Level Security (RLS) | Modeling tab → Manage Roles → define DAX filter | Restricts which rows each user sees. Define roles with DAX filter expressions, then assign users to roles in Power BI Service. |