• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

#PowerQuery – Replicate doing an Excel VLOOKUP in M

March 3, 2019   Self-Service BI

Power Query has a lot of built in functions, but it doesn’t have a function that exactly matches the VLOOKUP in Excel – but by using the M language and other M functions we can certainly replicate some of the VLOOKUP functionality and make an even more flexible lookup function.

Now the example data is like this

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

In Excel we would lookup the price for at specific productkey by using this formula

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

– in this case ProductKey 1 with a price of 100.

In order to replicate this in Power Query we can use the function List.PositionOf

So I add a new blank query

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

And then use the function List.PositionOf – with the following arguments

List – Is the column ProductKey from my lookuptable Products – refer to like this Products[ProductKey]

Value – Is the value to look in this case the value 1

Occurrence – Is set to 0 to only return one value

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

This will return the position of the value in the list – sort of like using the MATCH function in Excel

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

Now to return the price – we can use this result to lookup the price like this

= Products[Price]{List.PositionOf(Products[ProductKey], 1, 0)}

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

And we get 100 returned which is the price of productkey 1.

The formula is structured like this

=NameOfTheTable[NameOfTheColumnToReturnTheValueOf]{PositionReturnedByListPositionOf}

But we why not change it into a function in PowerQuery so we use the function on all rows in a table or on any table.

The function can be created like this

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

The code

(lookupValue as any, lookupTable as table, lookupColumnName as text, returnColumnValue as text) =>
let
// lookupTable= Products,
// lookupColumnName = "ProductKey",
// returnColumnValue = "Price",
// lookupValue = 1,
 colLookup = Table.Column(lookupTable, lookupColumnName),
 colToReturn = Table.Column(lookupTable, returnColumnValue),
 lookup = List.PositionOf(colLookup, lookupValue, 0),
 Result = if lookup >=0 then colToReturn{lookup} else "Not found"
in
 Result

The function takes 4 arguments –

lookupValue – The value to find – can be any type

lookupTable – The Table/Query to lookup in

lookupColumnName – The name of the column to lookup the value in

returnColumnValue – The name of the column from the table to return

The colLookup is a variable that uses the function Table.Column to return a list of values in the lookup column.

The colToReturn is a variable that uses the function Table.Column to return a list of values from the values you want to return column.

The lookup variable uses the List.PositionOf to find the position/index of the search value in the lookup column.

Result will use an if statement to test whether a position is found and if so returns the value at the position in the colToReturn list – other wise returns the text “Not Found”.

After this we can use the function in other tables to lookup the for instance the Product price like this – by added a invoke Custom Function Column

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

OBS – I haven’t tried this on a large table so be aware of any performance issues.

Hope you find this useful – and happy Querying

Here is a link to an example file – Link

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

#PowerQuery, Doing, Excel, Replicate, VLOOKUP
  • Recent Posts

    • Pearl with a girl earring
    • Dynamics 365 Monthly Update-January 2021
    • Researchers propose Porcupine, a compiler for homomorphic encryption
    • What mean should I use for this exemple?
    • Search SQL Server error log files
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited