SQL to DAX help request - Project Online data

Here is a walk-through of creating a single Join query in M:

What I did was start out with two CSV files:

 

one.csv

Column1,Column2
one,10
two,20
three,30
four,40
five,50
six,60
seven,70
eight,80
nine,90

 

two.csv

Column1,Column2
one,100
two,200
three,300
four,400
five,500
six,600
seven,700
eight,800
nine,900

 

The "normal" process is to create a query for both one.csv and two.csv, then go and edit one.csv query and add a Merge step to merge two.csv query. With this method, you wind up with 2 queries, the two.csv query is in effect an intermediate query. In the Advanced editor, the code looks like:

 

two.csv

let
    Source1 = Csv.Document(File.Contents("C:\Temp\two.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers1" = Table.PromoteHeaders(Source1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", Int64.Type}})
in
    #"Changed Type1"

 

one.csv

let
    Source = Csv.Document(File.Contents("C:\Temp\one.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column1"},two,{"Column1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"})
in
    #"Expanded NewColumn"

 

However, you can use the code from these queries to create a blank query that looks like this:

 

merge query

 

let
    Source = Csv.Document(File.Contents("C:\Temp\one.csv"),[Delimiter=",",Encoding=1252]),
    Source1 = Csv.Document(File.Contents("C:\Temp\two.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Promoted Headers1" = Table.PromoteHeaders(Source1),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", Int64.Type}})
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column1"},#"Changed Type1",{"Column1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"})
in
    #"Expanded NewColumn"

 

Now you can get rid of one.csv and two.csv queries and have a single query that does it all in one query!