learn.fttgsolutions.com · Data & BI
Quick reference for Power BI — from loading data and Power Query transformations to DAX measures, chart types, and data profiling.
FTTG · May 19, 2026 · 11 min read
Quick reference for Power BI — from loading data and Power Query transformations to DAX measures, chart types, and data profiling.
📋 Quick reference: Power BI Cheat Sheet — use this alongside the guide for fast syntax lookup while you read.
The authoring environment installed on your machine. This is where you connect to data, transform it in Power Query, build the data model, write DAX measures, and design reports. All work is saved as a .pbix file.
Three views in Desktop:
The cloud platform at app.powerbi.com. You publish .pbix files here to share reports, manage datasets, configure scheduled refresh, set up row-level security, and distribute content through workspaces and apps. The Service is where end users consume reports.
iOS and Android apps for consuming reports on the go. Reports render responsively. Supports push notifications from data-driven alerts configured in the Service.
The canvas where you build visuals. Each page is a separate tab. Visuals are placed on the canvas and connected to fields from the data model. Filters can be applied at the visual, page, or report level.
Key panels:
Shows the actual data inside each table after all Power Query transformations have been applied. Use it to inspect values, verify column types, and write calculated columns. Not available for DirectQuery models.
Shows all tables and the relationships between them as a diagram. Use it to create, edit, and delete relationships, set relationship cardinality and cross-filter direction, and manage the overall model structure.
The entry point for connecting to any data source.
Steps:
Common sources: Excel, CSV, SQL Server, Azure SQL, SharePoint Online, Web, OData, Power BI datasets, Dataverse, Fabric Lakehouse
After selecting tables in the Navigator, choose how to bring data in:
Best practice: always click Transform Data first so you can inspect and type the data before it enters the model.
Opens the Power Query Editor where all data shaping happens before the data enters the model. Every transformation creates a step in the Applied Steps pane and generates M code behind the scenes.
Once data is loaded, switch to Data View to verify:
Forces Power Query to re-execute all queries and reload data from source.
Scheduled refresh in the Service runs on a cadence you define (up to 8x/day on Pro, 48x/day on Premium). Requires an on-premises data gateway for local data sources.
Tell Power BI how tables are related so filters can flow between them.
Steps:
Best practice: always use integer surrogate keys for relationships, not text columns. Text relationships are slower and less reliable.
When connecting to a new data source, click Transform Data in the Navigator instead of Load. This opens the Power Query Editor before any data enters the model.
To edit queries on a dataset already in the model: Home ribbon → Transform data → Transform data
Or right-click any table in the Fields pane → Edit query
The panel on the right side of the Power Query Editor. Every transformation you apply — promote headers, change type, filter rows — creates a named step here. Steps execute in order from top to bottom.
fx bar to view or edit the M code for the selected stepRemove rows from the top, bottom, or based on a condition.
UI: Home → Remove Rows → Remove Top Rows / Remove Bottom Rows / Remove Duplicates / Remove Blank Rows / Remove Errors
Generated M:
// Remove top 1 row
= Table.Skip(Source, 1)
// Remove blank rows
= Table.SelectRows(Source, each not List.IsEmpty(
List.RemoveMatchingItems(Record.FieldValues(_), {"", null})
))
Add a new column based on a formula, conditional logic, or transformation of existing columns.
UI: Add Column tab → Custom Column
Generated M:
// Add a full name column
= Table.AddColumn(Source, "FullName",
each [FirstName] & " " & [LastName],
type text
)
// Add a conditional column
= Table.AddColumn(Source, "Tier",
each if [Amount] >= 10000 then "High"
else if [Amount] >= 5000 then "Mid"
else "Low",
type text
)
Replace a specific value in a column with another value.
UI: Right-click column → Replace Values
Generated M:
= Table.ReplaceValue(Source, "N/A", null,
Replacer.ReplaceValue, {"Status", "Region"})
Set the correct data type for each column. Critical — Power BI will not aggregate a number stored as text.
UI: Click the type icon left of the column header, or select column → Transform → Data Type
Generated M:
= Table.TransformColumnTypes(Source, {
{"OrderDate", type date},
{"Amount", type number},
{"CustomerID", type text},
{"IsActive", type logical}
})
Use the first row of data as column headers. Common after importing from CSV or Excel files where the header row was treated as data.
UI: Home → Use First Row as Headers
Generated M:
= Table.PromoteHeaders(Source, [PromoteAllScalars = true])
Split a column into multiple columns by a delimiter or by position.
UI: Transform → Split Column → By Delimiter / By Number of Characters
Generated M:
// Split "FirstName LastName" on space
= Table.SplitColumn(Source, "FullName",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"FirstName", "LastName"}
)
Pivot — turn row values into column headers (wide format). Unpivot — turn column headers into row values (long format). Long format is what Power BI's data model requires for most analyses.
UI: Transform → Pivot Column or Unpivot Columns
Generated M:
// Unpivot Jan, Feb, Mar columns into Month / Value rows
= Table.Unpivot(Source, {"Jan","Feb","Mar"}, "Month", "Value")
// Pivot Region column — turn North/South/East into separate columns
= Table.Pivot(Source,
List.Distinct(Source[Region]),
"Region", "Amount", List.Sum
)
Aggregate rows — equivalent to GROUP BY in SQL.
UI: Transform → Group By
Generated M:
= Table.Group(Source, {"Region"}, {
{"TotalSales", each List.Sum([Amount]), type number},
{"OrderCount", each Table.RowCount(_), type number},
{"AvgSale", each List.Average([Amount]), type number}
})
Join two queries on a common key column — equivalent to SQL JOIN.
UI: Home → Merge Queries
Generated M:
= Table.NestedJoin(
Orders, {"CustomerID"},
Customers, {"CustomerID"},
"CustomerData",
JoinKind.LeftOuter
)
Then expand the nested table column to bring in fields from the joined query.
Stack two or more queries vertically — equivalent to SQL UNION ALL.
UI: Home → Append Queries
Generated M:
= Table.Combine({Orders2023, Orders2024, Orders2025})
All rows from the left (first) table. Matched rows from the right. Unmatched right rows appear as null. Most common join type in Power Query.
Left table: all rows ✓
Right table: matches only (nulls where no match)
All rows from the right (second) table. Matched rows from the left. Unmatched left rows appear as null.
Left table: matches only (nulls where no match)
Right table: all rows ✓
All rows from both tables. Unmatched rows on either side appear as null.
Left table: all rows ✓
Right table: all rows ✓
Only rows with a match in both tables. Rows without a match are dropped.
Left table: matches only
Right table: matches only
Only rows from the left table that have no match in the right table. Used to find missing records.
Use case: customers with no orders
Only rows from the right table that have no match in the left table.
Use case: orders with no matching customer record
Horizontal bars. Best for comparing categories when labels are long or there are many categories.
Vertical bars. Best for showing values across a small number of categories or across time periods.
Shows trends over time. Requires a date or sequential axis.
Line chart with the area below the line filled. Emphasises volume and cumulative trends. Stacked area shows part-to-whole over time.
Plot two numeric measures against each other to show correlation. Add a third measure as bubble size for a bubble chart.
Combine a column chart and a line chart on the same visual. Useful for showing volume (columns) alongside a rate or target (line).
Show part-to-whole relationships using nested rectangles. Size and colour represent values.
Show part-to-whole for a small number of categories (3–5 max). Avoid when precise comparison matters — use a bar chart instead.
Same as pie chart with a hollow centre. Can display a summary value in the middle using a card overlay.
Plot data on a geographic map. Requires a location field — country, city, postcode, or lat/lon coordinates.
Display a single KPI value prominently. No axis, no legend — just the number and an optional label.
Display raw data in rows and columns. Supports conditional formatting, totals, and drill-through.
Pivot-table style visual. Rows and columns can both be expanded hierarchically.
An interactive filter control on the report canvas. Users click to filter all other visuals on the page.
Or: select fields first, then Power BI will suggest a visual type automatically.
Every visual has specific field wells that control what goes where.
| Well | Purpose |
|---|---|
| Axis / Rows | Category or time dimension — what you group by |
| Values | Numeric measure — what you aggregate |
| Legend | Breakdown series — adds colour grouping |
| Tooltips | Extra context shown on hover |
| Drill through | Fields available when drilling to another page |
When you add a numeric column (not a measure) to a visual, Power BI defaults to Sum. Change it by clicking the field in the well → select Sum / Average / Min / Max / Count / Count Distinct.
Best practice: use explicit DAX measures instead of relying on implicit aggregation. Measures give you full control and are reusable across visuals.
Every visual has three panes in the Visualizations panel:
Key formatting options:
If a field well has a hierarchy (Year → Quarter → Month → Day), drilling lets users navigate to lower levels of detail.
By default, clicking a data point in one visual filters all other visuals on the page — this is cross-filtering.
Edit interactions:
A measure is a dynamic calculation that evaluates in filter context. It does not store values — it calculates when a visual queries it.
Steps: Right-click a table in the Fields pane → New measure → type the DAX formula in the formula bar.
Best practice: store all measures in a dedicated empty table called
_Measuresto keep them organised and easy to find.
Total Sales = SUM(FactSales[Amount])
A calculated column is evaluated row by row at refresh time and stored in the table. Use for static row-level attributes, not for aggregations.
Steps: Right-click a table → New column
-- In DimCustomer
Full Name = DimCustomer[FirstName] & " " & DimCustomer[LastName]
The most common aggregation measures.
Total Revenue = SUM(FactSales[Amount])
Average Order = AVERAGE(FactOrders[OrderTotal])
Count rows or unique values.
Total Orders = COUNTROWS(FactOrders)
Unique Customers = DISTINCTCOUNT(FactOrders[CustomerID])
Return different values based on a condition.
Sales Status =
IF([Total Sales] >= [Sales Target], "On Target", "Below Target")
Evaluate a measure inside a modified filter context. The most important DAX function.
Sales USA =
CALCULATE([Total Sales], DimGeography[Country] = "USA")
Sales LY =
CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))
Divide two values safely — returns an alternate result instead of an error when the denominator is zero.
Attainment % =
DIVIDE([Total Sales], [Sales Target], 0)
Text manipulation in calculated columns.
Category Code = LEFT(DimProduct[ProductCode], 3)
Clean Email = LOWER(DimCustomer[Email])
Upper Code = UPPER(DimProduct[SKU])
Generate a complete date table — required for time intelligence functions.
DateTable =
VAR StartDate = DATE(2020, 1, 1)
VAR EndDate = DATE(2030, 12, 31)
RETURN
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & QUARTER([Date]),
"WeekDay", WEEKDAY([Date], 2),
"IsWeekend", IF(WEEKDAY([Date], 2) >= 6, TRUE, FALSE)
)
Mark the table as a date table: right-click the table → Mark as date table → select the Date column.
Return the day of week as a number. Used in date tables and conditional measures.
-- 2 = Monday is day 1
WeekDayNum = WEEKDAY(DimDate[Date], 2)
IsWeekend =
IF(WEEKDAY(DimDate[Date], 2) >= 6, TRUE, FALSE)
Accumulate a measure from the start of the year to the current date in filter context.
Sales YTD =
TOTALYTD([Total Sales], DimDate[Date])
-- Fiscal year ending June 30
Sales Fiscal YTD =
TOTALYTD([Total Sales], DimDate[Date], "06-30")
Data profiling tools live in the Power Query Editor. Enable them under the View tab.
View tab in Power Query Editor → check:
By default profiling runs on the first 1,000 rows. Change this to the full dataset (see below).
Shows a bar per column with three segments:
Use this to quickly spot columns with data quality issues before they enter the model.
Shows a histogram of value distribution for the selected column — how many distinct values, how many unique values (appear only once), and a frequency chart of the top values.
Use this to identify high-cardinality columns, unexpected duplicates, and outlier values.
Shows detailed statistics for the selected column:
Click any bar in the distribution chart to filter the table preview to those rows.
By default profiling is limited to the first 1,000 rows. For accurate profiling on large datasets:
Bottom-left of the Power Query Editor status bar → click "Column profiling based on top 1000 rows" → switch to "Column profiling based on entire data set"
Note: profiling the entire dataset can be slow on large sources. Use selectively.
When Column Quality shows red (errors) in a column:
Fix options:
Send your .pbix file from Desktop to the Power BI Service.
Steps:
After publishing, the report is accessible in the Service at app.powerbi.com.
Share a published report directly with individuals or groups.
In the Service:
Best practice: share through a Power BI App rather than direct links. Apps give you a stable URL, a curated navigation experience, and control over what users see.
Keep dataset data current by configuring automatic refresh in the Service.
Steps:
Requirements:
Export a report snapshot for distribution outside Power BI.
In the Service: File → Export to PDF or Export to PowerPoint
In Desktop: File → Export → PDF
Note: exports are static snapshots — they do not carry interactivity. For live sharing, use the Service URL or embed.
Surface Power BI reports inside Microsoft 365 tools without requiring users to navigate to app.powerbi.com.
Teams:
SharePoint Online:
Both methods respect Power BI permissions — users must have access to the report to see it.
Restrict what data each user sees based on their identity. Configured in Power BI Desktop, enforced by the Service.
Steps in Desktop:
FacilityDirector)[UserEmail] = USERPRINCIPALNAME()
Steps in Service:
Test RLS:
Best practice: filter on dimension tables, not fact tables. Apply the filter to the table that has a relationship to the fact — it will propagate through the model automatically.
Part of the FTTG Learn Cheat Sheet series — fttgsolutions.com