Merge two queries but show the one to many relationship between them

[arabiat995]

hello guys 

I have two quires that I want to join but I need them to show the one-to-many relationships based on the 'order_id' column 

each time I use merge quires it duplicates all values in query 1 and I want them to show as null or blank like in the picture 

please help 

[v-stephen-msft · Accepted solution]

Hi @arabiat995 ,

As a workaround, you can add a index column group by [Order ID TAB.1]. Then create the custom columns to return the columns you want. You can download my attachment to see my detailed steps.

let
    Source = Table.NestedJoin(Table, {"Order ID TAB.1"}, #"Table (2)", {"Order ID from TAB.2"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Order ID from TAB.2", "Product", "Service Type", "Qty", "Price", "Total Product Price"}, {"Order ID from TAB.2", "Product", "Service Type", "Qty", "Price", "Total Product Price"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table (2)", {"Order ID TAB.1"}, {{"Count", each _, type table [Order ID TAB.1=nullable number, User Number=nullable number, Driver ID=nullable number, Order ID from TAB.2=nullable number, Product=nullable text, Service Type=nullable text, Qty=nullable number, Price=nullable number, Total Product Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"User Number", "Driver ID", "Order ID from TAB.2", "Product", "Service Type", "Qty", "Price", "Total Product Price", "Index"}, {"User Number", "Driver ID", "Order ID from TAB.2", "Product", "Service Type", "Qty", "Price", "Total Product Price", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index]=1 then [Order ID TAB.1] else ""),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Index", "Order ID TAB.1", "User Number", "Driver ID", "Order ID from TAB.2", "Product", "Service Type", "Qty", "Price", "Total Product Price"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Order ID TAB.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Order ID from TAB.1"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Index]=1 then [User Number] else ""),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom2",{"Order ID from TAB.1", "Custom", "Index", "User Number", "Driver ID", "Order ID from TAB.2", "Product", "Service Type", "Qty", "Price", "Total Product Price"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns1",{"User Number"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "User Number"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [Index] =1 then [Driver ID] else ""),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom3",{"Order ID from TAB.1", "User Number", "Custom", "Index", "Driver ID", "Order ID from TAB.2", "Product", "Service Type", "Qty", "Price", "Total Product Price"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns2",{"Driver ID"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"Custom", "Driver ID"}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns2",{"Index"})
in
    #"Removed Columns4"

Table1

Table2

After merging

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arabiat995]

@VijayP  sorry, I want to join the two quires, I need the merged query to look like the above picture 

[VijayP]

@arabiat995 Then you must have two tables. one the Data with Transactions and anotehr with Codes

and in both the tables one column should be similar interms of data and data type

and in the Code table data should be unique values (no duplicates)

you can use left outer join to get the final result

[VijayP]

@arabiat995 

Your Question is not clear

Whether you want to join or create a relationship? Do you have both tables or only one table?