Left outer join by combing two tables

[Skumar100]

Hi All,
I am very new to power bi. I am trying to create a new output table using the calculated columns from the other table. I tried using the natural join function but I was un-able to create a new table sucessfully.

I know that this can be achieved easily in the sql but I am not quite familiar with the DAX. Any suggestions will be very helpful. Thanks 

Table 1 

Month Year	Cat
202401	c1
202402	c1
202403	c1
202404	c1
202405	c1
202401	c2
202402	c2
202403	c2

Table 2:

Month Year	Cat	Code	Date
202401	c1	1	23-Jan-24
202402	c1	1	2-Feb-24
202403	c2	2	31-Mar-24
202405	c1	1	24-May-24
202405	c1	2	25-May-24

Output:

Month Year	Cat	Code	Date
202401	c1	1	23-Jan-24
202402	c1	1	2-Feb-24
202403	c1
202404	c1
202405	c1	1	24-May-24
202405	c1	2	25-May-24
202401	c2
202402	c2
202403	c2	2	31-Mar-24
202404	c2
202405	c2

[lbendlin · Accepted solution]

Your expected output cannot be achieved with just a Left Outer Join. 

= Table.NestedJoin(#"Table 1", {"Month Year ", "Cat "}, #"Table 2", {"Month Year ", "Cat "}, "Table 2", JoinKind.LeftOuter)

Looks more like you wanted a cross join between [Month Year] and [Cat]  of Table 1 and then a left outer join with table 2.

= Table.NestedJoin(Table.ExpandTableColumn(Table.AddColumn(Table.Distinct(Table.SelectColumns(#"Table 1",{"Month Year "})), "Custom", each Table.Distinct(Table.SelectColumns(#"Table 1",{"Cat "}))), "Custom", {"Cat "}, {"Cat "}), {"Month Year ", "Cat "}, #"Table 2", {"Month Year ", "Cat "}, "Table 2", JoinKind.LeftOuter)

let
    Source = Table.NestedJoin(Table.ExpandTableColumn(Table.AddColumn(Table.Distinct(Table.SelectColumns(#"Table 1",{"Month Year "})), "Custom", each Table.Distinct(Table.SelectColumns(#"Table 1",{"Cat "}))), "Custom", {"Cat "}, {"Cat "}), {"Month Year ", "Cat "}, #"Table 2", {"Month Year ", "Cat "}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(Source, "Table 2", {"Code", "Date"}, {"Code", "Date"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table 2",{{"Cat ", Order.Ascending}, {"Month Year ", Order.Ascending}})
in
    #"Sorted Rows"

[Skumar100]

Hi @lbendlin @v-kaiyue-msft , 

Thanks for the your note. 

Table 1 & table 2 are created using DAX. So I couldnt find a way to modify using the power query.  Thanks! 

[v-kaiyue-msft]

Hi @Skumar100 ,

You can try to do this by creating a calculation table.

Table 3 = 
VAR _table1 = ADDCOLUMNS('Table',"Code",BLANK(),"Date",BLANK())
RETURN
UNION(_table1,'Table 2')

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Skumar100]

Hi @v-kaiyue-msft , 

Thanks for the quick update. 

for the record c1 - 202405 - I am getting one record from table 1 and 2 records from table 2. 

Table 1 - each Cat has a monthyear stamp ie it is a continuous

[v-kaiyue-msft]

Hi @Skumar100 ,

Sorry, I don't quite understand what you mean, can you describe it in a more visual and detailed way?

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kaiyue-msft]

Hi @Skumar100 ,

Thanks for the reply from @lbendlin , please allow me to provide another insight: 

1. In the power query editor, select append queries.

2. In the pop-up window, set the settings as shown below.

3. Click the triangle next to the cat column to sort them.

For more details, please refer to the documentation: Append queries - Power Query | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The preferred way in Power BI is to let the data model do the work for you. What's your reason for needing a materialized output?