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