How to Lookup Data in Power BI: Four Easy Ways

If you are working on Power BI with different datasets, a common issue is finding and merging the appropriate columns together.

You may have Employee Name and Salary details in one column and their Job Title in another. You would love to have them all together at a source table to analyze further. This method is known as Data Lookup.

Looking up data is an essential skill that analysts do thrive on. You will agree with me on the fact that the data you use in day-to-day life is not as clean as we expect it to be.

You might come up with a situation where you need to lookup values from different tables due to the non-user-friendly data structures. Most of the time, lookup functions such as VLOOKUP do this task for you.

There are different ways of doing this task in Power BI, though. Throughout this article, you will learn three or four different methods that work precisely like VLOOKUP and fetch data from different tables based on a standard column. Hit the button below to download the example files for this article. You are ready to go!

The Dataset

Let’s start with the dataset that you will be working on. There are two csv files, one for Employee Details and other for JobDetails respectively.

The first file has Employee ID, Employee Name, and Salary details as shown below:

Employee ID,Employee Name,Salary
1,Patricia Robinson,20000
2,Tristan West,21000
3,Thomas Garza,12000
4,Eddie Barajas,29000
5,Jessica Washington,14000
6,David Ruiz,5000
7,Veronica Williams,22000
8,Lynn Guzman,18000
9,Sonya Guerrero,11000
10,Jennifer Lee,12000
11,Andrew Martin,27000
12,Mary Howard,24000
13,Bradley Parker,26000
14,Richard Stout,17000
15,Daniel Williams,23000
16,Nancy Larson,14000
17,Cheryl Herrera,18000
18,Kathryn Charles,50000
19,Eric Gutierrez,23000
20,Jacqueline Rosales,16000

Another file contains the Job Details for these employees. As shown below:

Employee Name,Job Name
Patricia Robinson,Analyst
Tristan West,Purchasing Manager
Thomas Garza,Departmental Head
Eddie Barajas,Azure Data Engineer
Jessica Washington,Machine Learning Master
david ruiz,Procurement Analyst
Veronica Williams,Materials Manager
Lynn Guzman,GSC Information Designer
Sonya Guerrero,Systems and Process Lead
Jennifer Lee,Process Analyst
ANDREW MARTIN,Business Data Analyst
Mary Howard,Procurement Manager II
Bradley Parker,Financial Analyst
Richard Stout,Content Delivery Specialist
Daniel Williams,Customer Care Analyst
nancy larson,Cost Accounting Technician
Cheryl Herrera,Accounting Manager
Kathryn Charles,
Eric Gutierrez,System Analyst
JACQUELINE ROSALES,Celonis Specialist

Now, you need to import these two tables into Power BI through the Get Data > Text/CSV section and make sure you promote the first row as a header while importing.

Lookup Without Any Code or DAX

It is the first and by far the cleanest and most straightforward method of looking up data in Power BI without the need for any code or DAX function.

You must be aware of the relationships Power BI establishes between imported tables. The relationships are of type, Many to one, One to one, One to many, and Many to many.

Power BI most of the time, identifies the relationship between tables as soon as you import them. However, if it doesn’t, you can just drag the common column from one table to another, and it will establish a connection between them for you. In this example, Employee Name between two tables is common, and a one-to-one relationship is established based on that column.

Pro Tip: You can double click on the relationship line, and it will open up a window within which you can change the relationship between the two tables. However, I recommend you to go on with the standard relationship that Power BI identifies.

Once the relationships are set up, you can use any column between the two tables to visualize. Establishing a relationship allows Power BI to lookup another column from a table based on a common column (lookup column).

This method allows you to lookup the data without adding a new column into the original table. See the example above where the Job Name from the JobDetails.csv table and Salary from Employee Details.csv table are used to visualize the data.

If you look at this visual carefully, the first bar doesn’t have any Job Name with it. This is because that particular cell is blank for the Employee Name. It rectifies the limitation of using the relationships as a lookup alternative. You usually don’t have control over lookups while using the Power BI relationships. You can’t modify them after you import the tables. The drawback sets up the way for another method that we are about to use.

Note1: The relationship method of looking up the values also works on more than two tables. Whatever number of tables you input in Power BI will automatically detect the relationship between them. If not, you can establish one manually.

Note2: The Power BI relationship allows you to lookup values irrespective of the text case for Employee Names. That is a real cool thing!

Using DAX RELATED Function to Establish Lookup

Another way to establish relationships but with more control is using the DAX RELATED function.

Power BI has three sections – Report, Data, and Model. The first one allows you to create a visual report, the second one allows you to control data tables, and the third one manages the relationships and models between the imported data. These three sections are placed one below another on the extreme left pane of the Power BI desktop screen.

Under the Formula Bar, rename the column as Job Name_1 and use the RELATED function to fetch the Job Name column from the JobDetails table in the Employee Details table. Use the following formula –

Job Name_1 = RELATED(JobDetails[Job Name])

Click on the right-tick to accept this custom formula and create a column based on the same. You can also hit the keyboard Enter button to do so.

What you did just now is similar to establishing a relationship between two tables. However, in this way, you have the added advantage of being able to control the relationships.

Now, the Job Name is still blank for Employee ID 18. Use the following formula that works perfectly as an alternative to lookup values.

Job Name_2 = IF('Employee Details'[Employee Name] = "Kathryn Charles", "Azure Admin", RELATED(JobDetails[Job Name]))

This formula creates a Job Name_2 column with the job title as “Azure Admin” for Employee Name “Kathryn Charles.” The only drawback of this formula defined is that you will have to add multiple conditions if more than one blank Job Name is in your data. It makes the formula look complex and slows the processing down in such cases. But it indeed can be used as an alternative to lookup the values.

