Create New Table from two tables mixing columns and rows

[JoaoMS]

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. 

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

Thanks in advance,

Joao

[edhans · Accepted solution]

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.

[v-diye-msft · Accepted solution]

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"

Pbix attached.

[v-diye-msft · Accepted solution]

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"

Pbix attached.

[edhans · Accepted solution]

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.

[JoaoMS · Accepted solution]

Thank you, it works perfect!

[edhans]

Great @JoaoMS . Glad your project is moving forward!