Power Query very Slow

[morrtenjansen]

In PowerBI Desktop I have 

(parameter): cdir: C:\Users...\

let
fName = "x.xlsx",
Source = Excel.Workbook(File.Contents(cDir & fName), null, true),

and it hangs forever and ever.

The x.xlsx is 2 cols x 30 rows.

This is very annoying, any tips ?

[Anonymous]

@morrtenjansen 

I guess this is because you have made many transformation when shaping the data, more steps meaning more calculation, that could take longer time. If you can post the M codes see if we can optimize the calculation.

Paul Zheng _ Community Support Team

[Jimmy801]

Hello @morrtenjansen 

could you share your complete code? (not only the function, but also the complete code where you are invoking it)

How big is your file?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[morrtenjansen]

@Jimmy801, This one is part of a model with several other files. The actual file is quite small, 2 columns x 30 rows. One files is 67Mb but 5-6 other input files are around 50-100 kb.

I do some merging etc. but nothing that should cause wait time of 10-15 minutes before even seeing the resulting table(s) from the queries.

I do frequently clear cache and close/open, sometimes even reboot but the performance issue is persistent.

[Jimmy801]

Hello @morrtenjansen 

okay. But nevertheless a file with only 67 MB should not take that long to process. However, as long as I cannot see all your code I can't tell you anything. For sure the slow performance is not caused by the size of your files, but from how your code is structured and what kind of transformation you need to execute.

BR

Jimmy