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