LOOKUPVALUE in Power BI
Having said about the importance of the VLOOKUP functionVLOOKUP FunctionThe 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 are unique identifiers., we can use the same function in Power BI is the common question from all the Power BI beginners, but unfortunately, we don’t have VLOOKUP Power BI; instead, we have a similar kind of function, i.e., LOOKUPVALUE function in Power BI. In this article, we will guide you through this function. If I have to tell you the importance of VLOOKUP in excel for all the excel users in simple words, “it is just an integral part.” Yes, VLOOKUP is an integral part of all the excel users and household functions in the Excel world.
What Does LOOKUPVALUE Function Do in Power BI?
LOOKUPVALUE function is similar to the VLOOKUP function in MS Excel, which looks for the required column from one table to the other based on the search value. Since we already know enough about VLOOKUP, we will not go deep into this function theoretically, so let’s look at the scenario now.
I have three tables with me. Below are the screenshots of the same.
We have three tables named “Product_Table, Tax-Table, and Discount_Table,” respectively.
In Product_Table, we don’t have “Tax %” and “Discount %” information, which is there in the other two tables. So in all the three tables’ common column is “Product” so using this, we need to fetch the data to “Product_Table.”
Before we apply the LOOKUPVALUE function, let’s look at the syntax of this function.
Result Column Name: This is nothing but from then other tables from which column we need the result. So for an example, from “Tax_Table,” we need results from the “Tax %” column, and from “Discount_Table,” we need results from the “Discount %” column.
Search Column Name: This is nothing but in the targeted table (Tax_Table or Discount_Table) based on which column we are looking for the Result Column. So our Search Value: This is the column name in the present table (Product_Table), which is the same as the column in the Search Colum Name of other tables.
So, eventually, Search Column Name and Search Value both columns should be the same. Search Column Name is in from the result column table, and the search value column will be from the present table where we are applying the LOOKUPVALUE function.
Example of LOOKUPVALUE DAX Function in Power BI
Above is the data we are using to apply the LOOKUPVALUE Dax Function in Power BI. You can download the workbook from the below link and can use it to practice with us.
- Upload all the three tables to the Power BI file to start the demonstration.
- For “Product_Table” we need to fetch the values from the other two tables, so first, we will fetch “Discount %” from “Discount_Table.” Right-click on the “Product_Table” and choose “New Column.”
- Give the name for the “New Column” as “Discount %.”
- Open the LOOKUPVALUE function now.
- The first argument is the Result Column Name since we are looking for the discount percentage from “Discount_Table” choose the “Discount %” column name from “Discount_Table.”
- The next argument is Search Column Name 1, so this will be the “Product” column name from “Discount_Table.”
- The next argument is Search Value, so this will a “Product” column name from “Product_Table.”
- Ok, we are done. Close the bracket and hit the enter key to get the result.
There you go, we have got the result from “Discount %” from “Discount_Table.” But when we look at the result column, it is not in percentage format, so we need to change the number format to percentage format.
- Go to the “Modelling” tab, choose the “Format” as “Percentage,” and keep the decimal place as 2.
- This will apply the format to the selected column as below.
- Similarly, now we need to insert one more column to fetch “Tax %” from “Tax_Table,” as usual, right-click and choose “New Column” give the name to the new column as “Tax %” and open the LOOKUPVALUE function again.
- This time Result Column Name will be from “Tax_Table,” i.e., “Tax %”.
- Search Column Name will be the “Product” column name from “Tax_Table.”
- The next argument is Search Value, so this will the “Product” column name from “Product_Table.”
- Close the bracket and hit enter to get the “Tax %” values.
Like this using the Power BI LOOKUPVALUE function, we can fetch data from one table to another.
Note: Power BI LOOKUPVALUE file can also be downloaded from the link below, and the final output can be viewed.
Things to Remember
- LOOKUPVALUE is incorporated into Power BI as a lookup value function.
- If the lookup value is not found, then it will return blank as a result.
- Result Column & Search Value columns are the same in both the tables.
- Unlike VLOOKUP, we need to give any column name and range lookup parameters.
This has been a guide to Power BI LOOKUPVALUE. Here we discuss the examples of LOOKUPVALUE Dax Function in Power B to fetch data from one table to another. You may learn more about Power BI from the following articles –