Help with Power Query M Code optimisation

[Stilish]

Hi Folks

I am new to Power Query and am building an analysis that with the associated queries and Pivot Tables and Pivot Charts has caused my file to balloon to over 1Gig. I would be really great if someone could look at my code in the linked file below and let me know how I can optimise it. I have also done screenshots of a few of the outputs and which queries they are linked to.

Thanks a million

https://drive.google.com/file/d/1g7vj1Oef1sNlMhKhdI3cq5dOrtP5i2ut/view?usp=sharing 

@Greg_Deckler @edhans 

[edhans]

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.

[Stilish]

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

[edhans]

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.

[AlB]

@edhans 

Yeah, it's a shame Power Pivot in Excel is left behind and not kept up to date.  So now is it possible to  get  data from a Power BI dataset in Excel? Where can I read about it? I believe I have the latest version of Excel 365 and haven't seen anything of the like

Thanks

[edhans]

You have to be either on he Insider or Monthly Targeted channel, but don't bother. It was clarified yesterday that this will require an E5 license when it rolls out. Right now you only need a Power BI Pro license, but that is just in the preview stage. Worthless.

The article about it is here.

[Stilish]

Hi @edhans 

Thanks. Can you provide more insight and possible reference material for your comment:

"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."

Thanks

[edhans]

@Stilish - see Analyze in Excel here.

See my comment to @AlB about the new Power BI datasets, but that is for E5 licenses only I've recently found out. 😡

[AlB]

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  

[edhans]

@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.

[Stilish]

Hi @AlB 

Check this out: It works.

https://support.office.com/en-us/article/locate-and-reset-the-last-cell-on-a-worksheet-c9e468a8-0fc3...

@edhans  I'm working on your response and will revert shortly.

Thanks