One to Many Match

[chrisjbrogan]

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:

[v-xinruzhu-msft]

Hi,

Thanks for the solutions everyone offered,and i want to offer some more informtion for user to refert to.

hello @chrisjbrogan ,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.

[AlienSx · Accepted solution]

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

[chrisjbrogan]

this works well, I used it in bi rather than get from excel.

Do you mind giving an overview of what the steps actually do - though it works it would be great to understand the logic.

[AlienSx]

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

[PwerQueryKees]

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

[chrisjbrogan]

Ive used mereg and as per the screenshot it does repeat. You can see in the top right that there are duplicates in the first 5 columns - the many side is fine.

The desired result bottom right does not have the duplicate invoices.

[Omid_Motamedise]

Just Use Merge command

[chrisjbrogan]

how does this help with the avoidance of the repeating of the "one" side

[Omid_Motamedise]

You can filter the table on the one side first then use merge

[lbendlin]

Would it be ok to CONCATENATEX the comments into a single string?

[chrisjbrogan]

ideally not as I want the comment date, commenter and comment as fields.