create relationship

[Framboosje1]

Hi,

See picture below. what i want to create is a relationship between the merged table and table 1 so is can report on every column of table 1. 

[mwegener]

Hi @Framboosje1 ,

you have to create a column StateCity

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Framboosje1]

your solution looks good but can you share with me the pbix file.

[mwegener]

Hi @Framboosje1 ,

here my quick & dirty PBIX File.

You should generate table 1 in Power Query. (with append queries)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Framboosje1]

thanks for the pbix but can you explain to me how to create in PowerQuery. 

thanks in advance

[mwegener · Accepted solution]

Hi  @Framboosje1 ,

start with a Base Table, disable the load and create two references (State and City)

Disable the load for the references and dublicate the State/City Column.

Rename this Column as StateCity

Than Append this two tables as new query

I updated the PBIX File

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Framboosje1]

Last question 🙂

what should i do if i values in my merged table which are not unique to the Base Table. What should i do so i can create a many to one relationship.

example: instead of Ile de Paris - Paris i have Beieren - Munchen. what should i do(remove the not unique values or filter out something like that)

[mwegener]

Can you explain it with more sample data?

[Framboosje1]

https://drive.google.com/file/d/1UAcpAdtyYbZTFpLcgYqehbF3ReLtBK15/view?usp=sharing 

look to Osaka

[mwegener]

Sorry, but i don't understand the problem.

Why is Osaka not in the StateCity column of the merged table?

You know that you can edit the relationship?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.