cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arabiat995
New Member

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

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 

 

Capture.PNG

 

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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

vstephenmsft_1-1658901437731.png

 

Table2

vstephenmsft_2-1658901445187.png

 

After merging

vstephenmsft_0-1658901425453.png

 

 

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.

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

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

vstephenmsft_1-1658901437731.png

 

Table2

vstephenmsft_2-1658901445187.png

 

After merging

vstephenmsft_0-1658901425453.png

 

 

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
New Member

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

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


VijayP
Super User
Super User

@arabiat995 

Your Question is not clear

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.