We have seen in this article the importance of working with Flat File. This is especially true for working with PowerBI but not only
Reminder: what is a Matrix and what is a Flat File format
|Date 1||Date 2|
Flat File Format
|Product 1||Date 1||1000|
|Product 1||Date 2||1100|
|Product 2||Date 1||500|
|Product 2||Date 2||450|
Converting from Matrix to Flat File
We have seen that converting from Flat File to a Matrix is generally seemless. Excel Users know how to do it well using Pivot Tables for example.
However the opposite is not straightforward (at least used to be…). This is annoying since the Matrix format is one of the most used by Excel users and I have no doubt you will receive most of your data in this format.
So how do make this conversion from Matrix to Flat File? Here are some options:
- With PowerBI: We have told you you need to stop Excel and switch to PowerBI a long time ago. PowerBI’s Query Editor handles this conversion just fine:Just import your data and select ALL YOUR ATTRIBUTES columns (i.e the ones you do NOT want to “unflip”), then right click (your mouse cursor needs to be on the header of one of the attributes) and select Unpivot Other Columns
BOOOM! Job done!
- With Excel 2016 / PowerQuery: Similary Excel 2016 comes with PowerPivot and PowerQuery built in. You just have to follow these steps to convert your Matrix into a Flat file, it’s very similar to the PowerBI technique
- With old versions of Excel: this post will show you how to do it