cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoaoMS
Helper II
Helper II

Create New Table from two tables mixing columns and rows

Hi, I appreciate your help with this issue. I have two tables, one called "Requriment_Table" showing a list of Purchase Orders (PO) issued along with the date of the PO and the quantity required of an item, and a second table called "Delivery_Table", which shows for each PO how many items have been delivered in an specifical date (note: not all the items of a PO are usually delivered together). What I want is to create a new table "Output" that mixes both tables as shown in the next figure. 

 

New_Table.PNG

For the PO which has not yet any delivery, the columns "Date_Deliv" and "Qty_Deliv" should be empty.

Thanks in advance,

Joao

 

3 ACCEPTED SOLUTIONS
edhans
Super User
Super User

You really want to do this in Power Query, not in DAX.

  1. Click Transform in the Power BI ribbon.
  2. Find the Requirements table, click on the PO column, and click MERGE on the Home ribbon of Power Query.
  3. Select the Delivery table and also click on the PO column.
  4. Select Left Outer Join. This is pretty safe as it seems you will have requirements but not delivery on occasion, but never delivery without requirement. This will pull everything from the Requirements table and all Delivery items that match, and will return NULL when they don't
  5. Click on the double-arrow in the upper right of the new column that has 'Table' listed all through it and check the Date_Deliv and Qty_Deliv columns. Be sure you uncheck the "keep column name" box at the bottom. Click ok.
  6. That is your new table.

Now you need to go to the Delivery table and right-click and uncheck "Enable Load" so it doesn't load into the model. Your Requirements table will now load with all of the relevant columns.

Microsoft has a visual walkthough here.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

v-diye-msft
Community Support
Community Support

Hi @JoaoMS 

 

Kindly check below results:

let
    Source = Table.NestedJoin(Requirement_Table, {"PO"}, Delivery_Table, {"PO"}, "Delivery_Table", JoinKind.LeftOuter),
    #"Expanded Delivery_Table" = Table.ExpandTableColumn(Source, "Delivery_Table", {"PO", "Date_Deliv", "Qty_Deliv"}, {"Delivery_Table.PO", "Delivery_Table.Date_Deliv", "Delivery_Table.Qty_Deliv"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Delivery_Table",{"Delivery_Table.PO"})
in
    #"Removed Columns"

007.PNG

Pbix attached.

 

Community Support Team _ Dina Ye
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

Thank you, it works perfect!

 

View solution in original post

4 REPLIES 4
v-diye-msft
Community Support
Community Support

Hi @JoaoMS 

 

Kindly check below results:

let
    Source = Table.NestedJoin(Requirement_Table, {"PO"}, Delivery_Table, {"PO"}, "Delivery_Table", JoinKind.LeftOuter),
    #"Expanded Delivery_Table" = Table.ExpandTableColumn(Source, "Delivery_Table", {"PO", "Date_Deliv", "Qty_Deliv"}, {"Delivery_Table.PO", "Delivery_Table.Date_Deliv", "Delivery_Table.Qty_Deliv"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Delivery_Table",{"Delivery_Table.PO"})
in
    #"Removed Columns"

007.PNG

Pbix attached.

 

Community Support Team _ Dina Ye
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

edhans
Super User
Super User

You really want to do this in Power Query, not in DAX.

  1. Click Transform in the Power BI ribbon.
  2. Find the Requirements table, click on the PO column, and click MERGE on the Home ribbon of Power Query.
  3. Select the Delivery table and also click on the PO column.
  4. Select Left Outer Join. This is pretty safe as it seems you will have requirements but not delivery on occasion, but never delivery without requirement. This will pull everything from the Requirements table and all Delivery items that match, and will return NULL when they don't
  5. Click on the double-arrow in the upper right of the new column that has 'Table' listed all through it and check the Date_Deliv and Qty_Deliv columns. Be sure you uncheck the "keep column name" box at the bottom. Click ok.
  6. That is your new table.

Now you need to go to the Delivery table and right-click and uncheck "Enable Load" so it doesn't load into the model. Your Requirements table will now load with all of the relevant columns.

Microsoft has a visual walkthough here.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Thank you, it works perfect!

 

View solution in original post

Great @JoaoMS . Glad your project is moving forward!

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!