Forum Discussion
PowerQuery Refreshing for a Long Time
I have a PowerQuery that connects to multiple excel worbooks to a sheet called PHYS in each workbook. Just yesterday It seems that I started to encounter issues. I am refreshing the query and its taking a long time but I also see an issue in the Transform Sample File. Not sure what the issue is but it is taking forever to refresh the query. Does anyone know what to do in order to resolve this? Usually it take a bit to refresh but its just going on and on..
It looks like it starts spinning on the invoke custom function here:
Here are other screenshots that may or may not be helpful but please let me know if I can provide other ones to help.
3 Replies
The method you used will read the first file twice. If you want to avoid that you can roll your own processing function to ingest the PHYS sheets from all the workbooks.
From the looks of your code some of the steps can probably be combined or rearranged.
You can use Query Diagnostics to find out which step takes the longest.
I am a self taught PowerQuery user so I am certain I am super innefficient at this. Can you point me how to make this more efficient. You said you can roll your own processing fnction to ingest the Phys sheets. Can you point me to some literature so I can read to perform an effecient way to grab specific worksheet in multiple workbooks in a folder. Maybe I can learn from an example? Thank you for responding.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
