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 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.
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 excel file and then import it to Power BI as Excel file reference. You can also download the excel workbook from the below link which is used for this example.
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 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.
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 “City Names” column, so choose the column.
Close the bracket and hit 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 LOOKUPVALUE function once again.
- This time we need the result from “Manager Table” so 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”.
- Search Value is also will be “City” name but from “Sales Table”.
Close the bracket and hit enter key to get the “Manager” names as the new column.
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”.
- 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.
- Now click on “Ok” to come back to the “Query Editor” window.
- As you can see new column has been created, so click on the double side arrow to see further options.
- This will show the below options.
- From this select only “Regions” because in our “Sales Table” city name column already exists, so unselect that.
Click on “Ok” to get “Region” names.
- Repeat the same process to merge “Manager” Names.
- After merging columns click on “Close and Apply”.
Now it will come back to Power BI file, go to the “Data” tab to see new merged columns.
Note: You can download the Power BI VLOOKUP file from the below link and apply each formatting technique as applied.
Things to Remember Here
- LOOKUPVALUE is a VLOOKUP kind of function to fetch the data from other tables in Power BI.
- Power Query merge option is the alternative way of fetching data from different tables.
- LOOKPVALUE is a DAX function and you need to understand all the parameters of the DAX function in power bi.
Recommended Articles
This has been a guide to VLOOKUP in Power BI. Here we discuss how to replicate VLOOKUP using LOOKUPVALUE Dax function in Power BI with an example. You may learn more about Power BI from the following articles –
