VLOOKUP in Power BI

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

The use of LOOKUP functions is common in data representation. Like Excel, one of the extensively used LOOKUP functions is the VLOOKUP function in Power BI, but it is not built-in in Power BI, so we need to replicate the LOOKUP function using DAX for the VLOOKUP function in Power BI.

Power BI Vlookup

Not even a single Excel user says they are unaware of the function. That is the popularity of VLOOKUP in ExcelVLOOKUP In ExcelThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more. So, everybody doubts how to replicate the VLOOKUP function in Power BI. This article will detail how to replicate VLOOKUP in Power BI.

VLOOKUP-in-Power-BI-1.png

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VLOOKUP in Power BI (wallstreetmojo.com)

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

How to Replicate VLOOKUP in Power BI?

For example, assume you have three tables: “Sales,” “City,” and “Manager.”

Power BI Vlookup (Data)

You can copy the data to an Excel file and then import it to Power BI as an Excel file reference. You can also download the Excel workbook from the link below, which we have used for this example.

You can download this VLOOKUP in Power BI Excel Template here – VLOOKUP in Power BI Excel Template

Upload these tables to Power BI.

In the “Sales_Table,” we do not have “Region Names” and “Manager” names, but to fetch the data from the other two tables, we have “City” as the common column or value among these tables.

Power BI Vlookup (Tables)

Using the LOOKUPVALUE DAX function, we can fetch the data from other tables to the “Sales Table.” Below is the syntax of the LOOKUPVALUE DAX function.

LOOKUPVALUE Formula
  • Result_ColumnName: In this argument, we need to specify from which column we need the result? For example, if we are fetching the “Region” name from “City Table,” then the result column will be “Region Names” from “City Table.”
  • Search_ColumnName: Based on which column we are searching the Result Column in the other table, i.e., in “City Table,” “City” is the base column.
  • Search_Value: In the result required table (Sales_Table), based on which column we are searching for the result. In “Sales_Table,” “City” is the search base value.

Hint: Search_Column Name and Search Value should be the same in both tables.

For example, take the above tables.

  • Go to the “Data” tab and choose “Sales _Table.”
Power BI Vlookup (Sales Table)
  • Right-click on the “Sales_Table” and choose “New column.”
Power BI Vlookup (New column)
  • It will ask you first to name the column, so give it a name as “Regions.”
Power BI Vlookup (Regions)
  • Now, open the LOOKUPVALUE function.
Power BI Vlookup (Lookupvalue Syntax)
  • The first argument of this DAX function is “Result_Column Name.” So, from “CityTable,” choose the “Region Names” column.
Power BI Vlookup (Region names)
  • The next argument is “Search Column Name,” i.e., from “City Table” based on “City Names,” we are fetching the data, so choose the “City Names” column from “City Table.”
Power BI Vlookup (city names)
  • The next argument is Search Value1, i.e., from the current table The “Sales_Table” base value is the “City Names” column, so choose the column.
Power BI Vlookup (sales-table-city)

Close the bracket and press the ‘”Enter” key. We will get a new “Sales Table” column as “Regions.”

Power BI Vlookup (Regions Column)
  • Similarly, we must fetch the “Manager Names” from the “Manager Table.” Again right-click on the “Sales_Table” and choose “New Column.” It will ask you to name the column, so give it a name as “Manager.”
Power BI Vlookup (Manager)
  • Open the LOOKUPVALUE function once again.
Power BI Vlookup (Manager lookupvalue)
  • This time we need the result from “Manager_Table,” so the Result_Column Name will be “Manager” from “Manager_Table.”
Power BI Vlookup (Manager table)
  • Next, we need to select the Search_Column Name, i.e., from “Manager_Table” based on “City” we are fetching the data, so choose the “City” column from “Manager_Table.”
Power BI Vlookup (Manager table - City Names)
  • Search Value will also be “City” name but from “Sales_Table.”
Power BI Vlookup (Manager-city)

Close the bracket and press the “Enter” key to get the “Manager” names as the new column.

Power BI Vlookup (Manager table value)

So, like this, using the LOOKUPVALUE DAX function in Power BIDAX Function In Power BIIn Power Bi, DAX stands for Data Analysis and Expression and is a functional language that represents all of the functions in Excel and Power BI. Formulas in Power BI can be created using the DAX language.read more to replicate VLOOKUP in Power BI as well.

Alternative Way of Fetching the Data in Power BI

Using “Power Query,” we can merge or fetch the data from other tables.

  • From the Power BI file under the “Home” tab, click on “Edit Queries.”
Power BI Edit Queries
  • It will open up the “Power Query” editor window. From this new window, under the “Home” tab, click on “Merge Queries.” Note: Select “Sales Table” and do this.
POWER BI Merge Queries
  • It opens the “Merge” window.
Power BI Vlookup (Merger Window)
  • As of now, we can see “Sales_Table” is already selected. So, from the second dropdown list, choose “City Table.”
Power BI Vlookup (CityTable)
  • We need to select the common columns from these two tables, so the common column between these two tables is “City” names, so select the same columns in both tables.
Power BI Vlookup (Select Tables)
  • Click “OK” to return to the “Query Editor” window.
Column Added
  • As you can see, it has created a new column, so click on the double-side arrow to see further options.
Click on Double Arrow
  • It will show the below options.
Filter options
  • From this, select only “Regions” because in our “Sales_Table,” “City Names” column already exists, so unselect that.
Regions Names

Click on “OK” to get “Region” names.

Region Column Added
  • Repeat the same process to merge “Manager” names.
Merged Manager_Table
  • After merging columns, click on “Close & Apply.”
Close and Apply

Now, it will return to the Power BI file. Go to the “Data” tab to see new merged columns.

Power BI Vlookup (Final Table)

Note: You can download the Power BI VLOOKUP file from the below link and apply each formatting technique as applied.

You can download this Power BI VLOOKUP Template here – Power BI VLOOKUP Template

Things to Remember Here

This article has been a guide to VLOOKUP in Power BI. Here, we discuss how to replicate VLOOKUP using the LOOKUPVALUE DAX function in Power BI with an example. You may learn more about Power BI from the following articles: –