Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Power Query extremely slow with imported data

Hey all,

 

I have a set up which looks like 1 big data table, then 5 different lists of items which I use as search criteria against free text in Power Query to create 5 new fields.  That all works like a dream when those lists have been directly entered into the document. 

 

My issue is that I was looking to store those lists outside of the document, likely on Sharepoint, so that they can be updated there instead of within Power Bi itself.  When I do that I see processing time more than quadruple vs when I just entered the data direct.  Does anyone know why?

 

The data hooks are on import mode, the import itself takes seconds as the files are small.  I've tried it on Sharepoint files, lists, CSVs, local CSVs etc etc but it all goes slow.

Need Help

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi Anonymous 

    Hard to say exactly what's happening but if you put the data into a remote storage location you have to expect slower loading.  Exactly how slow is it?  10 seconds?  20?

    Have you tried using OneDrive rather than Sharepoint?

    Regards

    Phil

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi PhilipTreacy 

       

      It's much much slower.  A test sample size of 1000 records took 8 seconds when the data was stored directly in the Power Bi file.  To do only half of the processing took 2 minutes when using data from an external source.

       

      I tested it from sharepoint, onedrive and physically on my machine it didn't make a difference sadly.  It's not the data pull that's causing it, if I pull the data with no processing after it takes a couple seconds.  But as soon as I want to use that data for lookups etc after the pull it goes slow. 

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi Anonymous 

    OK so it's not the data load that's the issue, it's the transformations.  It might be that whatever transforms you are doing are being pushed back to the data source (query folding).

    Can you share your query code?

    Regards

    Phil

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yeah it's definately to do with the processing after.

       

      This is the code used to create a custom column.  It searches a field called Description for the values in a lookup table.  The code works efficiently when the data is entered directly, but runs slowly when imported from an external data source


      let myvalue=[Description]
      in
      List.First(
      Table.SelectRows(LookupTableName,
      each Text.Contains(myvalue,[Value],Comparer.OrdinalIgnoreCase))[Value])