Forum Discussion
One to Many Match
I have a table with invoice data, each row unique. There is a related table with comments. I face issues combining when there is more than 1 comment and do want to bring this information back without duplicating the invoice data.
Any help getting to the DESIRED MERGE RESULT grateful:
do merge to get merged_result and then
let merged_result = Excel.CurrentWorkbook(){[Name="MERGED_RESULT"]}[Content], nulls = List.Buffer(List.Repeat({null}, 5)), data = List.Buffer(Table.ToRows(merged_result)), gen = List.Generate( () => [i = 0, row = data{0}, first_five = List.FirstN(row, 5), replace = false], (x) => x[i] < List.Count(data), (x) => [i = x[i] + 1, row = data{i}, first_five = List.FirstN(row, 5), replace = first_five = x[first_five]], (x) => if x[replace] then nulls & List.Skip(x[row], 5) else x[row] ), tbl = Table.FromRows(gen, Table.ColumnNames(merged_result)) in tbl
11 Replies
do merge to get merged_result and then
let merged_result = Excel.CurrentWorkbook(){[Name="MERGED_RESULT"]}[Content], nulls = List.Buffer(List.Repeat({null}, 5)), data = List.Buffer(Table.ToRows(merged_result)), gen = List.Generate( () => [i = 0, row = data{0}, first_five = List.FirstN(row, 5), replace = false], (x) => x[i] < List.Count(data), (x) => [i = x[i] + 1, row = data{i}, first_five = List.FirstN(row, 5), replace = first_five = x[first_five]], (x) => if x[replace] then nulls & List.Skip(x[row], 5) else x[row] ), tbl = Table.FromRows(gen, Table.ColumnNames(merged_result)) in tblList.Generate goes row by row and compares first 5 items of current and previous rows of "merged" table. If they are equal then first 5 items of current row are replaced by nulls.
Would it be ok to CONCATENATEX the comments into a single string?
Just Use Merge command
You can filter the table on the one side first then use merge
Use the merge. This will not produce repeates on the "one" side.
If I understand you correctly you would like to have 3 new fields, each concatenating the multiple values for the field at the many side.
Then for each column you want to expand, add a powerquery step like this:
= Table.AddColumn(#"Merged Queries", "COMMENT", each Text.Combine([Many Table][COMMENT], ", "))
Where #"Merged Queries" is your merge result (or the previous step), COMMENT is the name of the column you want to expand and [Many Table] is the name of the Field produced by the merge, containing the merged records (as a table).Hi,
Thanks for the solutions everyone offered,and i want to offer some more informtion for user to refert to.
hello Anonymous ,you can create two blank queries and refer to the following code in advanced editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZLBCoAgEET/xbNgztrXhHSwjmZU/09RhxKVbW/LwjC8xwyDCmm0SivbGRh0cNc9Pa913va03GdIMc7Lcd1evxHbiuATQRZxWQR/WvpWpN2Ss5CchZgWlCycMZQsnDGULJwxlCycsQoLZ6zCwhkj+cZIvjGSb4zkG6u0cMYqLW1j/gQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"Comment Date" = _t, #"Document No" = _t, Commenter = _t, Comment = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Comment Date", type text}, {"Document No", type text}, {"Commenter", type text}, {"Comment", type text}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Comment Date", type date}}, "en-GB") in #"Changed Type with Locale"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs6PN1TSUTI00DfSNzIwMgGyUyBCZalgysjAQClWB6HQBEWhEUShEdgIVIWoJhpDFIIoQ1O4QiNME7FbbYRpInarsZiI3WpjYn1tTKyvsShEtzoWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Date = _t, #"Document No" = _t, Vendor = _t, Amount = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Date", type date}}, "en-GB"), #"Merged Queries" = Table.NestedJoin(#"Changed Type with Locale", {"Company", "Document No"}, #"Query1", {"Company", "Document No"}, "Table (2)", JoinKind.LeftOuter), #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Comment Date", "Commenter", "Comment"}, {"Comment Date", "Commenter", "Comment"}), #"Grouped Rows" = Table.Group(#"Expanded Table (2)", {"Company", "Document No"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Vendor", "Amount", "Comment Date", "Commenter", "Comment", "Index"}, {"Date", "Vendor", "Amount", "Comment Date", "Commenter", "Comment", "Index"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Data",each [Company],each if [Index]=1 then [Company] else "",Replacer.ReplaceValue,{"Company"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Document No],each if [Index]=1 then [Document No] else "",Replacer.ReplaceValue,{"Document No"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Date],each if [Index]=1 then [Date] else null,Replacer.ReplaceValue,{"Date"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",each [Vendor],each if [Index]=1 then [Vendor] else "",Replacer.ReplaceValue,{"Vendor"}), #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",each [Amount],each if [Index]=1 then [Amount] else null,Replacer.ReplaceValue,{"Amount"}), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value4",{"Index"}) in #"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
