Forum Discussion

CMMaliniJoshi's avatar
CMMaliniJoshi
Icon for Helper I rankHelper I
3 years ago
Solved

Lookup multiple items in a list and merge matching data

Dear All Kindly help me for below mentioned situation I have two tables having data like as under: Table1 : ID_NO1,OTHER_COLUMN1,OTHER_COLUMN2,OTHER_COLUMN3,OTHER_COLUMN4,OTHER_COLUMN5,OTHER_COLU...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc65DcMwDEDRXVS7iG679CkfCyQxvIGRLvuH+mxS8LOg8KDzNL1pTN9b6TA46Th66TQF6TxH6bIkaSm5vpH5fO9b1rq20m3rpPtuH7KOw1pzNacZQR2oBw2gETSBZtBqzPWG2CFiCWmVdJALpIcMkBEyQWbIFrIi5f+feMJaZZ2yHvYJG2AjbILNsC0sYEF5ybwVtYo6Rb2iwVzXDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_NO1 = _t, OTHER_COLUMN1 = _t, OTHER_COLUMN2 = _t, OTHER_COLUMN3 = _t, OTHER_COLUMN4 = _t, OTHER_COLUMN5 = _t, OTHER_COLUMN6 = _t, OTHER_COLUMN7 = _t, ID_NO2 = _t, ID_NO3 = _t, OTHER_COLUMN8 = _t, OTHER_COLUMN9 = _t, OTHER_COLUMN10 = _t, OTHER_COLUMN11 = _t]),
    Table2= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HdC5FYRADATRXGRjiBtMkMQeKcyb/NPYrXa+U161ZpcNdo1/bghIqNH60OwmT2QISKhJOcgzGQISalZO8kKGgIRalIu8kiEgoVblh7yRISChNuUXeSdDQELtym/yQYaAhDqUP+STDAEJdSp/tcX1xTXGdca1xq33Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_NO = _t, INFO1 = _t, INFO2 = _t, INFO3 = _t, INFO4 = _t, INFO5 = _t]),
    Custom1 = let ColumnListToTransform={"ID_NO1","ID_NO2","ID_NO3"} in Table.FromRecords(List.TransformMany(Table.ToRecords(Table1),each List.RemoveNulls(List.Transform(Record.ToList(Record.SelectFields(_,ColumnListToTransform,1)),each Table2{[ID_NO=_]}?)),(x,y)=>Record.RemoveFields(x,ColumnListToTransform)&y))
in
    Custom1
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago
let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc65DcMwDEDRXVS7iG679CkfCyQxvIGRLvuH+mxS8LOg8KDzNL1pTN9b6TA46Th66TQF6TxH6bIkaSm5vpH5fO9b1rq20m3rpPtuH7KOw1pzNacZQR2oBw2gETSBZtBqzPWG2CFiCWmVdJALpIcMkBEyQWbIFrIi5f+feMJaZZ2yHvYJG2AjbILNsC0sYEF5ybwVtYo6Rb2iwVzXDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_NO1 = _t, OTHER_COLUMN1 = _t, OTHER_COLUMN2 = _t, OTHER_COLUMN3 = _t, OTHER_COLUMN4 = _t, OTHER_COLUMN5 = _t, OTHER_COLUMN6 = _t, OTHER_COLUMN7 = _t, ID_NO2 = _t, ID_NO3 = _t, OTHER_COLUMN8 = _t, OTHER_COLUMN9 = _t, OTHER_COLUMN10 = _t, OTHER_COLUMN11 = _t]),
    Table2= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HdC5FYRADATRXGRjiBtMkMQeKcyb/NPYrXa+U161ZpcNdo1/bghIqNH60OwmT2QISKhJOcgzGQISalZO8kKGgIRalIu8kiEgoVblh7yRISChNuUXeSdDQELtym/yQYaAhDqUP+STDAEJdSp/tcX1xTXGdca1xq33Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_NO = _t, INFO1 = _t, INFO2 = _t, INFO3 = _t, INFO4 = _t, INFO5 = _t]),
    ColumnListToTransform = {"ID_NO1","ID_NO2","ID_NO3"},
    Custom1 = Table.Unpivot(Table1,ColumnListToTransform,"attr","ID_NO"),
    Custom2 = Table.SelectRows(Custom1,each [ID_NO]<>null and [ID_NO]<>"null" and [ID_NO]<>""),
    Custom3 = Table.RemoveColumns(Custom2,"attr"),
    Custom4 = Table.NestedJoin(Custom3,"ID_NO",Table2,"ID_NO","n",JoinKind.Inner),
    #"Expanded n" = Table.ExpandTableColumn(Custom4, "n", {"INFO1", "INFO2", "INFO3", "INFO4", "INFO5"}, {"INFO1", "INFO2", "INFO3", "INFO4", "INFO5"})
in
    #"Expanded n"

this can be deployed step by step

CMMaliniJoshi's avatar
CMMaliniJoshi
Icon for Helper I rankHelper I
3 years ago

Dear wdx223_Daniel

Sorry for the late revert.  Kindly note that -

1. The second solution, too, provided by you is nice.  However, since it would result in increasing the number of rows in my Table1 data, I preferred the solution no 1. provided by you.

2.  I have used that Solution No.1 on my actual data and it has successfully completed my requirement.  Therefore I am marking it as an "Accepted Solution".  I am thankfull to you for providing me the help.  

Regards