Forum Discussion
Left outer join by combing two tables
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 |
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"
7 Replies
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?
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.
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.
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
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"
