Forum Discussion
Create an intersection table from reference and transaction tables
- 3 years ago
= 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")
- 3 years ago
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")
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")
Thank you!