VLOOKUP in Power BI

Lookup functions are very commonly used in the representation of data, and similar to excel one of the extensively used lookup functions is Vlookup function in power bi but is not inbuilt in power bi so we need to replicate the lookup function using DAX to use Vlookup in power bi.

Power BI Vlookup

Probably not even a single excel says they are not aware of the function. That is the popularity of VLOOKUP in Excel. So, everybody has a doubt on how to replicate the VLOOKUP function in Power BI. In this article, we will show you how to replicate VLOOKUP in Power BI in detail.

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)

How to Replicate VLOOKUP in Power BI?

For example, assume you have three tables named “Sale Table, City Table, and Manager Table.”

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 below link, which is 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 don’t 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)

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

LOOKUPVALUE Formula
  • Result Column Name: In this argument, we need to specify from which and from which column we need the result from??? For example, if we are fetching the Region name from “City Table,” then the result column will “Region Names” from “City Table.”
  • Search Column Name: 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. i.e., In “Sales Table,” “City” is the Search base value.
Hint: In both the table, Search Column Name & Search Value should be the same.

Take the above tables only for an example,

  • 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)
  • This will ask you to first name the column, so give 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 “City Table,” choose “Regions 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, i.e., “Sales Table” base value is the “City Names” column, so choose the column.
Power BI Vlookup (sales-table-city)

Close the bracket and hit the enter key. We will get a new column in “Sales Table” as “Regions.”

Power BI Vlookup (Regions Column)
  • Similarly, we need to fetch the “Manager Names” from “Manager Table.” Again right-click on the “Sales Table” and choose “New Column” this will ask you to name the column, so give 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 is also will be “City” name but from “Sales Table.”
Power BI Vlookup (Manager-city)

Close the bracket and hit the enter key to get the “Manager” names as the new column.

Power BI Vlookup (Manager table value)

So, like this, by using the “LOOKUPVALUE” DAX function in Power BI to replicate VLOOKUP in Power BI as well.

Alternative Way of Fetching the Data in Power BI

By 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
  • This 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
  • This opens up the “Merge” window.
Power BI Vlookup (Merger Window)
  • As of now, we can see “Sales Table” is already selected. So from the second drop-down list, choose “City Table.”
Power BI Vlookup (CityTable)
  • From these two tables, we need to select the common columns, so common columns between these two tables is “City Names” so select the same columns in both the tables.
Power BI Vlookup (Select Tables)
  • Now click on “Ok” to come back to the “Query Editor” window.
Column Added
  • As you can see, a new column has been created, so click on the double side arrow to see further options.
Click on Double Arrow
  • This will show the below options.
Filter options
  • From this, select only “Regions” because in our “Sales Table” city name 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 and Apply.”
Close and Apply

Now it will come back 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 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>