Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Newbie Data Lookup

Good Day,   I was wondering if anyone could assist me with a data structure. I have a master reference table that contains multiple columns. The first column is a unique number/text and the second ...
Need Help
  • MattAllington's avatar
    MattAllington
    8 years ago

    This is the foundation of Power BI. You need to join the 2 tables with a relationship in the relationship view. The rest will just work, as long as that column in the first table is unique (as you suggest). 

     

    You may like to read my article about Relationships In Power BI.

  • MattAllington's avatar
    MattAllington
    8 years ago

    You are not allowed blanks in the table on the 1 side of the relationship.  Remove the blanks with Power Query first.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    In Query Editor mode, select the [Code] field in Table1, then, remove empty and remove duplicates.

         

     

    Then, in data view mode, add a calculated column in Table2 with LOOKUPVALUE function.

    New Column =
LOOKUPVALUE ( Table1[Desc], Table1[Code], Table2[Code] )

     

    Best regards,

    Yuliana Gu

ValentinBIA's avatar
ValentinBIA
Resolver I
8 years ago

Hi Anonymous,

 

In Power BI you have something much more powerfull and easy to use the basic lookup.

 

To do what you are looking to, you should:

- Charge your 2 tables in 2 different queries

- Make sure the Type of your Code column is the same in both queries (text or number, doesn't matter)

- Go to the main query

- In the home upper ribbon, click merge querries (top right)

- Select your second query, and for both, select the code column.

- Press OK

- Click the small expand icon on the new column

- Press OK

 

There you go, you can now rename your columns and delete the ones that are not useful

 

Hope that helps

Best,

Valentin

Did I answer your question? Mark my post as a solution!