Forum Discussion
Create an intersection table from reference and transaction tables
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 |
= 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")
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")
5 Replies
= 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 this was spot on! Thank you for the new knowledge.
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")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")
