What is alternative solution of merging queries

[Anonymous]

Hello,

I have a table loaded into power bi using ODBC with 7 columns contains a country code, end-user wishes to see the country name instead of country code, I've another table loaded also to power bi with (country name and country codes), I've now a solution to merge both tables 7 times for each column from power bi query editor, my question is what is the most effective way to achieve to  above instead of merging quires 7 times

[v-janeyg-msft]

Hi, @Anonymous 

According to your description,I think you don;t need to merge queries, you can easliy load two tables and create a relationship with them(country codes>country codes),then you can directly use country name and columns in fact table at the same visual.

If it doesn’t solve your problem, please feel free to ask me.

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 · Accepted solution]

I've 7 columns with different country codes in the main table, I can't create 7 relationships to the same destination (country, country code) table, I've referenced the country table 7 times with different names and created those relationships 

[v-janeyg-msft]

Hi, @Anonymous 

Has your problem been solved? If it is solved, you can mark yourself or other reply that is helpful to you. If not, Can you share the sample data ?I will see if there is a good way to solve.

Best Regards

Janey Guo

[mahoneypat]

Depending on your planned analysis/visualizations, you may want to consider unpivoting the 7 country columns anyway.  If so, it would be a single merge at that point.

You could also doing a bulk replace values by turning your code/country table into a list of lists.

Pat

[amitchandak]

@Anonymous

You have Table.Combine, we if that can be used

https://docs.microsoft.com/en-us/powerquery-m/table-combine

Union in DAX in another option

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