Reference: Transformation & DAX Formulae
Quick reference for Power Query (M) and DAX
Commonly Used Power Query (M) Transformation Formulae
Power Query uses the M language for data transformation. Below are frequently used patterns.
Filtering & Removing
Table.SelectRows(Source, each [Revenue] > 0)
Filter rows where Revenue is positive
Table.RemoveColumns(Source, {"Column1", "UnwantedCol"})
Remove specified columns
Table.Distinct(Source, {"KeyColumn"})
Remove duplicate rows based on key column(s)
Data Type & Formatting
Table.TransformColumnTypes(Source, {{"Date", type date}, {"Revenue", type number}})
Change column data types
Text.Trim([CustomerName])
Remove leading/trailing spaces (in Add Column)
Text.Proper([Region])
Convert to Title Case
Splitting & Combining
Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" "), {"First", "Last"})
Split column by delimiter
[FirstName] & " " & [LastName]
Concatenate columns (Add Column → Custom Column)
Handling Blanks & Nulls
Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"Quantity"})
Replace null with 0 in Quantity column
if [Region] = null then "Unknown" else [Region]
Conditional replacement (Custom Column)
Aggregation & Grouping
Table.Group(Source, {"Region"}, {{"TotalRevenue", each List.Sum([Revenue]), type number}})
Group by Region, sum Revenue
Commonly Used DAX Formulae
DAX (Data Analysis Expressions) is used for measures and calculated columns in Power BI.
Aggregation
Total Revenue = SUM(Sales[Revenue])
Avg Order Value = AVERAGE(Sales[OrderAmount])
Order Count = COUNTROWS(Sales)
Distinct Customers = DISTINCTCOUNT(Sales[CustomerID])
CALCULATE & Filter Context
Revenue (West Only) = CALCULATE(SUM(Sales[Revenue]), Region[RegionName] = "West")
Revenue YTD = CALCULATE(SUM(Sales[Revenue]), DATESYTD('Date'[Date]))
Revenue Last Year = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR('Date'[Date]))
Time Intelligence
Revenue MoM % = DIVIDE([Revenue] - [Revenue LM], [Revenue LM])
Revenue LM = CALCULATE([Revenue], DATEADD('Date'[Date], -1, MONTH))
Revenue YoY % = DIVIDE([Revenue] - [Revenue LY], [Revenue LY])
Ratios & Percentages
Gross Margin % = DIVIDE(SUM(Sales[GrossProfit]), SUM(Sales[Revenue]), 0)
Revenue per Customer = DIVIDE([Total Revenue], [Distinct Customers], 0)
% of Total = DIVIDE([Revenue], CALCULATE([Revenue], ALL(Region)), 0)
Conditional & Logical
Revenue Tier = IF([Revenue] > 1000000, "High", IF([Revenue] > 100000, "Medium", "Low"))
Has Sales = IF([Revenue] > 0, "Yes", "No")
Top N Filter = IF(RANKX(ALL(Product), [Revenue], , DESC) <= 10, [Revenue], BLANK())
Useful Functions Quick Reference
Copilot in Power BI and Tableau
Both Microsoft and Salesforce have integrated AI assistants to accelerate report creation, data exploration, and insight generation.
Power BI Copilot
- Report creation: Describe what you want in natural language—e.g., "Show revenue by region as a bar chart"—and Copilot suggests visuals, layouts, and data bindings.
- DAX generation: Ask for measures in plain English ("Revenue year over year growth") and Copilot drafts DAX; you review and apply.
- Q&A (Ask a question): Natural language queries over your model—"What were total sales in Q3 last year?"—return answers without building visuals.
- Data model: Copilot can suggest relationships, summarize tables, and recommend optimizations.
- Availability: Requires Power BI Premium or Premium Per User (PPU). Part of Microsoft 365 Copilot when licensed.
Tableau Copilot (Tableau Pulse / Einstein)
- Ask Data: Natural language queries—"Show me top 10 products by revenue"—generate visualizations and refine with follow-up questions.
- Tableau Pulse: AI-driven insights surface automatically; monitors metrics and alerts when trends change.
- Explain Data: Select a data point and ask "Why is this high/low?"—Copilot suggests statistical explanations and related dimensions.
- Viz creation: Describe what you want; Copilot suggests chart types and field mappings.
- Availability: Varies by Tableau plan (Creator, Explorer). Tableau Pulse and advanced features on higher tiers.
Best Practices
- Use Copilot to accelerate first drafts; always validate DAX, measures, and visuals for accuracy.
- Ensure data models are well-structured—Copilot performs better with clear relationships and naming.
- Governance: Define who can use Copilot; review AI-generated content before publishing.
