COALESCE WITH MORE TABLE

[Anonymous]

Hello everyone,
I need your help.
I have 22 tables and each table has the 'Year' field. I should create a column in which I have all the years of all the tables with no dublicates.
I tried to use COALESCE or RELOTED but, COALESCE doesn't work because they are different tables and RELOTED doesn't work because, even if I have active relationships these relationships are many to many.
Anyone have a solution to apply a COALESCE to all tables?
Thanks for your help

[parry2k]

@Anonymous you should append all the tables together in Power QUery and then get the distinct value. Although it is not clear what you are trying to achieve.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I'm sorry, but I forgot that I use direct query and I can't append the tables

[v-janeyg-msft]

Hi, @Anonymous 

Is there no date in your tables but only year?  You said ' I should create a column in which I have all the years of all the tables with no dublicates.', What's your purpose? Can you share some sample data and your desired result? So we can see if there is any other workaround?

Best Regards

Janey Guo

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

[Anonymous]

Hi @v-janeyg-msft ,

thank you for your answere.

I have this situation, for example:

I want put in relationiship this 3 tables. I would the "Table output" with all years.

I use direct query and I can't merge 22 tables with power query so I would connect the table with dax and create other table or column in the primary table.

I hope I was clear

Thank you so much

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

You can use 'union' function to achieve your desult result.

Like this:

Best Regards

Janey Guo

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