how to concatenate two columns from different tables many to many

[userpien]

Hi, I need to rewrite this query that comes from sql server in power bi. I think I have to use concatenatex but this function doesn't allow me to select the column of another 'table. How can I rewrite in power bi this query?

The two tables Aggregato and DER have a many to many relationship.

Query in SQL Server
Aggregato.Cod + ' ' + convert(char(10), CAST(DER.DTINO AS datetime), 103 ) + '-' + convert(char(10), CAST( DER.DTFIO AS datetime), 103 )

Thank you!

[parry2k · Accepted solution]

@userpien Either there is a blank value in the one side of the table or there is a duplicate row, you need to check that. The issue is that it needs to be one to many relationship as it is in SQL.

[parry2k]

@userpien I see the issue there. What is your join condition in SQL? If it is many to many in SQL, it must be returning more rows than expected, no?

[userpien]

on sql server the relation is many to one while on power bi it doesn't allow me to put this relation, but only many to many, and I don't know why ( it tells me that the many to one relation is not valid for this relation). How to correct this relation?

[parry2k]

@userpien it depends on the relationship between these two tables. could you provide the relationship details?

[userpien]

Hi, thanks for your help. The two tables Aggregato and DER have a many to many relationship with columns DTINO and DTFIO. All two set on "Both" for direction of cross filter.