Column created from combined columns in different tables and filter

[v4391]

Hi,

I´m working with 3 tables.

- 1st table (main table) contains 4 column:

Num, ID Client, ID possible_Client, ID_Client + possibleClient

- 2nd table with client information (id, name_company)

ID, Name

- 3rd table with possible_client information (id, name_company)

Id, Name

These tables contain:

Now I need to create a new column in the first table which should contain the name of the clients and the name of the possible_Clients (see image)

Thanks for the help!!!

[v-stephen-msft · Accepted solution]

Hi @v4391 ,

You don't have to use the ID_Client+possibleClient column.

You could create relationships like

Then create a calculated column as follows.

Name = IF(ISBLANK(RELATED('Table_2'[Name])),RELATED(Table_3[Name]),RELATED(Table_2[Name]))

Best Regards,

Stephen Tao

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

[Anonymous]

Hi,

I would append Table_2 and Table _3, then merge tables by doing a left outer join to Table_1 using the [ID_Client+possibleClient] as the left table

[v4391]

It´s a good idea, but ID(client) and ID (possible_Client) can be the same number so I have created 2 columns in the first table:

- COLUMN_Client_Names: with names of Clients  (and 0 if it is a possible_Client) and

- COLUMN_possibleClient_Names: with names of Possible Clients (and 0 if it is a Client).

I need to read the column COLUMN_Client_Names to get names of Clients and if the cell has 0, I´ll get the name from the table called COLUMN_possibleClient_Names.

How can I read names from first column and if 0, get the name from the other column???

Thank you for your help!

[Anonymous]

Ah right!

In the case that you're describing, I would still merge table 1 and table 2 on a left outer join using [ID_Client+possibleClient] as the key, expand the name and then do another left outer join onto table 3 and also expand the name.

Afterwards, i would add a custom column:

if [ID Client] <> 0 then [Client] else [Possible Client]

Then you can just remove any extra columns after.

[v4391]

When trying to write the name of clients or possible_clients, I am not allowed because names are coming from table 2 and table 3 (different tables).

IF( Tabla1(Client_Name)=0,

     Tabla2(possible_Client_Name),

      Tabla1(possible_Client)

)

[Anonymous]

The tables need to be joined to each other first via Power Query, using the merge I described above