Moving column from one table to another table.

[rschaudhr]

I have two tables. One table (lets say table1) in direct query from dataflow. The other table (lets say table 2) is an import query from sql server. I want to be able to add column in table1 columns from table2. The is a join between two tables called "key". I have tried related and lookupvalue and it does not seems to work. Is this possible at all?

Thank you,

R 

[v-zhouwen-msft · Accepted solution]

Hi all,thanks for the quick reply, I'll add more.

Hi @rschaudhr ,

Regarding your question, you are using a composite model and the “Relate” function is not supported.

The “Relate” function is only supported in the following cases:

1.Relationships between Import Mode tables
2.Relationship between Direct Query Mode tables from the same SQL database

1.You can create form table visual with similar fields to achieve a similar effect

2.You can switch Table 1 to import storage mode,then you can use the “Relate” function.Or you can use merge operations with Power Query

Best Regards

[vanessafvg]

what type of relationship do you have set up between the tables?

[rschaudhr]

It is one to many relationship. The table2 has unique values whereas table1 has many values. It is joined on one field called key. I hope I have answered your question. Thanks!

[Irwan]

hello @rschaudhr 

i am not sure if you can add physical calculated column in DQ table since you can not see the content of DQ tables.

however, i am pretty sure you can do with measure to get a value from table 2 to table 1 (you need table visual to see the value).

below 'Day' column is come from DQ table and 'Column1' is imported table.

Hope this will help.

Thank you.