Forum Discussion
Help with Power Query M Code optimisation
It isn't the M code, it is what are you loading into Excel, and have you ever loaded any of this data to an Excel table. Excel is notorious for allocating space in worksheets and not relcaiming it even if you remove the Load to Table functionality.
You can verify this by going to each tab and pressing CTRL-END and see where the cursor is. If you are somewhere around column GXR and row 800,000, that is your problem.
Hi edhans
I've come across that issue a number of times. For instance, you have a tab with many columns and a million rows initially. You keep only the first 100 rows by deleting all the others (even by eliminating all rows other than the first 100 with "Ctrl -"), save the file and it has the same size on disk than it did initially. Only after copying the 100 first rows into a new tab and deleting the initial tab did I manage to reduce the size to what it should be for a file with a tab with 100 rows only.
Do you know any other way (faster) reduce the size?
Thanks
AlB I do it in these - go to the end of my actual data and then:
- Delete all rows to the right and below the data
- Select ALL cells below the data (first to last coluimn) then on the home ribbon, Clear, All.
- Select ALL cells to the right of the data - all 1M rows, then Clear All.
- Save file
- close file
- reopen
- do CTRL-END again
usually that fixes it for me.
Hi edhans
My excel file contains at least 12 tables that are the source for the power query. Each is 30 columns and 200+ rows. Each table is populated by running a macro that uses several variables and calculations to populate the tables.
I have cleaned up the worksheets as per your reply but it doesn't make much difference to the size.
One solution I thought of was to write each table to it's own file in a folder and then in power query use the folder as the source. Do you think that would help since the power query workbook would then not be so large?
Lastly, are you saying there's nothing more I can do to make my M-code more efficient?
Thanks
Hi Stilish - I'm not saying there is nothing you can do to make your M code more efficient. It can always be more efficient, especially mine. 😁
But M code doesn't make a file big or small. If you are loading a table with 50 columns and 500,000 rows, that is going to be a big table. You can have the best M code in the world, and the table is the same size at the end. Or you can have the worst most horribly inefficient M code, and at the end, it is that same table. Bad M code can affect RAM and processing time, but once you save that file, that is all gone. it is just the output.
You can try this:
- Make a backup of your file.
- Delete a tab and save the file.
- Check the file size.
- See which tab caused a massive size drop.
- Open your backup and do what you can to fix that tab.
- Wash, rinse, repeat.
FWIW, this is one reason I've kind of abandoned Excel as a serious BI platform. Power Query is good, and up to date, but I've run into all kinds of permission issues with data sources when trying to share files, and the Power Pivot model is so far behind what Power BI has. I'd rather do the model in Power BI then use the Analyze in Excel or the new getting data from a Power BI dataset that is rolling out now.
