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 ExcelVLOOKUP In Excel The 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 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.

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.”

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.

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.

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.”

Right-click on the “Sales Table” and choose “New Column.”

This will ask you to first name the column, so give a name as “Regions.”

Now open the LOOKUPVALUE function.

The first argument of this DAX function is “Result Column Name,” so from “City Table,” choose “Regions Names” Column.

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.”

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.

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

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.”

Open the LOOKUPVALUE function once again.

This time we need the result from “Manager Table,” so the Result Column Name will be “Manager” from “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.”

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

So, like this, by using the “LOOKUPVALUE” DAX function in Power BIDAX Function In Power BI In 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

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.”

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.

This opens up the “Merge” window.

As of now, we can see “Sales Table” is already selected. So from the second drop-down list, choose “City Table.”

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.