topic Re: Create an intersection table from reference and transaction tables in Power Query

Create an intersection table from reference and transaction tables

witbi — Mon, 09 Jan 2023 05:40:21 GMT

Hello,

I have a reference table (a) with values that I would like to look up in a transaction table (b) across multiple columns "Location" and "Interests" and generate a new intersection table (c).

The reference table has text.startwith values like "cycl" so I can match "cycle" or "cycling". Duplicated relationships are ok as I can remove them later.

Any help would be most appreciated! Thank you and happy new year.

a) Reference table

ID_category	Value
1	UK
2	Canada
3	Australia
4	cycl
5	travel
6	read
7	theatre
8	cook
9	sport
10	garden

b) transaction table

ID_person	Name	Location	Interests
1	Alex	London, UK	Traveling, going to the theatre, gardening are my weekend pleasures
2	Bev	Toronto, Canada	I'm a world traveler. Most recently I cycled the UK on a 20 day food and cooking tour
3	Cath	Sydney, Ausstralia	I'm an avid sports fan. I belong to a sports travel club that's visited Canada for ice hockey and Europe for cycling
4	Dean	Glasgow, UK	I started a book club that reads 10 books a month. We're all avid readers!
5	Ed	Montreal, Canada	I get the most joy from cooking with the freshest ingredients direct from my garden

c) intersection table (new table)

ID_category	ID_person	Column
1	1	Location
1	5	Interests
1	10	Interests
2	2	Location
2	5	Interests
2	1	Interests
2	8	Interests
3	3	Location
3	9	Interests
3	2	Interests
3	4	Interests
4	1	Location
4	6	Interests
5	2	Location
5	8	Interests
5	10	Interests

Re: Create an intersection table from reference and transaction tables

wdx223_Daniel — Mon, 09 Jan 2023 06:28:53 GMT

= let a=Table.Buffer(Table.UnpivotOtherColumns(TransactionTable,{"ID_person","Name"},"Attributes","Value")) in Table.Sort(#table({"ID_person","ID_category","Custom"},List.TransformMany(Table.ToRows(Table.AddColumn(ReferenceTable,"n",(x)=>Table.SelectRows(a,each Text.Contains([Value],x[Value],Comparer.OrdinalIgnoreCase)))),each Table.ToRows(_{2}),(x,y)=>{y{0},x{0},y{2}})),"ID_person")

Re: Create an intersection table from reference and transaction tables

witbi — Mon, 09 Jan 2023 22:40:34 GMT

wdx223_Daniel this was spot on! Thank you for the new knowledge.

Re: Create an intersection table from reference and transaction tables

witbi — Wed, 11 Jan 2023 22:54:00 GMT

Hello again wdx223_Daniel

I'm also interested in the opposite case where the transaction table row does not contain the search value. Below isn't giving the desired result. Any help most appreciated. Thank you!

= let a=Table.Buffer(Table.UnpivotOtherColumns(TransactionTable,{"ID_person","Name"},"Attributes","Value")) in Table.Sort(#table({"ID_person","ID_category","Custom"},List.TransformMany(Table.ToRows(Table.AddColumn(ReferenceTable,"n",(x)=>Table.SelectRows(a,each not Text.Contains([Value],x[Value],Comparer.OrdinalIgnoreCase)))),each Table.ToRows(_{2}),(x,y)=>{y{0},x{0},y{2}})),"ID_person")

Re: Create an intersection table from reference and transaction tables

wdx223_Daniel — Thu, 12 Jan 2023 00:36:21 GMT

let me guess

= let a=Table.Buffer(TransactionTable) in Table.Sort(#table({"ID_person","ID_category"},List.TransformMany(Table.ToRows(Table.AddColumn(ReferenceTable,"n",(x)=>Table.SelectRows(a,each not List.Contains(List.Skip(Record.ToList(_),2),x[Value],(m,n)=>Text.Contains(m,n,Comparer.OrdinalIgnoreCase))))),each Table.ToRows(_{2}),(x,y)=>{y{0},x{0}})),"ID_person")

Re: Create an intersection table from reference and transaction tables

witbi — Mon, 16 Jan 2023 10:42:08 GMT

Thank you!