PoweQuery - Compare rows between two queries

[Mekrel]

Hi all,

I'm really struggling with this as I'm not that great with PowerQuery and I think this will require something more advanced.

Essentially - I have two reports, one with total sales and one with products split out. There is "masked" data when the product is split, i.e. the total sales from the product split doesn't add up to the total sales.

Therefore I want to:

• Compare the sales for the month from product split, to the total sales for the month - to find the difference
• Add the difference into the sales split table/query e.g. in the below example, it will return 50 for Jan, Feb, March and so on (as that's the difference between the roll up and the total)

Product split


Total


Does anyone know how I would go about this?

 

[gpuerrilla]

Just trying to digest this. Baddum tsst.

Get Data from Product report.
Transform if you need to clean it.
Unpivot the Sales with your Brand.
Group.

 

[gpuerrilla]

If your tables are like your example, you could also just add an index to both as you import each data set. Merge the two tables by using the index as their unique keys.
Add custom column when you deduct [total sales] - [product sales] calling it 'difference'.

Like so: