Day 14: Power Query — Advanced Transformations

Unpivot transforms wide data (where categories are spread across multiple columns) into long/tidy data (one row per observation). Wide data: columns Jan | Feb | Mar | Apr, each with revenue values per customer. Long data: columns Customer | Month | Revenue. Long data is required for Pivot Tables, Power BI, and most analysis tools. In Power Query Editor: select the category columns (Jan, Feb, Mar...) → Transform → Unpivot Columns. A new "Attribute" column (containing the column names) and "Value" column are created.

Add Column → Custom Column → enter a name and a formula in M language. Basic examples: combine columns: [FirstName] & " " & [LastName]. Conditional: if [Revenue] > 100000 then "High" else "Standard". Date extraction: Date.Year([OrderDate]). M language is case-sensitive and uses square brackets around column names. For simple column calculations, Custom Column is often more readable than adding a calculated column in Excel after loading.

Errors in Power Query appear as red "Error" cells in the preview. To see the error: click the error cell to see the error message at the bottom. To handle errors: right-click the column header → Replace Errors → enter a replacement value (0, null, or a string). Alternatively, click the column → Transform → Replace Errors. Errors commonly occur when changing data type — e.g. changing a column to Number when some cells contain text. Fix the source data if possible; otherwise use Replace Errors.

Group By aggregates data — equivalent to a SQL GROUP BY or an Excel Pivot Table. Home → Group By → select the column(s) to group on → add aggregations (Sum, Count, Average, Min, Max of other columns). Example: group by Region and sum Revenue gives total revenue per region. The result is loaded as a summary table. Unlike a Pivot Table, the Group By result is a fixed table — it does not have the interactive drill-down of a Pivot Table but is useful for creating summary inputs for dashboards.

Data → Get Data → From File → From Folder → select the folder. Power Query shows all files in the folder. Click "Combine & Transform". Power Query creates a sample file query, a function query, and a combined query that runs the same transformations on every file and stacks the results. This is the standard approach for combining 12 monthly CSV files into one annual dataset — every month, you just add the new file to the folder and click Refresh.