Create an intersection table from reference and transaction tables

[witbi]

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

[wdx223_Daniel · Accepted solution]

= 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")

[wdx223_Daniel · Accepted solution]

= 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")

[witbi]

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")

[wdx223_Daniel · Accepted solution]

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")

[witbi]

Thank you! 

[witbi]

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