Bridge Query w/ Multiple Columns to Sub-Category Queries? (Power Query)

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
WHAT I HAVE NOW:
1600962534194.png


RESULT I NEED:
1600962974854.png


*DISCLAIMER- I realize this example is a little different, but don't try to interpret WHY we have tables this way- this is merely an example of our actual data (privacy restrictions). The data shown is only an example just to illustrate. I used Excel to show the Query/Tables, but I AM using Power Query to do all this.

I have a query of combinations of different products (all unique combinations), where each product in the combination is listed in a separate column (PRODUCT COMBOS table in the image above).

I then have separate queries for the sales data for each individual product, separated into different tables by category (ie- all the sales data for shoes is in the 'SHOES' query, all Pigs are in the 'PIGS' query, etc). Each cell is either a 1 or 0 representing if we sold ANY products that day or not (not quantity or price sold, merely a yes/no of "Did we sell ANY 'Pig Blue' on this date?")

Lastly, I have a query of the sales data listed by date.

I need to pull a pivot table together for all the PRODUCT COMBOS and their AVG TOTALS across all dates. To simplify- I want to look at every combination of products, find every date where we sold any of the products (represented by 1 in the sales table) in combination, and pull the average total across all those dates. ie- For all the days we sold any Dog White, shoe black and pig purple, what's the average total across all those days? Or in another example- For the past year, the days we sold 'shoe black and dog blue' we averaged $285.64. However, the days we sold 'Dog White, Shoe Black and Pig Purple' we averaged $1,125,89. This way we can compare the combinations against each other with their respective avg totals. Man I hope that makes sense!

I assume I need some sort of bridge queries that will list each individual product and their respective category/sales tables so Power Query knows where to go find the information on each individual product? Perhaps something like this?
1600963683437.png


And this is where I'm stuck... :)

I know it's not an easy example. I'd REALLY appreciate if someone could show me how to handle this? I'm up against a deadline and the boss is getting very antsy with me... :(
Thank you!
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,365
@SuperNerd, this is not a direct answer to your question or even for this particular question but could be a helpful suggestion to get faster answers or at least some solution attempts in MrExcel Message Board.

If you could post all those sample data above as copiable content instead of images, like pasting as tables, or by using the dedicated tool for this purpose, XL2BB, then it would be much easier to create your data set in a worksheet and try to solve it.
 

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Thank you. I was hoping for more of an explanation rather than any kind of code, since, what I need is to know how to do the queries... but thank you- I'll see if there's anything I can do with that.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,117
Messages
5,628,793
Members
416,340
Latest member
PJB1102

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top