Day 4: Power Query — Advanced Transformations

Unpivot transforms wide data (where categories are columns) into long/tidy data (one row per observation). Example: a sales table with columns Jan | Feb | Mar | Apr (each containing revenue per product) becomes a table with columns Product | Month | Revenue — one row per product per month. Long format is required for Power BI date intelligence, proper filtering, and most DAX calculations. Select the columns to unpivot → Transform → Unpivot Columns. The column names become an "Attribute" column and values become a "Value" column — rename them appropriately.

Home → Merge Queries → select the first query, click the matching column, select the second query, click its matching column → choose join type (Left Outer, Inner, Full Outer, Right Outer, Left Anti, Right Anti). The result adds a new column containing a nested table — click the expand icon in that column's header to select which columns to bring in from the second query. This is exactly equivalent to a SQL JOIN. Merge Queries as New creates a third query without modifying either original.

Home → Append Queries → select the second query to append to the current one. This stacks rows from both queries into one table — equivalent to SQL UNION ALL. All columns must have the same names for data to align correctly. If column names differ, Power Query creates new columns for each unique name and fills with null where data is absent. Append Queries as New creates a third combined query. Use case: combining 12 monthly sales files into one annual dataset.

Add Column → Custom Column → enter a name and write a formula in M language. M language uses square brackets around column names and is case-sensitive. Basic examples: [Revenue] - [Cost] calculates profit. if [Revenue] > 100000 then "High" else "Standard" categorises rows. Date.Year([OrderDate]) extracts the year. Text.Upper([ProductName]) converts to uppercase. Custom columns are calculated at query refresh time, which is faster than DAX calculated columns for row-level operations on large tables.

Home → Group By → select columns to group on → define aggregations (Sum, Count, Average, Min, Max, All Rows). Creates a summary table from detailed data. Example: group by Region and Product Category, sum Revenue — produces one row per Region-Category combination with total revenue. Use Group By when you need a clean aggregated table in your model rather than an interactive Pivot Table. The result is a static summary that refreshes with the data.