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

Michael B

New member
Joined
Jun 21, 2024
Messages
2
Reaction score
0
Points
1
Location
England
Excel Version(s)
Excel 365
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.

1719059981243.png
 
Code:
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
 

Attachments

  • PQ Merger.xlsx
    18.5 KB · Views: 2
Last edited:
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 ?
 
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.
 
Last edited:
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
 
Back
Top