how to get report from multi-dimensional data in Excel sheet?

[prokurors]

I have multi-dimensional sales data (in a simplified version "three dimensions" only - month, sales category, customer) in Excel sheet. The data in the sheet is layed out like this:

1) columns represent month

2) rows are grouped at a customer level and this group contains sales categories. Something like this:

Customer 1

Sales category 1

Sales category 2

...

Sales category N

Customer 2

Sales category 1

Sales category 2

...

Sales category N

Customer 3

...

etc.

Question:

How can I get Power BI to work with data in such a structure? Is it possible?

[MattAllington]

You would not get Power BI to work with this stucture, you would use Power BI to change the structure so something more appropriate.  You would use Power Query on data load (called Get Data in Power BI).  Connect to the data source and then transform the data so it is in the format

Date | Customer | Category | Sales Qty

Exactly how you do that will depend on the specifics of your file.  Power Query is pretty intuitive so you may be able to work it out.  If you get stuck, post a sample workbook back here and I will help you out.

[prokurors]

Ok, thanks!

I found out that it would be possible to use "Use first row as headers" in combination with "Unpivot columns" to flatten three dimensional data, like it is presented here

https://youtu.be/T97FQTIP6LA?t=636

but rows represent hierarchical data either... i.e. I have a row "Category" and there are some x rows that are "Sub-Categories" to this "Category" and then goes next "Cagegory"=>"Sub-category" row pack.

I have added a dummy sample file (with original data sheet and sheet that contains the level of transformation I managed to make)

What would be recommended way to transform that type of data to tabular / power bi valid data structure?

I expect result to be in a form something like this: 3 tables -> Countries", "Food types" and "Data". Then data would have FK to "Countries" and "Fodd types" +number of year and sales amount in that year (for that country & food type).

Thanks!

[MattAllington]

Can you post the sample?

[prokurors]

Oh, sorry - completely forgot 🙂

Here is the link to the sample file (I somehow don't see where can I attach file to my post...)

https://onedrive.live.com/redir?resid=3D6E330ACC72A355!20797&authkey=!APIjFs9VZ9en0zE&ithint=file%2c...

[MattAllington · Accepted solution]

well I just realised it was going to take me half an hour to type up the instructions on how to do this (one way anyway), and 5 mins to record a video.  So here is a video.  I hope I have correctly interpreted what you are trying to do.

BTW, Power Query is awesome

https://www.dropbox.com/sh/b4mug9j96xp0j7e/AABo0fXptDaQT2ifBbBggYyfa?dl=0

[prokurors]

Thanks! I managed to repeat these tasks in Power BI Desktop!