How to merge or combine rows with unique value from different tables?

[CG_pbi]

Hello all,

I have read multiple thread for my issue, but i did not find a way to solve it. I'm sure this is pretty simple, but i can't achieve... 

I have 6 different tables (from excel files) with many columns in each (total is 92 for all 6 files). Each table has a column with a device ID, and device ID is unique in table. This device ID can exist in other tables, or not. My need is to create a new table with a column that will retrieve all distinct Device ID from different tables, and to populate each columns (the 92) with values from each files. 

Basically i have something like this: 

TABLE 1

Table 2

Table 3

And i need to have something:

I have tried merging as new query, append as new, unpivot column, etc.. nothing work until the end. I always end up with an error about dateformat that cant be parsed... even if i remove all columns with date (and after checking the format of each column and replace any blank date with a value).

I have been able to create a new table with all distinct Device ID, but only in the reporting part, not in power query editor (via Transform data). Should i continue this way, and create my 92 columns using some sort of "vlookup" formula to populate the values? I thought there might be a way to do it quickly in power query...

Thank you for any advice, help on the good way to do it.. I am kind of desperate ! 

[BA_Pete · Accepted solution]

@CG_pbi ,

OK. First things first: why do you want all this data in one table when you have a common field ([Device ID]) between them?

The 'correct' way to handle this would be to have a Device dimension table (all possible unique device ID's, then some columns telling us about each device), and relate this in the data model to each of your smaller tables on dimDevice[Device ID] ONE : MANY subTable1[Device ID], subTable2[Device ID], subTable3[Device ID]... etc.

You then use dimDevice[Device ID] (or any other dimension from the dimension table) in visuals and Power BI automatically filters all the sub tables to the relevant rows.

Pete

[CG_pbi]

Thank you so much for your help! It is working fine now with the new table. 
I never noticed this command before to show empty data.. and obviously it resolves everything! 😉

[CG_pbi]

Hello @BA_Pete 

Thank you so much for your quick reply! 

I have managed to create a single table with distinct Device ID, and to create relationship with other tables. 

Then when i use visuals, let's say with a simple table visual, i have all my values. When i start adding other values, it remove rows for which Device ID do not have the information. 

For example: 

Device 1 has value A and B from table 1

Device 2 has value C and D from table 2 (it doesnt exist in table 1)

When i select Device ID from the newly created table, and Value A, i have only Device 1 showing up.

I guess this is normal since there's no value available for Device 2, but then if i add a new one, let's say D, i will not have anything anymore showing up. I would expect to have blank value when it's not existing... That's why i was trying to recreate a table with all rows merged into one for each Device ID. 

[BA_Pete]

Hi @CG_pbi ,

If I understand you correctly, and assuming that you have set up your model as I suggested, you should just be able to right-click dimDevice[Device ID] from the Values list when it's in a visual and select 'Show items with no data'.

Pte