Power Query slowed down by vlookup formulas RRS feed

  • Question

  • Hello,

    I need help with an Excel file where the Power Query load is greatly slow down by Excel vlookup formulas.

    The file has 7 queries that loads data from 3 different files, in total the queries load around 13.000 rows of data, about 6 columns for each. All of the files the queries are pointing to, are located on network locations. The file has around 120.000 vlookup formulas. 

    When I click refresh all it takes about 1½-2 minutes to load all of the data, if I remove the two sheets with all of the vlookup formulas, all of the data is loaded in about 8 seconds. 

    I have turned off manual calculation and tried to disable all background refresh, but none of seems to make a difference.

    Has anyone experienced anything similar or know what can be done?

    Friday, February 23, 2018 3:29 PM


  • is there any way that you can replace those VLOOKUPs with the merge operation within Power Query?

    More info about the Merge queries operation in the next link:

    I'd highly recommend that you try to push as much of the Data Wrangling to Power Query and not Excel functions.

    Friday, February 23, 2018 5:45 PM