none
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

Answers

  • 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:

    https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9

    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