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...
  • AlienSx's avatar
    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