Newbie Data Lookup

[Anonymous]

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 is a descriptor and 3rd an addtional descriptor and the rest is not relevent.

I then have a second table(daily) that contains only 2 fields but the 2nd field has a numeric value. And the first field would need to match the 1st field of the master table. This would then lookup the items based on the unique number and show me the 2nd column of the second table(daily). The data result would then Read: 6955266202337, CASTLE SERIES: KNIGHTS SMALL BALLISTA 27 PCS 1006, 122

See below: Any assitance would be appreciated.

Table 1 (Master)
Code	Desc	Man
6955266102323	CASTLE SERIES: KNIGHTS PAY CAR 25 PCS 1005	MIN002
6955266202337	CASTLE SERIES: KNIGHTS SMALL BALLISTA 27 PCS 1006	MIN002
6955266302341	CASTLE SERIES: KNIGHTS TRANSPORT BOATS 30 PCS 1007	MIN002
6955265623744	CASTLE SERIES: KNIGHT OF EAGLE 10 PCS 1010	MIN002

Table2 (Daily)
Code	Qty
6955266202337	122

[MattAllington · Accepted solution]

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

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

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

[MattAllington · Accepted solution]

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.

[Anonymous]

Hi Matt,

Thank you for your response, I double checked that the master table doesnt contain duplicates on the 1st row, but yet I still get the error message about one tables column must contain unique values. I see on the one table the data is blank but is visible why I click the drop down on the filter?

Any ideas?

[MattAllington · Accepted solution]

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

[Anonymous]

Hello Matt,

I double checked that there is no blanks in the first table and yet the relationship cannot be established.

Please help 

[ValentinBIA]

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!