WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » VLOOKUP in Power BI

VLOOKUP in Power BI

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Jeevan A Y

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

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

  • LOOKUPVALUE is a VLOOKUP kind of function to fetch the data from other tables in Power BI.
  • The 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 the LOOKUPVALUE Dax function in Power BI with an example. You may learn more about Power BI from the following articles –

  • Average Power BI Measure
  • Power BI IF Statement
  • Pivot Table in Power BI
  • Dashboard in Power BI
  • SUMX in Power BI
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel, VBA & Power BI,  Learn Power BI

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI VLOOKUP Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VLOOKUP in Power BI Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More