cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Community Champion
Community Champion

@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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors