Query- How to work with Grouped data in Power BI

[Anonymous]

Hello All,

I'm trying to work with grouped excel source data. I'm new to Power BI.

Please find the dummy data above. This is how the source data (in excel) has been provided. 

I would like to develop a report where i can present the 'Price' as KPI with other Category, Sub- category as my 'Filters' in the Power BI dashboard. 

How do I do it in Power BI ?

Any suggestions.

Thank you.

Regards, 
Varun

[edhans]

I believe you are going to need to set up some tables. First of all the data you show isn't consistent. For example, the products under Chocolate are not at teh same level as the products under Cakes, Savouries, Beverages.

If you break the data down to the product, then various categories, then this would be a snap.

I'm struggling though to figure out how to do this in Power Query using the source data as it is. Grouped data isn't "real" in Excel. It is just rows that have been grouped together. I cannot see a consistent pattern to start extracting the data into the varoius categories.

I'm not even sure why the data is grouped as it is as there are no helpful subtotals.

If you created a table that has something like:

Product/Class/Category/SubCategory

Coca Cola/Food/Sweets/Beverages/

Loreal/Personal Care/Conditioner

Then you could delete all of the rows that had no distributor information in Power Query, load the data and the category table into Power BI, and you could start to work.

[Anonymous · Accepted solution]

Hi Edhans,

Thanks for your reply.

The grouping was wrong in my example. 

The chart below should be a better example. 

Basically, the number of categories and subcategories are not fixed. Might change in subsequent month. There is no unique field to identify how to segregate this data into unique columns. 

But for now, we have managed to use Dataiku to transform the excel file into the required output. Let me fix this with transformed excel file.

Thanks,

Regards,

Varun

[v-cherch-msft]

Hi @Anonymous

It seems you've solved it. Please accept your answer as solution, that way, other community members will easily find the solution when they get same issue. 

Regards,

Cherie