How do I show both matching and non-matching rows in a matrix from two different tables?

[Anonymous]

I have two tables. Table A has three columns. Table B has three columns.

Table A

RefIDA------OpIDA------ValueA

123---------ABC---------AAAA

456---------DEF---------BBBB

<blank>---GHI---------CCCC

101-------<blank>-----DDDD

Table B

RefIDB------OpIDB------ValueB

123---------ABC---------aaaa

456---------DEF---------bbbb

789--------<blank>-----cccc

<blank>---JKL----------dddd

The tables are linked using the columns RefIDA and RefIDB (the relationship is a 1:*).  When I create a visual table with the following columns I don't get the complete results. I only get the rows where there are matching values in RefIDA and RefIDB. I need to also get the rows that have a blank value for the RefIDA and RefIDB. In other words, I need all the rows returned but the rows whaere there are matching values in RefIDA and RefIDB should be combined into a single row.

Result Table that I currently get:

RefIDA------RefIDB------OpIDA------OpIDA------ValueA------ValueB

123---------123----------ABC---------ABC---------aaaa--------bbbb

456---------456----------DEF---------DEF----------aaaa--------bbbb

Result Table that I Need:

RefIDA------RefIDB------OpIDA------OpIDA------ValueA------ValueB

123----------123---------ABC---------ABC---------aaaa--------bbbb

456---------456----------DEF---------DEF----------aaaa--------bbbb

<blank>---<blank>-----GHI--------<blank>------cccc-------<blank>

101--------<blank>----<blank>----<blank>------DDDD-----<blank>

<blank>----789--------<blank>----<blank>-----<blank>----ccccc

<blank>---<blank>----<blank>----<JKL>-------<blank>-----dddd

To summarize, I need the final results to return all the rows that match (where the matching rows are combined into a single row) and all the rows that don’t match.

[V-lianl-msft · Accepted solution]

In your model, the null field is also a one-to-one relationship in the two tables. You need to replace the null field in the two tables with different characters, then use PQ or DAX as mentioned in the previous reply.

[amitchandak]

@Anonymous , full outer join in power query Merge - https://radacad.com/append-vs-merge-in-power-bi-and-power-query

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