Power BI Pivot & Unpivot

In some cases, you might wonder why you couldn’t get the graph or chart you wanted from your raw data. The fact is that sometimes you might need to ‘shape’ your data before visualization. This is what PowerBI is really strong at.

This example is from a multiple answer questions of marketing research survey asking which type of products they’ve purchased online. One respondent (ID) could answer more than 1 answer. The chart below is what we expected to have.

However, our raw data could be either 1) in 1 column with commas or 2) in various columns as header and value as 0,1.

To be able to do the chart, we need to have 2 columns ‘ID’ and ‘Value’ with one value in each row.

The function to be used here is ‘unpivot’ column to shape data by bringing the column names to become rows in ‘attribute’ column and show the values in ‘values’ column.

After that we could just beautify the label by extracting the attribute after ‘-‘ and also filter only value ‘1’

Lastly, we can just use ‘Atrribute’ and ‘Count of ID’ to plot the graph easily.

In summary, ‘pivot column’ could bring the row values to become header and ‘unpivot column’ could bring the column header to become rows. It is very useful in shaping data to create appropriate graphs.