how to get columns from 4 different tables into single table without joins and merge

[Anonymous]

Hello Team,

I am trying to acheive the below requirement but no luck (all the tables have more than 40k records) .Below is the senario Please any idea , it would be a great help

i am having 4 different tables Table -a(devicename,Zone)

                                                Table -b(devicename,Zone)
                                                Table -c(devicename,Zone)
                                                Table -d(devicename,Zone)

now i need to show case the devices which are common in Table A & Table B , Table B & Table C, Table C & Table A ,

Table B & Table D, Table A & Table D , Table C & Table D  into a new table 

So that i want show     :     column                                   devices in common

                                          Table A & Table B                         device names

                                          Table B & Table C                         device names

                                          Table C & Table A                         device names

                                          Table B & Table D                         device names

                                          Table A & Table D                         device names

                                          Table C & Table D                         device names

Thanks In Advance

[Anonymous]

Hi  @Anonymous ,

According to your description, I created the data:

TableA:

TableB:

Here are the steps you can follow：

1. Create calculated table.

TableA & TableB = INTERSECT(DISTINCT(TableA[devicename]),DISTINCT(TableB[devicename]))

2. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi  @Anonymous  ,

According to your description, I created the data:

TableA:

TableB：

Here are the steps you can follow：

1. Open transform data and enter power query

2. Select home – merge queries -- merge queries as new

3. Select the two tables and columns to be merged, and select the inner merge method

4. Select remove columns to delete the extra columns

5. Result

You can downloaded PBIX file from here.

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

thnkayou , but i dont want to merge or append because each table contains more than 60k records

[amitchandak]

@Anonymous , Not very clear, what you want to achieve. But if you do not want to merge or append then you can create common dimesion of device and zone and analyze data together : https://www.youtube.com/watch?v=Bkf35Roman8

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/