Creating a unique table with 2 columns which is pulled from multiple tables in the report

[rahulmenon]

Hi Community,

I was trying to create a unique table by combining 2 columns from all the tables available in my report.

There are 4 FACT tables in my report. Each table have multiple columns in it. But each tables have Client ID and Client Name column, but not necessarily having same details. I want to create a new table named 'Client List', where in I need Client ID and Client Name which is pulled from all other 4 reports and then I have to remove duplicates. So the Client ID and Client Name columns from the Client List table should have only unique values. 

Hence, i can use the Client Name column from Client List table to keep in Slicers to filter informations.

An example is given below:-

Expected result

[HotChilli · Accepted solution]

You can duplicate each of the fact tables and then remove all columns apart from ID and Name.

Append the tables together.

Then select ID and Name in the Appended table result, and Remove Duplicates

[NishPatel · Accepted solution]

First of all make a copy of all the tabels in power query using duplicate.  From each table select Client ID and Client Name column and remove other columns. And then use append query to merge all into one table and name this table as Client List. Close and Load power query.

[rahulmenon]

Thanks NishPate for your response. After duplicating and appending, can i remove the duplicated tables? will it impact the appended table?

[NishPatel]

Hi, You cannot delete them as if there will be a new data than the newly created table will fail. Instead you can uncheck the "Enable Load" option in power query by right clicking on those tables and those tables will not show up in your report pane.

[HotChilli · Accepted solution]

You can duplicate each of the fact tables and then remove all columns apart from ID and Name.

Append the tables together.

Then select ID and Name in the Appended table result, and Remove Duplicates