Power Query in Excel for Mac: First Impressions

image

OK – you might not have a Mac and you might not be convinced on Excel for Mac just yet, but here’s something that will light up your eyes if you’re a Power Query user:

Power Query can run up to 600% faster on Excel for Mac

YES! – I’m not kidding!! I’m almost as shocked as you are.

Image result for blue pill red pill matrix

This blog post won’t be about which OS is better than the other. Whether you pick the red or blue pill, you’ll still get Power Query inside of Excel.

I know quite a few folks that are using their Mackbook Pros to run Windows using Parallels or Boot camp, but never use Excel for Mac and this might be a good reason to start using it.

What is Power Query in Excel for Mac?

I’d like to tackle this question in two parts. In the first one, let’s define some initial concepts:

  • What is Power Query? Power Query is the Microsoft Data Connectivity and Data Preparation technology that enables business users to seamlessly access data stored in hundreds of data sources and reshape it to fit their needs, with an easy to use, engaging and no-code user experience.
  • What is Excel on Mac? It’s the MacOS specific version of Excel. It is not the same as the one that you get on your iOS like your iPhone or tablet, but rather a full desktop experience, but not exactly the same that you might be accustomed to on Windows.

OK – with that out of the way, let’s go with part 2: what is Power Query in Excel for Mac?

Well, in essence is the integration of the new standard Microsoft business-user oriented Data Preparation tool inside of Excel on Mac.

The integration is still in its early stages, but it was released on a public preview through the Office Insider program for Mac. You can read the full press release by Guy Hunkin (Microsoft’s Excel PM) from here.

What can I do with Power Query for Excel on Mac?

image

The most important part about Power Query, besides its user interface, is the actual engine that runs all of the transformations. Guess what! That engine is now able to run inside of Mac.

I didn’t think that I’d be alive to see Power Query run on a MacOS, especially since PowerPivot has been around for almost 10 years now and it seems like it’ll never see the light on MacOS.

so, why Power Query and not PowerPivot? that’s a good question and something that I’d let the MSFT team answer, but I do firmly believe that Power Query is a tool that has a way bigger audience/reach. While PowerPivot is more intended to be used by the Power Users, Power Query can be used by almost 70% of the Excel userbase.

The current integration is very limited on what you can do, but so much of the ground work is already in place. Again, the engine is already in place, so that’s BIG.

What exactly can we do right now with this integration? We can do 2 things:

  • Refresh an existing query inside of a Workbook
  • Change the File Paths of our queries

The only supported connectors right now are for local files:

  • Text / CSV
  • Excel files (not from Table / Range yet)
  • XML
  • JSON

You’re probably wondering, what happens if you try using a connector not previously listed? Well, you end up in an infinite loop of prompts telling you that something went wrong, so I highly recommend that you don’t try other connectors just yet.

image

This makes me believe that only a “Implicit” authentication is available at this point and you can’t use other types of authentications just yet.

The current integration basically limits you to consumption only. You can get queries from other folks (who create them in Windows Excel) and you can refresh them inside of MacOS.

Testing Power Query in Excel for Mac

My first intention was to figure out what M functions are already available inside of this integration. You can see the full list of available functions on Power Query in Excel for Mac from here.

Most of the functions are already in there. Most of the ones that are missing are the ones related to Connectors, but there’s more than 680 functions already in MacOS just dormant in there – waiting for someone to use them.

I then did a simple test refreshing a query that connected to a file on a local file inside my windows PC and it gave me an error, so I went into the connections window, change the file path to my local MacOS path and it worked.

That was pretty straightforward, but what about the performance?

That’s when it hit me. The engine is already there. We need too perform an engine test.

Sadly, we can’t access the trace log (I have no idea if it’s even available in MacOS), so we have to go back to our roots and do something similar to what Chris Webb did back in the old days (as showcased here).

That’s how I modified Chris’ original code and ended up with this code:

I created a query from that code and saved the file in my SharePoint so I could easily access the file from both my Mac mini and my Windows PC.

The good thing about this query is that it’s not using any connectors. It’s basically M code in its purest form, so you don’t have to worry about any prompts or issues. It’ll simply run.

