How to Transform this Table for Power Pivot?

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDEFGHIJKL
1SALESCOMBOS
2DATESOCKSPANTSSHOESSHIRTSWATCHES1PRODUCT2PRODUCTS3PRODUCTS4PRODUCTS5PRODUCTS
32/12/201911SOCKS
42/13/2019111PANTSWATCHES
52/14/201911SHIRTSWATCHESPANTS
62/15/20191WATCHESPANTSSHIRTSSOCKS
72/16/201911SHIRTSSHOESSOCKSPANTS
82/17/201911WATCHES
92/18/2019SHOESPANTS
102/19/2019111SHIRTSSHOESSOCKSPANTSWATCHES
112/20/2019WATCHES
122/21/20191PANTSSHIRTSSOCKS
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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
is that what you want?
if not post expected result from your example
Sum of ValueAttribute.1
DATEAttribute1PRODUCT2PRODUCTS3PRODUCTS4PRODUCTS5PRODUCTS
12/02/2019PANTS1111
WATCHES111
13/02/2019SHIRTS111
SHOES11
SOCKS111
14/02/2019PANTS1111
SOCKS111
15/02/2019SHIRTS111
16/02/2019SHOES11
SOCKS111
17/02/2019PANTS1111
WATCHES111
19/02/2019PANTS1111
SHOES11
WATCHES111
21/02/2019SOCKS111
 
Upvote 0
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?

1601215429082.png


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!
 
Upvote 0
try
Power Query:
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)
 
Upvote 0
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!
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top