Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago

    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
PwerQueryKees's avatar
PwerQueryKees
Super User
1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.