Forum Discussion

Stilish's avatar
Stilish
Frequent Visitor
6 years ago

Help with Power Query M Code optimisation

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 

Need Help

10 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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.

    • AlB's avatar
      AlB
      Community Champion
      6 years ago

      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  

    • Stilish's avatar
      Stilish
      Frequent Visitor
      6 years ago

      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's avatar
        edhans
        Community Champion
        6 years ago

        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.