How to Transform this Table for Power Pivot?

[SuperNerd]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1			SALES							COMBOS
2	DATE	SOCKS	PANTS	SHOES	SHIRTS	WATCHES		1PRODUCT	2PRODUCTS	3PRODUCTS	4PRODUCTS	5PRODUCTS
3	2/12/2019		1			1		SOCKS
4	2/13/2019	1		1	1			PANTS	WATCHES
5	2/14/2019	1	1					SHIRTS	WATCHES	PANTS
6	2/15/2019				1			WATCHES	PANTS	SHIRTS	SOCKS
7	2/16/2019	1		1				SHIRTS	SHOES	SOCKS	PANTS
8	2/17/2019		1			1		WATCHES
9	2/18/2019							SHOES	PANTS
10	2/19/2019		1	1		1		SHIRTS	SHOES	SOCKS	PANTS	WATCHES
11	2/20/2019							WATCHES
12	2/21/2019	1						PANTS	SHIRTS	SOCKS
Sheet1

I have a SALES table with a products marked as a true/false (true marked as '1') showing if a specific product was sold on a specific date.

I have a COMBOS table with a wide range of combinations of products. It ranges from 1 product up to 5 different products, in any order.

I need to compare each combination row to the sales table and get a list of matched dates for each combination.

For example- I need a list of every time the PANTS and WATCHES combination was sold together, regardless if anything else was sold on the same date or not. SO- the resulting list would include:
2/12/2019
2/17/2019
2/19/2019 *- Notice there was also shoes sold on this date, but that doesn't matter, I still need it included in the list since PANTS and WATCHES were still sold together on this day.

I've been using power query to do this since the dataset is large. Can someone tell me how to manipulate the data so I can either pivot or merge and get the resulting lists of dates?
Thank you!

 

[sandy666]

is that what you want?
if not post expected result from your example

Sum of Value		Attribute.1
DATE	Attribute	1PRODUCT	2PRODUCTS	3PRODUCTS	4PRODUCTS	5PRODUCTS
12/02/2019	PANTS	1	1	1	1
	WATCHES	1	1			1
13/02/2019	SHIRTS	1	1	1
	SHOES	1	1
	SOCKS	1		1	1
14/02/2019	PANTS	1	1	1	1
	SOCKS	1		1	1
15/02/2019	SHIRTS	1	1	1
16/02/2019	SHOES	1	1
	SOCKS	1		1	1
17/02/2019	PANTS	1	1	1	1
	WATCHES	1	1			1
19/02/2019	PANTS	1	1	1	1
	SHOES	1	1
	WATCHES	1	1			1
21/02/2019	SOCKS	1		1	1

 

[SuperNerd]

First off- THANK YOU for responding. I sincerely appreciate ALL of your help- you've been awesome!

For some reason I can't seem to get the XL2BB to work now. I'll keep trying, but here's an image of what I'm thinking. I'm looking to do this in Power Query anyways- so maybe you can just explain how to do it?

Honestly, I don't even need a resulting table. I just need a way to merge these together in power query. The result of the merge would be a table in each row/column, that when expanded has the all the dates for every time that combination of products was sold. I'll be using the results of this merge to merge in with another column or two. Remember, the matching results are not limited to ONLY exact matches- as long as ALL of the variables in the combinations are in the matched row, it should count it (ie- there can be OTHER variables on the same matched row/date that are NOT in the combination)- if that makes sense...

Thank you!

 

[sandy666]

try

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source1, {"DATE"}, "Attribute", "Value"),
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    UC = Table.UnpivotOtherColumns(Source2, {}, "Attribute", "Value"),
    Join = Table.NestedJoin(UOC,{"Attribute"},UC,{"Value"},"Table2",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table2", {"Attribute"}, {"Attribute.1"}),
    Distinct = Table.Distinct(Expand),
    Type = Table.TransformColumnTypes(Distinct,{{"DATE", type date}})
in
    Type

anyway, I'd like to see whole procedure you did
and see END result (and expected result)

 

[SuperNerd]

So can I just put this code into the Advanced section in Power Query (and change the names to match)? I can't use Excel worksheet as there's too much data/rows...

Thank you!

 

[sandy666]

put this code into the Advanced section in Power Query

no, but Advanced Editor yes

and change the names to match

You can do what you want

I can't use Excel worksheet as there's too much data/rows

Excel worksheet is limited to 1,048,576 rows by 16,384 columns

 

[SuperNerd]

UPDATE- this essentially worked. The lesson learned is if/when I have 2 tables in different formats, I can join them if I unpivot both first. Now, I have another issue (out of memory errors)- but starting a new thread for that one.
Thank you for your help!!!

 

[sandy666]

You are welcome & thanks for the feedback
Have a nice day