Pro Tip: You can right-click on the table in the Fields pane on the right and click on New Column to add a one as well.

Using the DAX LOOKUPVALUE function for Data Lookup

The LOOKUPVALUE function from Power BI works similarly to the VLOOKUP function from Excel. It allows you to fetch data from different tables based on a common column and by far a complete method of looking values up in Power BI.

The syntax for the LOOKUPVALUE function is as shown below –

=LOOKUPVALUE(Result_ColumnName, Search_ColumnName1, Search_Value1, ..., [Alternate_Result])

Where,

Result_ColumnName – column from which you want to fetch/lookup the values into the current table. The column should be an existing one and not an expression.

Search_ColumnName1 – A lookup column based on which we want to fetch the result. It can be present in the same table or a related table.

Search_Value1 – a value to search in Search_ColumnName1.

[Alternate_Result] – an alternate result that appears in the case of advanced filters applied. It is an optional argument and, by default, provides a BLANK value unless specified extensively.

Navigate towards the Data section in the Power BI file and select the Employee Details table. The table will probably appear selected by default as it is the first one in the list. Then, click on the New Column option present under the Calculations section through the Home menu.

Use the following formula to lookup Job Name in the table using the LOOKUPVALUE function.

Job Name_3 = LOOKUPVALUE(JobDetails[Job Name],JobDetails[Employee Name], 'Employee Details'[Employee Name])

Here,

• The column is named as Job Name_3.
• The first argument for the LOOKUPVALUE function is Result_ColumnName. You want the Job Name from JobDetails table to be fetched. Hence it appears as a first argument.
• For the Search_ColumnName1, you need to specify a column based on which you want to lookup the values. In this case the Employee Name column from JobDetails table is the right choice.
• Finally, you need to give a value to Search_Value1 parameter. This ideally represents a column from source table that can be matched with the second argument to lookup the values. In this case, Employee Name column from Employee Details table.

The formula above will lookup all the Job Names from JobDetails tables based on the Employee Name column.

You see that the Job Name is still blank for “Kathlyn Charles,” Employee ID 18. The LOOKUPVALUE function only returns those values for it finds the matches. However, You can club the LOOKUPVALUE function as a second argument to the IF function you used previously to generate the desired output. The formula is as shown below:

Job Name_3 = IF('Employee Details'[Employee Name] = "Kathryn Charles","Azure Admin",LOOKUPVALUE(JobDetails[Job Name],JobDetails[Employee Name], 'Employee Details'[Employee Name]))

This function gives you complete control over the lookups. How? With the LOOKUPVALUE function, you can easily manipulate the data the way it looks up. You can customize it to produce advanced results (remember the blank rows?) this was not possible in the first method, where you were dependent on the standard relationships to lookup values for you.

Using the Merge Queries Option to Lookup Values in Power BI

The last option that we think can be utilized while looking up values from different tables in Power BI is the Merge Queries option. It will allow you to combine two tables together based on a common column and then fetch the details based on lookup values.

• Being in the Data section, click on the Home menu tab.
• Click on the Transform data dropdown under the Queries section.
• Select Transform data option to open up the Power Query Editor.

• Inside the Power Query Editor, click on the Home menu tab to access all the options available under it.
• Under the Combine section, you will see the Merge Queries dropdown.
• Click on the Merge Queries option to merge the current table query with another one. Make sure the Employee Details table is selected while performing the Merge Queries operation.

A new Merge window will appear with Employee Details as the first and default table.

• Choose the JobDetails table from the second dropdown list.
• Select the Employee Name column from both of the tables. It will work as a lookup value column while performing the merge operation.
• The Power BI system by default sets the Join Kind as Left Outer. You can choose the one of your preference, but I will suggest to go with the Power BI intelligence with this one.
• Check the Use fuzzy matching to perform the merge option as it will match everything based on the Employee Name irrespective of different text cases or additional spaces.
• Finally, hit the OK button to merge these two queries together.

You will now see a new column named JobDetails that represents the table in the Employee Details layout.

• There is a two-way arrow on the column heading that specifies the Expand option. Click on that to expand this table.
• Make sure the Expand radio button is selected.
• Select the Job Name column to expand from this table and merge with the Employee Details table based on the Employee Name.
• Tick the Use original column name as prefix box. This will add a original column name as a prefix to the column when merged.
• Click on the OK button and that’s it! You have successfully looked up the Job Name column into the table.

Note: You will still see the blank Job Name for “Kathryn Charles”, because there is not value for that employee in Job Name field. Besides, I already covered the ways of adding the Job Name for such cases.

Conclusion

Throughout this article, you learned four different ways of looking up the data in Power BI. The basic way is by using the standard Power BI relationships. Though they don’t provide a much of control to the user, they are best when you have everything to lookup in another column and need nothing out of the box to fetch the results. Basic yet powerful method I will say.

The Power BI Merge Queries option also allows you to lookup data in it’s own ways. It merges columns from different tables with a common lookup value into the source table. It also doesn’t provide a great control when it comes to deal with the blank values.

Then there are DAX function which are advanced ways of looking up the data with more control than the relationships and merged queries together. There are countless ways of using DAX functions to lookup the values in Power BI. However, the combination of RELATED, IF, and LOOKUPVALUE can give you a best possible lookup alternative if used properly.

Will end this article here. Hope it adds some value to your prior knowledge of Power BI. For more awesome Power BI tips and tricks, make sure to visit my blog often. Until the next time, Ciao!😁