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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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!
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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)
 

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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!
 

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
You are welcome & thanks for the feedback
Have a nice day
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

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