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

[SuperNerd]

WHAT I HAVE NOW:

RESULT I NEED:

*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?

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!

 

[smozgur]

@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]

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.