matching two tables with multiple values

[Anonymous]

I have two tables.

TABLE 1

Product ID	ICD Code
1	A
1	B
1	C
2	A
3	B
3	D

My second table looks like this:

TABLE 2

Product ID

1

3

I would like to match table 2 with table 1, so that the result looks like the following:

Expected Result

Product ID	ICD Code
1	A
1	B
1	C
3	B
3	D

Since a simple lookup doesnt work here, does someone have a solution of how I can still match both tables preferably with DAX? Thanks so much as always!

[v-diye-msft]

Hi @Anonymous 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[FrankAT · Accepted solution]

Hi @Anonymous 

you can do it in report view like this:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[amitchandak]

@Anonymous , Power query way

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

DAX way

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[darentengmfs]

Hi @Anonymous 

You can do a merge in Power Query.

Go to Power Query Editor, under Home tab - Combine, select Merge Queries. Choose column Product ID as the keys for both tables and select either Left Outer or Right Outer Join kind, depending on which table comes first. If TABLE 2 comes first, choose Left Outer, and if TABLE 2 is the second table, choose Right Outer. After the join, expand the data.