M query or DAX query for left outer join

[Anonymous]

Need to create dax query or Mquery to join two tables SCD and Fosa on the key Internal_Key which is acomposite key formed with 

Internal_Key = SCD[Stichtag] & "_" & SCD[Depot] & "_" & SCD[WP ID]  in SCD Table

and  

Internal_Key = Fosa[Stichtag] & "_" & Fosa[Depot] & "_" & Fosa[WP ID] in FoSA Table

Can anyone help me with a dax script or M query?

[v-diye-msft · Accepted solution]

Hi @Anonymous 

I’ve created 2 tables as requested, both of them added the calculated column “Internal_Key”, and we’d like to merge them based on that special column.

Assuming Fosa as the left table, and SCD as the right one.

1. Using the function Addcolumns:

Table 2 = ADDCOLUMNS(Fosa,"SCD.WP ID",RELATED(SCD[WP ID]),"SCD.Stivhtag",RELATED(SCD[Stichtag]),"SCD.Depot",RELATED(SCD[Depot]),"SCD.Internal Key",RELATED(SCD[Internal_Key]))

2. Using Merge option id query editor:

Create the custom column in both 2 tables using M code:

= Table.AddColumn(#"Changed Type1", "Custom", each Text.Combine({[Stichtag],"_",[Depot],"_",[WP ID]}))

And then Merge them using M code:

= Table.ExpandTableColumn(Source, "SCD", {"Stichtag", "Depot", "WP ID", "Custom"}, {"SCD.Stichtag", "SCD.Depot", "SCD.WP ID", "SCD.Custom"})

Regards,

Dina