Reshaping and aggregating data

Data Transformation in Power BI

Khaled Choucri

Strategic Analyst - Trilogy

Understanding dataset shapes

Long Data Structure
- Repeating values in first column
- Usually only one numerical column
- Easier for computer to interpret

A long dataset containing two categorical columns: Team and Variable, and one numerical column: Value.

Wide Data Structure
- Unique values in first column
- Multiple numerical columns
- More human-readable

A wide dataset containing one categorical column: Team, and three numerical columns: Points, Assists ,and Rebounds.

¹ Data can be represented in different ways through different dataset shapes

Transforming data shape

Two datasets, one with a long structure, and one with a wide structure. The long dataset is connected to the wide dataset with an arrow labeled pivot, while the wide dataset is connected to the long dataset with an arrow labeled unpivot.

To transform from long to wide, we pivot our data
To transform from wide to long, we unpivot our data

Transforming data shape

Transposing changes your rows into columns and your columns into rows

A table containing day names as our first columns, with units sold in the North, South, East, and West regions as our other columns. After transposing this table, we get North, South, East, and West as our first column with the units sold per day of week as the other columns.

Aggregating with group by transformation

Grouping changes the granularity of your dataset
After choosing a granularity (groups) you apply an aggregation such as:
- Sum
- Average
- Median
- Min / Max
- Count
- Custom aggregation

Two tables, one showing ungrouped daily sales data, and another showing the total and average sales for each week after grouping the daily data and aggregating.

Let's practice!

Data Transformation in Power BI