Copy one column from one table in another table (tables have one column in common)

[Anonymous]

Hi,

I would like to copy one column from one table in another table. Both tables hable one column in common (Chat ID), although there might be some values in the table A that do not appear in the table B and for that I should get as a result "No Chat".

How should I do this?

Thanks,
JFB

[v-yangliu-msft]

Hi  @Anonymous ,

It may be related to the different formats of the columns associated with your two tables. 

This is about the merger related content, hope to help you：

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data

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]

Hi,

I was trying but I actually did not manage, I do not know which is actually the problem.

The idea described simple is as follows:

so table B has some common values with table A via column "chat ID", and I would like to copy in table B one column from Table A (where as I said, they have in comun the Chat ID), although table A has more values than table B, so I should get just the values they actually share via Chat ID.

[v-yangliu-msft]

Hi @Anonymous ,

Use the lookupvalue function to describe your problem_ zit chat_ chat’[chat_ ID] is equal to stag_ zit chat_ daten’[Chat_ ID], "stag"_ zit chat_ chat’[routing_ behavior_ By_ Agent] contains multiple values, resulting in an error. Summarize is recommended.

This is about the summarize function ：https://docs.microsoft.com/en-us/dax/summarize-function-dax

For your second question, you can merge 'stag'_ zit chat_ chat’[chat_ ID] and stag_ zit chat_ daten’[Chat_ Change the format of ID] to consistent

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]

When trying the merge option, so that I can have all (or just one column) from the second table into the first one, it gives me error too.

and i do not know what can it be, because i checked and the column "chat id" is in number format, not in text as highlighted in the error:

what can be happening here?

[Anonymous]

Hii!

Thanks so much for such a detailed answer. I am not able though to do it with any of the options.

With Vlookup, i get the following error:

Routing_behavior_by_agent: is the column I actually want to copy from the 1st table into the 2nd one.

chat_chat is the first table and chat_daten is the second one. Chat ID is the column that both tables have in common, although it does not correspond 100%, that means, they share some values. "chat_id" has more fields than "Chat_ID".

What am I doing wrong with Vlookup here?

[v-yangliu-msft]

Hi  @Anonymous ,

There are three ways you can use it:

1. Using the related function

Related =
RELATED(TableB[city])

2. Using the LOOKUPVALUE function.

LookupValue =
LOOKUPVALUE('TableB'[city],
TableB[ID],'TableA'[ID])

3. Use the merge of power query

Expand the table B and select the desired column

Result.

For related and lookupvalue documents, you can take a look at these:

 https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

https://docs.microsoft.com/en-us/dax/related-function-dax

You can downloaded PBIX file from here.

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.

[mahoneypat]

You should consider merging your tables in the query editor on the ChatID column, to get all your fields into one table.  If not, you can use the RELATED() function to bring values from Table B to Table A (if A is on the 1 side of a 1:1 or 1:Many relationship on the ChatID columns).

Regards,

Pat

[Anonymous]

I am trying with Lookupvalue, but the column which i want to copy "routing behavior" is not a number but a text, so this might be the reason of the error?

Apart from that, the column chat_id from table A  (chat_chat) and the column Chat_ID from table B (chat_daten), share some values, but the column chat_id has more fields than the column Chat_ID (i.e, this last one, has not all the chat ids).

Which is the best way to do this?