About my Hardware used in the tests

I do have an acceptable hardware in my own opinion. Is not an apples to apples comparison (pun intended), but it’s what I have, so it’ll do:

My Windows Desktop PC

  • CPU: Intel i7-7700K CPU @4.20GHz, 4 cores, 8 logical processors, 8MB L3
  • RAM: 32GB DDR4 @ 2133 MHz
  • Power Query version: 2.70.5494.701 64-bit
  • OS: Windows 10 Pro 64-bit

My Mac Mini (v2018)

  • CPU: Intel i5-8500B @ 3.00GHz, 6 cores, 6 logical processors, 9MB L3
  • RAM:  8GB DDR4 @ 2666 MHz
  • Power Query version: Office Insider Fast Channel (201907)
  • OS: macOS Mojave

Running the tests

When I test this on my Windows 10 PC, I do see that my CPU is running at 100% during the whole processing of the query and the final results say this:

image

and when I test this same query on my Mac mini, the average result that I get is this:

image

    and yes – my CPU does the same turbo boost for its processing time, but in this case it’s much faster and finishes evaluating the query in just 3 to 4 seconds.

    One of the key pieces of my test code is that I use a Buffer function, and when I remove that piece of my code, the comparison is much closer, but the Mac Mini still wins by up to 25% faster.

    Comparing Apples to Apples

    What if I run this same test on the same hardware?  I was advised to use Boot Camp to install Windows 10 and partition my hard drive so I could test the same query under the same hardware.

    That’s exactly what I did. I installed Windows 10 through Boot Camp, installed Office and then ran the query inside of my Windows inside my Mac mini and the average of that test was 17 seconds. The fastest time was around 12-14 seconds:

    This is still a big difference! Power Query in Excel for Mac is still at least 3x faster than on Windows. Why? I have no clue. I wish I had the answer, but I’ve contacted the MSFT folks letting them know about my findings.

    The reality is that I’m thrilled about this because it means that Power Query can get better. Power Query can run faster without just throwing more resources at it, which translates into more consumer-grade computers being able to take full advantage of Power Query without being limited to processing power.

    We are far from seeing the best performance that Power Query can deliver. Now Power BI dataflows or Power Query Online has its own set of configurations that enable you more resources and capacity, so if I try doing this same test with dataflows, I can’t even begin to imagine how crazy fast it would be. I need to give it a try.

    Try this on your own!

    I highly encourage you to do this test on your own Mac computer and let me know your results in the comments section. You can download my workbook from the button below and all you have to do is simply click refresh.

    Download Test Workbook

    Perform your own tests and let me know your experience!

    What can you expect in the future for Power Query inside Excel for Mac?

    Note: This is pure speculation and me guessing what things might happen, so take things with a grain of salt. I’ll be updating this post as soon as new features get added to the tool.

    Support for new connectors is a given. We can expect new connectors for things like SQL Database, ODBC, Web.Contents, probably SharePoint and others like the From Folder connector. This has happened before in other Power Query integrations, and I doubt that this will be the exception.

    Apart from that, I really hope that they end up integrating the full query authoring experience with the full Power Query interface inside of Excel for Mac. Right now there’s a huge dependency on having someone that has Power Query on a Windows PC so that they can create the query and the file for you, but maybe there are already some situations like that in the real world?

    If you’ve tried Power Query in Excel for Mac, leave you feedback in the comments below.

    UPDATE 2-October-2019: Power Query in Excel for Mac has hit GA. Read more here (url).

    Update 9-December-2019: I recently did a webinar to cover more on this topic. You can watch the full recording from the video below:

    Download the files used in the webinar

    Posted in:

    15 Comments

      • Hey!
        When you open the workbook you might get a security warning for the query inside of it. You need to click on “Enable content” before hitting the refresh button on the data tab, otherwise nothing will happen.

        I’ve just tried this (even re-downloaded the file from here) and it works perfectly for me.
        Are you getting any errors? if you’re getting any then I’d recommend sending your feedback to the Power Query team.
        You could recreate the workbook on your own, but you’d need Excel on Windows as well and then create your query in there using the code published above.

        I’m not entirely sure why you might not be able to run the query, but I’d still recommend getting in touch with the Power Query team to let them know about your situation

      • Hey! No clue what might be happening, but I highly recommend that you get in touch with Microsoft to report the issue. You can always try to replicate the query from scratch instead of from a file, but it’s difficult to pinpoint what might be happening with your Instagram of excel

      • Hi Ehud

        When this issue occurs, can you please click on the smiley face on the top right of your workbook window and select “I don’t like something” ? Then you can provide additional context.

        This will automatically open an issue in Microsoft’s customer voice and will help us investigate this issue. If you also provide your email as part of that feedback, it will allow us to communicate with you directly if we need additional information.

        [Disclaimer: I am a Microsoft employee working on PowerQuery for Mac]

    1. I use latest version (16.29.1) for Mac (supposed to be Excel 2019 Home & Student)… I can open the file, but I can not refresh data. More worse: I don’t see the mentioned connectors, only “Aus HTML” and “Aus Text” (yes, somehow I ues the german version). At least “Excel files” would be great. Then also, when I somehow try to get to the so far hidden PowerQuery editor, I tried via using “Aus Text”, but what it does is only importing the text file via the import wizard. Far away from the Power Query editor… what’s wrong with my version? Is there a Add-In installation required? If yes, where to find this PowerQUery Addin for Mac?

      • The article doesn’t mention anything about the Power Query Editor inside of Excel for Mac because is simply not there (at least not yet).
        The engine is there and that’s why the article uses the analogy of the iceberg, as the vast majority of the work that has been done is behind the scenes at the engine level that has been implemented in Excel for Mac.

        I don’t really use Excel for Mac, so I wouldn’t be able to tell you what “Aus HTML” or “Aus Text” represent. Perhaps that’s the standard experience for getting data in Excel for Mac? That would be my best guess.

        If you cannot refresh the file, please send a frown report. On the top right of the Excel for Mac window you’ll be able to see the icon to send your report.
        That would be the best way to contact the Microsoft team about any issues regarding Power Query inside of Excel for Mac.

    2. Hello Miguel,

      when you tested power query on the same hardware ( the Mac hardware, assume) did you run the test with or without the buffer, you mentioned?

      I wonder if the memory speed (2133 vs 2666) could account for some of the huge speed difference when PC hardware and Mac hardware is compared one to one.

      it would be interesting if this indeed is the case.

    3. Hey when I try to refresh Query database on Mac I received following [Expression.Error] The module named ‘ExcelInterop’ has been disabled in this context.
      Do have any idea what am I doing wrong?
      Thanks a lot in advance.
      I am using Office 365 for Mac

    4. i have a query (power query ) created on Excel by Windows, this file put on Nas synology.
      i can run query with any PC windows on Lan
      but i can not run query by refresh on menu Data by my Macbook.
      So, how can i run query on Macbook, because as i know, Macbook don’t have power query.

    5. Yes, same issue here – I am trying to manipulate data from a HUGE CSV file, so importing is a FAIL (too many rows), but I see your test has many more than the 1M+ in Excel, so if all Excel gives me is “From HTML”, “From Text” (yes, that’s all “Aus” means – “From”) and “New Database Query”, then I still do not have the solution of “New Query” like they do on Windows for gigangtic CSV files:
      https://blogs.technet.microsoft.com/josebda/2017/02/12/loading-csvtext-files-with-more-than-a-million-rows-into-excel/
      I want to query it purely externally, not try to import tha dnag thing and be right back where I was. WHere’s the love? You talk about the VNA and your results but nowhere can I find how to SETUP the source without first importing it, which is a catch-22 for massive CSV files…that MS blog is no more helpful either. Anyone know the true Steps 0 and 1 before the above step 4 or 5 shown?

      • The query definition needs to happen on Windows. This means that when you open the Mac for Excel version you might need to go through the “Connections” pane to define your new local path and be able to query your file from the source of your choice.
        If you’re encountering issues, I’d highly recommend that you send your feedback directly to the Microsoft team by clicking on the “frown” icon or you can also post your full scenario, file and questions on the official Power Query forum here:
        https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

        Best!

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.