How to distinctly count the total Members from 2 different datasets

[Mnisar17]

Hello Everyone,

I'm working on 2 different datasets or tables and have uploaded the data tables on the power BI for creating the relation among the tables which have some similar columns and the data is partly connnected to each other. Though the relationship is created as Many to Many because the data is not completely similar and both tables has some of their own unique values too.
Example

Table 1

Table 2

Here are 2 different tables and the columns which I want to append into a new or merging the columns or the tables from a lot more different column names ( any walkaround would work). So here we can see that the member IDs are somewhat similar but some are unique on its own. I want to append or merge both the columns and create the relation among the tables in order to create the dashboard. Also, additionaly, I want to get a distinct count of the member IDs in order to get the total members from both the tables. My main goal is to create a relation between the 2 tables in order to create the dashboard. Any help is appreciated. Thank you in advance.

[Anonymous · Accepted solution]

Hi @Mnisar17 ,

Try appending a query to both tables in PowerQuery.

Then distinct the table:

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group