Forum Discussion
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 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 | |
| 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) | ||
| Qty | |
| 6955266202337 | 122 |
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.
You are not allowed blanks in the table on the 1 side of the relationship. Remove the blanks with Power Query first.
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
6 Replies
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.
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?
You are not allowed blanks in the table on the 1 side of the relationship. Remove the blanks with Power Query first.
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!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
