Loading data into Power BI through Power Query allows you to manipulate and change the layout of your data before you load it into your Power BI model. There are many functions in Power Query and it can be a 'no code' experience making it a simple tool to use.
But the problem with having a lot of functions is that it can be overwhelming, and one of the things that I came across recently was from a client who was having trouble utilising some of the Power Query features and had resorted to using Excel as he was more comfortable in an Excel environment.
However, getting to grips with Power Query would have ultimately saved him time as it would have automated the steps that he needed to go through on his report. The most complex of which was flattening out his data.
Flattening of the data is an important data preparation step and is the process of ensuring that all of the data is arranged in columns, rather than with a fact dimension across rows.
For example, below is a typical data table that you could be faced with, where the months of the year are arranged across top of the report.
What we need to do is flatten this out. In other words, turn this round to have it as 3 columns - Barcode, Date and Sales.
The way of solving this that I was shown was to create a second tab within the Excel and perform a series of lookups using months and barcodes, which wasn't only complex to build, but had the potential to go wrong every time the data needed updating.
What we really needed to do was let Power Query perform this operation so that it was consistent every time and didn't need any complex changes in Excel.
The best news of all was that this can be done without any code and through the press of one button within Power Query.
Once the data is loaded into Power Query you need to use the 'Unpivot' Function. There are two versions for this. The first allows you to unpivot the columns you had selected, so if you had selected all of the months then you could select this as an option. Or there is the second, which is to unpivot the other columns, meaning everything other than the one (or ones) that I had selected.
In this example its easier to select the 'Barcode' column and 'Unpivot Other Columns'. You can find this by right clicking on the column header. This will then unpivot everything other than the Barcode column.
Once this is complete you will be left with three columns, one for the barcode, one for the dates and one for the sales values.
All that is left to do now is to rename the columns and load the data.
This is a simple and no code solution that can unpivot columns during the loading of data. Making sure that the change in the shape of the data is consistent every time.
Comments