Forum Discussion

Framboosje1's avatar
Framboosje1
Frequent Visitor
6 years ago
Solved

create relationship

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. 
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    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's avatar
Framboosje1
Frequent Visitor
6 years ago

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

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

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's avatar
    Framboosje1
    Frequent Visitor
    6 years ago

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

    thanks in advance

    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago

      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's avatar
        Framboosje1
        Frequent Visitor
        6 years ago

        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)