Forum Discussion

twister8889's avatar
twister8889
Icon for Helper V rankHelper V
5 years ago

Improve power query performance

Hi guys, 

 

I'm trying to improve the data refresh. I have 12 excel files, that contain 6 tabs. I'm using transform data to do the ETL, one of these transformations have more than 30 steps ( I don't know if I can minimize the steps )

Here I have some examples of my structure. ( I'm sorry for the picture but I can't share the data model )

 

Any suggestion to improve the data refresh?
 - Try to minimize the steps its enough?
 - Try to create a model without transformation data steps?

.......
 

 

Need Help

4 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey twister8889 ,

     

    usually Excel and CSV is not made for such big transformations.

    Do you have the possibility to load the files to a relational database? Then you have the advantage that  Query Folding can happen and improve a lot.

     

    Otherwise think to load the XLSX and CSV on a cloud storage like Sharepoint, Dropbox or OneDrive and load the data with a data flow:

    Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs

    Then you have the advantage that the data can load a little slower in the background. With your Power BI Desktop file you can then connect to the already processed data flow tables.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • twister8889's avatar
      twister8889
      Icon for Helper V rankHelper V
      5 years ago

      First of all, thank you for your answer

      - I don't have at this moment to use the relational database

      - I don't  have the access to use the data flow

       

      Considering this scenario, do you think it's possible to improve the performance and change the structure ( not many queries ), excluding some steps?

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Your refresh issue is more likely due to the 30 steps and what you are doing there?  Please share that M code from the Advanced Editor so we can propose alternate approaches (perhaps with a buffer step if appropriate).

     

    Also, how many tables are you loading to the model?  If multiple, can you disable load on all but one separately to see which one is the slow one?

     

    Pat

    • twister8889's avatar
      twister8889
      Icon for Helper V rankHelper V
      5 years ago

      First of all, thank you for your answer

       

      I will try to reproduce the M code here, unfortunately, I can't share the code. However, I will try to minimize the steps, I don't know if is possible in my scenario because I have a lot of transformations.

      Here I have the excel source:

       

      https://1drv.ms/f/s!AoDYwrtLrltJn1AmP9R-vWOWRP_w

       

      Each tab, its a table in power query, and after of apply the transformation I linked the tables for some keys.