Improve power query performance

[twister8889]

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?

.......
 

[mahoneypat]

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]

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.

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[twister8889]

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?