Weekend Work 001 – Unpivot and basic aggregations

Query Editor – Unpivot your data

Report Layer – Basic aggregations

When you first start with Power BI, these two areas are vital to so many people I have worked with. Unpivot is so common when you have spreadsheets as a source to your data. Having had conversations that go along these lines “Ok I’ll write a Macro to re-shape the data into columns…” then two minutes later it is “Oh wow that’s so much quicker and simpler”.
Basic aggregations in Power BI are fundamental, and as you learn to do more with DAX, you will always need to use the basics.
Simple aggregations
AVERAGE ( ) – Average (mean) of a Column
AVERAGEA ( ) – Average (mean) of a column, but can handle non-numeric values (treats them as 0)
COUNT ( ) – Count the number of items in a column (not including blank or Boolean)
COUNTA ( ) – Count the number of items in a column (not including blank)
COUNTBLANK ( ) – Count the number of blank items in a column
COUNTROWS ( ) – Count the rows in a table
DISTINCTCOUNT ( ) – Count the number of unique values in a column (including blank)
MAX ( ) – Returns the largest numeric value in a column
MIN ( ) – Returns the smallest numeric value in a column
SUM ( ) – Arithmetic total

1 thought on “Weekend Work 001 – Unpivot and basic aggregations”

Leave a Reply