topic Re: Left outer join by combing two tables in DAX Commands and Tips

Left outer join by combing two tables

Skumar100 — Mon, 20 May 2024 04:21:14 GMT

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

Re: Left outer join by combing two tables

lbendlin — Mon, 20 May 2024 00:55:15 GMT

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?

Re: Left outer join by combing two tables

Anonymous — Mon, 20 May 2024 01:38:22 GMT

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.

Re: Left outer join by combing two tables

Skumar100 — Mon, 20 May 2024 02:30:40 GMT

Hi lbendlin Anonymous ,

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!

Re: Left outer join by combing two tables

Anonymous — Mon, 20 May 2024 02:58:52 GMT

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.

Re: Left outer join by combing two tables

Skumar100 — Mon, 20 May 2024 03:09:52 GMT

Hi Anonymous ,

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

Re: Left outer join by combing two tables

Anonymous — Mon, 20 May 2024 09:40:09 GMT

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.

Re: Left outer join by combing two tables

lbendlin — Mon, 20 May 2024 19:46:55 GMT

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"