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...
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
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.