Forum Discussion

JoaoMS's avatar
JoaoMS
Icon for Helper III rankHelper III
6 years ago
Solved

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 a...
  • edhans's avatar
    6 years ago

    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's avatar
    6 years ago

    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.

     

  • JoaoMS's avatar
    JoaoMS
    6 years ago

    Thank you, it works perfect!