Power Query Data Transformation

Answer: The three phases are:

1. Extract: Connect to data sources and import raw data using connectors
2. Transform: Clean, reshape, and prepare data through transformations
3. Load: Load the transformed data into Power BI's data model for visualization

Why it matters: Understanding the ETL workflow helps you organize queries logically, optimize performance, and follow industry best practices for data preparation.

Answer: The Applied Steps pane records every transformation as a named step, creating a transparent history. You can:

• Click any step to preview data at that stage of transformation
• Edit step names for better documentation
• Delete steps to undo transformations
• Reorder steps (with caution) to optimize or fix logic
• View the M code for each step by selecting it and checking the formula bar

Practical tip: Always rename cryptic auto-generated step names like "Changed Type1" to descriptive names like "Set Sales Amount to Number" for better maintainability.

Answer: Disable loading for intermediate or helper queries that:

• Are referenced by other queries but don't need to appear in the data model as separate tables
• Contain parameter definitions or reusable functions
• Store staging or temporary transformations used in multiple queries

Impact on dependencies: The query still executes during refresh (as long as it's referenced by a loaded query), but it won't create a table in the data model. This reduces model size and keeps the field list clean while maintaining functional dependencies.

Example: If you create a "DateTable Helper" query that generates a date range, and then reference it in your main "Sales" query, you can disable loading for "DateTable Helper" since you only need the final Sales table in your model.

Answer:

• Load: Imports data directly into the Power BI data model without opening Power Query Editor. Use this only when data is already clean and properly structured.
• Transform Data: Opens Power Query Editor first, allowing you to inspect and transform data before loading. This is the recommended approach for most scenarios.

Best Practice: Always use "Transform Data" for new connections to set proper data types, remove unnecessary columns, and apply initial cleaning steps.

Answer: Use the Folder connector with "Combine & Transform Data" feature:

1. Get Data → Folder → Browse to the network folder
2. Click "Combine & Transform Data" (not "Combine & Load")
3. Select an example file to define the structure template
4. Power Query combines all files automatically
5. A "Source.Name" column tracks which file each row originated from

Requirements: All files must have matching column names and structure. Power Query will error if columns don't align.

Performance Tip: Filter the file list to only relevant extensions (e.g., .xlsx) before combining to avoid processing unnecessary files.

Import Mode:

• Pros: Fast queries (cached data), supports all DAX functions, works offline, reduces database load
• Cons: Requires scheduled refresh, data may be stale, consumes storage in Power BI
• Use when: Data doesn't change frequently, you need complex DAX, database is slow, or you need offline access

DirectQuery Mode:

• Pros: Always shows current data, no storage limits, suitable for very large datasets
• Cons: Slower performance (queries hit database), limited DAX support, requires live connection, increases database load
• Use when: Real-time data is critical, dataset exceeds Power BI size limits (10GB Pro, 100GB Premium), or you need row-level security at database level

Hybrid Approach: Use Composite Models to combine Import and DirectQuery tables in the same report, getting benefits of both modes where appropriate.

Answer:

• Remove Columns: Deletes the selected columns, keeps all others
• Remove Other Columns: Keeps only the selected columns, deletes all others

Use Case: When you need to keep only 5 columns out of 50, use "Remove Other Columns" after selecting the 5 you want. This is faster than selecting and removing 45 columns individually.

Tip: "Remove Other Columns" is more resilient to source changes. If new columns are added to the source, they'll be automatically excluded, maintaining your intended column set.

Answer: Use Append Queries because you're combining tables with the same structure (same columns) vertically (adding rows).

Steps:

1. Home ribbon → Append Queries → Append Queries as New (creates a new combined query)
2. Select "Three or more tables"
3. Move North, South, East, and West queries to the "Tables to append" list
4. Click OK

Result: A single query containing all rows from all four regions with matching column structure.

Tip: Add a custom column before appending to identify which region each row came from, or retain the query name as a source column during append configuration.

Answer: Use a Right Anti Join with Sales as the left table and Products as the right table.

Explanation:

• An Anti Join returns rows from one table that have NO match in the other table
• Right Anti Join keeps rows from the right table (Products) with no match in the left table (Sales)
• This gives you products that don't appear in any sales records

Steps:

1. Start with Sales query
2. Home ribbon → Merge Queries
3. Select Products as the second table
4. Match on ProductID in both tables
5. Join Kind: Right Anti
6. Result shows only products with no sales

Alternative: You could also use Left Anti Join by starting with Products and merging with Sales, which would be logically equivalent but conceptually clearer.

Answer: The let...in structure is the foundation of every M query:

• let section: Defines variables (steps) that represent transformations
• in section: Specifies which variable to return as the final query output

Example:

let
    Source = Excel.Workbook(...),    // Variable 1
    FilteredRows = Table.SelectRows(...) // Variable 2
in
    FilteredRows  // Return this variable

Key Point: Each Applied Step you see in the GUI corresponds to a variable in the let section. The last step is always the variable referenced in the in section.

Answer:

= Table.AddColumn(ChangedTypes, "Quarter", 
    each 
        let month = Date.Month([Order Date])
        in
            if month <= 3 then "Q1"
            else if month <= 6 then "Q2"
            else if month <= 9 then "Q3"
            else "Q4"
)

Alternative approach using List.PositionOf:

= Table.AddColumn(ChangedTypes, "Quarter",
    each "Q" & Text.From(Number.RoundUp(Date.Month([Order Date])/3))
)

Explanation: The second approach divides the month (1-12) by 3 and rounds up, giving quarters 1-4, then concatenates "Q" prefix. This is more concise but less explicit than the if-then-else chain.

Answer: Right-click any step in the Applied Steps pane and look for the "View Native Query" option:

• If available (not greyed out): Query folding is working up to that step. Click it to see the SQL or native query being sent to the data source.
• If greyed out: Query folding has stopped, either at this step or a previous one. Power Query is processing transformations locally instead of at the source.

Tip: Check after each transformation to identify which step breaks folding. Consider reordering steps or using alternative approaches to maintain folding for better performance.

Answer: Create a custom function that encapsulates the transformation logic:

1. Create the base query with all transformations
2. Right-click the query → Create Function
3. Add parameters (e.g., table name, filter criteria)
4. Call the function from each of your 5 queries with different parameters

Benefits:

• Update logic in one place, automatically applies to all queries
• Reduces code duplication and maintenance burden
• Ensures consistent transformations across queries
• Makes intent clearer (named function vs repeated steps)

Alternative: Create a base query with common steps, then reference it 5 times and add query-specific transformations to each reference.

Solution Strategy:

1. Analyze the custom column logic: Can it be rewritten as a SQL calculation?
2. Push calculation to source: If possible, create a database view or stored procedure that includes the calculation, maintaining full query folding
3. Filter before custom column: If you only need a subset of rows, apply filters in steps 1-10 (which fold) to reduce dataset before the custom column calculation
4. Simplify custom column: Break complex logic into multiple simpler steps that might fold individually
5. Use calculated column in DAX instead: If the calculation doesn't need to happen in Power Query, move it to a DAX calculated column in the data model (evaluates during report interaction, not refresh)

Example: If custom column is "SalesCategory = IF Amount > 1000 THEN 'High' ELSE 'Low'", you could add this logic to the SQL view:

CREATE VIEW vw_Sales AS
SELECT *, 
  CASE WHEN Amount > 1000 THEN 'High' ELSE 'Low' END as SalesCategory
FROM Sales

Result: Query folding preserved, calculation runs on SQL Server (optimized engine), refresh time potentially reduced from 30 minutes to seconds.

Answer: Step 4 - Add Custom Column

Why it breaks folding:

• Steps 1-3 fold: Filter, remove columns, and source operations can be translated to SQL (WHERE, SELECT, FROM)
• Step 4 breaks: Custom columns require M language logic that SQL Server doesn't understand. Power Query must download ALL 5 million rows and process the calculation locally
• Steps 5-6 don't fold back: Once folding breaks, all subsequent steps also process locally

Impact: Instead of SQL Server filtering to relevant 2024 rows, Power Query downloads millions of rows, calculates the custom column, then filters. This is 100x+ slower!

Example: If you need Amount * 1.1 calculation, create a SQL view instead:

CREATE VIEW vw_SalesAdjusted AS
SELECT 
  SalesID,
  ProductID,
  Amount,
  CAST(Amount AS FLOAT) * 1.1 as AdjustedAmount,
  YEAR(SaleDate) as SalesYear
FROM Sales
WHERE YEAR(SaleDate) = 2024

Result: Query folding maintained throughout! Calculation happens on SQL Server (the optimized engine). Power Query downloads only 2024 data with adjusted amounts pre-calculated. Refresh: 100x faster!

Solution: Move custom column to the end OR use a SQL query/view to pre-calculate on the server side.

Optimized Approach:

Step 1: Source = SQL Server (SELECT only needed columns) ✓ Folds
Step 2: Filter = WHERE year = 2024 ✓ Folds
Step 3: Filter = Add 2nd filter for margin calc (done in SQL) ✓ Folds
Step 4: Remove unnecessary columns ✓ Folds
Step 5: Change data types ✓ Folds
Step 6: Custom Column = profit margin (can't fold) ✗ Doesn't fold
Step 7: Filter = margin > 5% (processes 1.2M locally, filters to 300K)
Step 8: Unpivot ✗ Doesn't fold
Step 9: Group By ✗ Doesn't fold

Key improvements:

1. Push filtering to SQL: Modify the source to include `WHERE Year = 2024` in the SQL query, not as a filter step
2. Filter early before custom calculations: Every step before the custom column processes 50M rows. Move Step 2 before Step 3
3. Minimize folding breakers: Keep custom columns and unpivot at the end (they'll process minimal rows)
4. Use SQL functions: Calculate margin in the SQL query itself, letting the server do the math

Expected improvement: From 45 minutes to 2-3 minutes by:

• Reducing downloaded rows from 50M → 1.2M (early filter)
• Further reducing to 300K before custom column (double filtering)
• Having server handle numeric calculations (SQL is faster)

Example: If your custom column calculates profit margin = (Amount - Cost) / Cost, push this to SQL:

CREATE VIEW vw_Sales_Optimized AS
SELECT 
  SalesID,
  ProductID,
  Amount,
  Cost,
  CASE 
    WHEN YEAR(SaleDate) = 2024 THEN 1 
    ELSE 0 
  END as IsCurrentYear,
  CAST((Amount - Cost) AS FLOAT) / NULLIF(Cost, 0) as ProfitMargin,
  CASE 
    WHEN (Amount - Cost) / NULLIF(Cost, 0) > 0.05 THEN 'Pass' 
    ELSE 'Filter Out' 
  END as MarginStatus
FROM Sales
WHERE YEAR(SaleDate) >= 2023

Result: Query folding preserved, calculation runs on SQL Server (optimized engine), refresh time reduced from 30+ minutes to seconds. Power Query now only downloads 300K pre-filtered rows with margins already calculated!

What it tells you:

• Steps 1-3 fold: The native SQL query is being constructed up to the filter
• Step 4 or 5 breaks folding: One of those steps contains an operation that can't be translated to SQL
• All subsequent steps don't fold: Once folding breaks, steps 6, 7, 8 also process locally

How to diagnose the exact breaking point:

1. Right-click Step 4 → Check if "View Native Query" is available
2. If available: Step 4 still folds, so Step 5 (custom column) broke it
3. If greyed out: Step 4 broke it (might be a text operation, unpivot, or complex transformation)

Solutions to restore folding:

• Option 1 - Reorder: Move the folding-breaking step (custom column) to the very end, after all folding operations
• Option 2 - SQL approach: Calculate in your SQL source query instead of in Power Query
• Option 3 - Use separate query: Create a helper query that does the foldable steps, then reference it for the custom column
• Option 4 - Avoid Table.Buffer(): If used, remove it immediately (it completely kills folding)

Performance impact: Restoring folding by reordering might reduce refresh time from 30 minutes to 5 minutes for large datasets!

Optimization Strategy:

1. Push filtering to the source (Game changer!):

// DON'T do this:
Source = SQL.Database("server", "database", "SELECT * FROM Sales")
= Table.SelectRows(Source, each [Year] = 2024)

// DO this instead:
Source = SQL.Database("server", "database", 
  [Query = "SELECT * FROM Sales WHERE Year = 2024"])

Impact: Download 100M → 10M rows (90% reduction!)

2. Merge with smallest tables first:

Step 1: Sales (10M filtered rows) - source
Step 2: Merge with Categories (200 rows) → result: 10M rows
Step 3: Merge with Products (50K rows) → result: 10M rows
(Both merges work on reasonable dataset sizes)

3. Keep foldable operations early:

Step 1: SQL Source (filtered) ✓ Folds to SQL
Step 2: Remove unnecessary columns ✓ Folds
Step 3: Merge with Categories (can fold if using merge-as-join)
Step 4: Merge with Products (might break folding)
Step 5: Change data types ✓ Still folds (probably)
Step 6: Custom column (revenue calc) ✗ Breaks folding

4. Group/Aggregate on SQL if possible:

-- Consider pre-aggregating on SQL Server instead of in Power Query:
SELECT 
  CategoryID, 
  Month, 
  SUM(Quantity) as TotalQty,
  SUM(Amount) as TotalAmount,
  SUM(Amount) / NULLIF(SUM(Quantity), 0) as AvgPrice
FROM Sales
WHERE Year = 2024
GROUP BY CategoryID, Month

Example: Create a pre-aggregated view for Power Query to consume:

CREATE VIEW vw_SalesSummary AS
SELECT 
  s.CategoryID,
  c.CategoryName,
  YEAR(s.SaleDate) as SalesYear,
  MONTH(s.SaleDate) as SalesMonth,
  COUNT(DISTINCT s.OrderID) as OrderCount,
  SUM(s.Quantity) as TotalQuantity,
  SUM(s.Amount) as TotalRevenue,
  SUM(s.Amount - s.Cost) as TotalProfit,
  CAST(SUM(s.Amount - s.Cost) AS FLOAT) / NULLIF(SUM(s.Amount), 0) as ProfitMargin,
  CAST(SUM(s.Amount) AS FLOAT) / NULLIF(COUNT(DISTINCT s.OrderID), 0) as AvgOrderValue
FROM Sales s
INNER JOIN Categories c ON s.CategoryID = c.CategoryID
WHERE YEAR(s.SaleDate) >= 2023
GROUP BY s.CategoryID, c.CategoryName, YEAR(s.SaleDate), MONTH(s.SaleDate)

Result: Instead of Power Query downloading 100M rows and performing unpivot + grouping, SQL Server returns pre-aggregated data (potentially just 12-24 rows per category). Power Query only needs to load, rename columns, and format. Refresh time: 2 hours → 5-10 minutes!

Expected improvements:

Optimization	Impact	Effort
Push year filter to SQL	100M → 10M rows (10x faster)	Low
Merge before unpivot	30% faster merging	Low
Pre-aggregate on SQL	10M → 1K rows final (100x faster)	Medium
Total optimization	2 hours → 5-10 minutes (10-20x faster!)	Medium

Pro tip: Ask yourself: "Can SQL Server do this?" If yes, let it! Databases are 100x faster than Power Query at filtering, joining, and aggregating.

Answer: Preserve SQL folding as long as possible

DO THIS (Optimal):

Step 1: SQL Source = Query with filters applied ✓ Folds
Step 2: Filter = WHERE conditions ✓ Still folds
Step 3: Remove columns ✓ Still folds
Step 4: Excel Source = Load the 5K row file (separate query)
Step 5: Merge SQL (after folding optimizations) with Excel
         ✓ Folding breaks here (mixing sources)
         ✓ But you've minimized SQL data FIRST (e.g., 1M rows instead of 10M)

DON'T DO THIS (Inefficient):

Step 1: SQL Source = Get all 10M rows (no filters)
Step 2: Excel = Load 5K rows
Step 3: Merge = Download all 10M + process locally ✗ SLOW
Step 4: Then filter ✗ Way too late

Why the difference matters:

• Optimal: Filter 10M → 1M in SQL, merge 1M with 5K locally = 5M operations
• Inefficient: Get 10M from SQL, merge with 5K locally = 50M operations (10x slower!)

Key principle: Maximize folding with queryable sources BEFORE merging with non-foldable sources

Advanced optimization:

1. Create separate queries for each source (SQL and Excel)
2. Apply heavy filtering to the SQL query alone
3. Only after optimization, merge the two queries
4. Do additional transformations (custom columns, unpivot) AFTER merge

Example: Create an optimized SQL view that does the heavy lifting:

CREATE VIEW vw_Orders_Optimized AS
SELECT 
  o.OrderID,
  o.CustomerID,
  o.Amount,
  o.SaleDate,
  CASE 
    WHEN o.Amount > 5000 THEN 'High Value'
    WHEN o.Amount > 1000 THEN 'Medium Value'
    ELSE 'Standard'
  END as ValueCategory,
  YEAR(o.SaleDate) as OrderYear
FROM Orders o
WHERE YEAR(o.SaleDate) = 2024 
  AND o.Status = 'Active'
  AND o.Amount > 100

Then in Power Query, use this optimized view:

// Query 1: OptimizedOrders (pre-filtered from SQL view)
let
    Source = SQL.Database("server", "db", 
      [Query = "SELECT * FROM vw_Orders_Optimized"]),
    RemovedColumns = Table.RemoveColumns(Source, {"Debug"})
in
    RemovedColumns

// Query 2: CustomerLookup (from Excel)
let
    Source = Excel.Workbook(File.Contents("C:\Lookups\Customers.xlsx"))
in
    Source

// Query 3: MergedData (combine after optimization)
let
    Merged = Table.NestedJoin(OptimizedOrders, "CustomerID", CustomerLookup, "ID", "Lookup"),
    ExpandLookup = Table.ExpandTableColumn(Merged, "Lookup", {"Name"}, {"CustomerName"}),
    CustomCalc = Table.AddColumn(ExpandLookup, "Profit", each [Amount] * 0.3)
in
    CustomCalc

Result: SQL handles filtering and calculations (optimized engine). Power Query only downloads 1M pre-filtered rows + 5K lookup = 1M total operations instead of 10M. Query runs in minutes instead of hours!

Systematic Bottleneck Identification Process:

Phase 1: Isolate the slow query

1. In Power BI Desktop, go to File → Options → Performance Analyzer
2. Enable it and refresh your workbook
3. The Performance Analyzer shows which queries are slowest
4. Focus on the query(ies) taking >5 minutes

Phase 2: Check query folding status

In Power Query Editor:
1. Open the slow query
2. Go through each Applied Step
3. Right-click each step → "View Native Query"
4. Note where it becomes greyed out
5. This is where folding breaks

Phase 3: Quantify the impact

// Test by disabling folding-breaking steps:
1. Temporarily delete steps after folding breaks
2. Refresh and note the time
3. Re-enable steps one by one
4. Identify which step causes the biggest slowdown
5. Data rows downloaded = indicator of folding success

Phase 4: Apply optimization strategies

Problem Identified	Solution	Expected Improvement
No filters before custom column	Add filter before custom column step	50-70% faster
Merging large table first	Reorder to merge small table first	20-40% faster
Downloading all rows unnecessarily	Add WHERE clause in SQL source	50-90% faster (biggest gain!)
Using Table.Buffer()	Remove it	30-60% faster
Complex M calculations	Pre-calculate on SQL server	40-80% faster

Phase 5: Validate improvement

1. Make one optimization change
2. Refresh and measure time (note in Performance Analyzer)
3. If improvement >10%, keep it
4. Apply next optimization
5. Repeat until acceptable performance (<5 min preferred)

Real-world example:

Starting: 45 minutes (all 50M rows downloaded)
After Step 1 (add WHERE to SQL): 15 minutes (90% reduction)
After Step 2 (reorder merges): 12 minutes (20% reduction)
After Step 3 (move custom column): 8 minutes (33% reduction)
Final: 8 minutes total (5.6x improvement!)

ROI: 30-40 minutes saved per refresh = 2.5+ hours per week

Key takeaway: Most slow queries have a single root cause (usually downloading all rows instead of filtering). Find it with Performance Analyzer, fix it with SQL filtering, and watch your refresh time collapse!