Merge but with single record on left table and multiple records on right table

[Michael B]

I am relatively new to power query, and l am finding it quite intuitive however l cannot fathom out how to achieve the 'Result' as per the example below. I would be most grateful if somebody could provide detailed instructions to achieve this. TIA.

 

[alansidman]

let
    T1 = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="TableB"]}[Content],
    MQ = Table.NestedJoin(T1, {"Task ID"}, T2, {"Task ID"}, "Table2", JoinKind.FullOuter),
    ExpandedTable = Table.ExpandTableColumn(MQ, "Table2", {"Task ID", "Amount"}, {"Task ID.1", "Amount.1"}),
    SortRows = Table.Sort(ExpandedTable,{{"Task ID", Order.Ascending}}),
    GroupedRows = Table.Group(SortRows, {"Task ID"}, {{"Data", each _, type table [Task ID=text, Amount=number, Task ID.1=text, Amount.1=number]}}),
    AddedCustomColumn = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([Data],"Row",1,1)),
    RemovedColumns = Table.SelectColumns(AddedCustomColumn,{"Custom"}),
    ExpandedCustomColumn = Table.ExpandTableColumn(RemovedColumns, "Custom", {"Task ID", "Amount", "Task ID.1", "Amount.1", "Row"}, {"Task ID", "Amount", "Task ID.1", "Amount.1", "Row"}),
    FilterFor1 = Table.AddColumn(ExpandedCustomColumn, "Custom", each if [Row]=1 then [Task ID] else null),
    #"Added Custom" = Table.AddColumn(FilterFor1, "Custom.1", each if [Row]=1 then [Amount] else null),
    RemoveCol = Table.RemoveColumns(#"Added Custom",{"Task ID","Row","Amount"}),
    MoveColumn = Table.ReorderColumns(RemoveCol,{"Custom", "Custom.1","Task ID.1", "Amount.1"}),
    RenamedColumns = Table.RenameColumns(MoveColumn,{{"Custom", "Task ID"},{"Custom.1","Amount"}})


in
    RenamedColumns

 

[Michael B]

Many thanks for this, it is great and does exactly what l asked, l really appreciate the help.

May l ask if you wrote the M code from scratch or did it all using the power query ribbon?

If it is the latter can l ask how you assigned the query steps to the variables T1, T2, MQ ?

 

[alansidman]

the first was using the UI. I then changed the source to T1 manually. I then copied the first line of code and pasted it to the second line. I changed the T2 line and the table name. I then wrote the Mcode to merge the two tables. If you choose to not do it this way, you can import both tables to PQ and then merge the two tables. I preferred to do it this way so as to not have three queries, but one. Also, you can change the step names in the UI by renaming them.

 

[pinarello]

A little bit shorter:

let
T1 = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name="TableB"]}[Content],
MQ = Table.NestedJoin(T1, {"Task ID"}, T2, {"Task ID"}, "Table2", JoinKind.FullOuter),
idx_to_Table2 = Table.AddColumn(MQ, "idx", each Table.AddIndexColumn([Table2], "idx", 1, 1)),
Expand_idx = Table.ExpandTableColumn(idx_to_Table2, "idx", {"Task ID", "Amount", "idx"}, {"Task ID.1", "Amount.1", "idx.1"}),
Custom1 = Table.ReplaceValue(Expand_idx, each [Task ID], each if [idx.1] > 1 then "" else [Task ID], Replacer.ReplaceValue,{"Task ID"}),
Custom2 = Table.ReplaceValue(Custom1, each [Amount], each if [idx.1] > 1 then "" else [Amount], Replacer.ReplaceValue,{"Amount"}),
Keep_needed = Table.SelectColumns(Custom2,{"Task ID", "Amount", "Task ID.1", "Amount.1"})
in
Keep_needed

 

[ONG]

Hi,

I've a litter bit different to handle it.

BR
ONG CHEE SENG

let
    Source = TableA,
    ColNames = Table.ColumnNames(Source),
    Merged = Table.NestedJoin(Source,{"Task_ID", "Amount"},TableB,{"Task_ID", "Amount"},"Table2",JoinKind.LeftOuter),
    AddedCol = Table.AddColumn(Merged, "New", each
                        let
                            a = List.Transform(Table.ToColumns([Table2]), each {_{0}}) & Table.ToColumns([Table2]),
                            b = Table.FromColumns(a, ColNames & List.Transform(ColNames, each "TableB." & _))
                        in b, type table)[New],
    Custom1 = Table.Combine(AddedCol)
in
    Custom1