Forum Discussion
CMMaliniJoshi
Helper I
Helper ILookup 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...
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
Community Champion
Community Championlet
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
CMMaliniJoshiHelper I
Helper IDear wds223_Daniel
The solution is giving me correct output on my sample data. The solution code provided by you is extra ordinary for a beginner level person like me. However, I will have to test it on my actual data so that I can check if it can finish working within a reasonable amount of time. Kindly bear with me till that time. I also request you guide how this code is working because in a single step (Custom1), there are many inner steps which I could not figure out. Hence, I will be thankfull if you can advise, in the mean time, how it works.
I am thankful to you for taking interest in my query.
Regards
- wdx223_Daniel
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
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