Unable to figure out the correct way to accomplish join on matching column.

[Gayatrik]

Hi All,

I have 2 tables (TABLE1 and TABLE2), that have 1 column(ColumnX) in common. Below are examples of the two tables and what resultant table I need from joining both of them.

TABLE1

COLUMNX	COLUMNY
1	A
1	B
2	A
3	B

TABLE2

COLUMNX	COLUMNZ
1	abcd
2	efgh
3	ijk
4	lmno

I'm trying to find a way to join these tables so that the resultant table looks like .

COLUMNX	COLUMNY	COLUMNZ
1	A	abcd
1	B	abcd
2	A	efgh
3	B	ijk

I can't seem to figure out the correct join to get the desired output.
Thanks 

[Dinesh_Suranga]

@Gayatrik 

Hi,

Try following M code

let
Source = Table.NestedJoin(TABLE1, {"COLUMNX"}, TABLE2, {"COLUMNX"}, "TABLE2", JoinKind.LeftOuter),
#"Expanded TABLE2" = Table.ExpandTableColumn(Source, "TABLE2", {"COLUMNZ"}, {"COLUMNZ"})
in
#"Expanded TABLE2"

If this solved your problem, please accept as the solution.

Thank you.

[Anonymous]

Is this M query, a new column, or DAX? You can build them all but they have different verbiage and different uses.

[Gayatrik]

Trying to accomplish this with power query. What approach would you suggest?