10/11/2021 0 Comments Do Power Query In Excel 365 For Mac
The 2nd step in my query, after my source step, expands the table. There are a few columns in the source file with dates that use a custom date format. Situation: I'm working on a project that pulls in data from an Excel (.xlsx) source file via Power Query in Excel.
Do Power Query In Excel 365 Mac Does NotYou’ll get the tools to make a big difference at your workplace.YES! – I’m not kidding!! I’m almost as shocked as you are.In addition, Excel for Mac does not support Power Pivot, Power View, Power Query, or Power Maps. Plus learning Excel Power Query will be more fun and effortless than you think. If you work with Power BI, you also have the latest version of Power Query. Power Query can run up to 600% faster on Excel for MacIf you have Excel 2016 or above, you have Power Query as a part of your Excel (Power Query is Not available for MAC). Excel for Microsoft 365 for Mac If you're a Microsoft 365 subscriber, you can refresh existing Power Query query queries and use VBA to create and edit new queries.In the first one, let’s define some initial concepts: What is Power Query in Excel for Mac?I’d like to tackle this question in two parts. 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.You can read the full press release by Guy Hunkin (Microsoft’s Excel PM) from here. 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. What is Excel on Mac? It’s the MacOS specific version of Excel. ![]() 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. The engine is already 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. Most of the functions are already in there. You can see the full list of available functions on Power Query in Excel for Mac from here. Testing Power Query in Excel for MacMy first intention was to figure out what M functions are already available inside of this integration. Infinity land biffy clyro raritanPower Query version: Office Insider Fast Channel (201907)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:And when I test this same query on my Mac mini, the average result that I get is this: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. CPU: Intel i5-8500B 3.00GHz, 6 cores, 6 logical processors, 9MB L3 CPU: Intel i7-7700K CPU 4 cores, 8 logical processors, 8MB L3 Is not an apples to apples comparison (pun intended), but it’s what I have, so it’ll do: About my Hardware used in the testsI do have an acceptable hardware in my own opinion. New orleans funk 2 rarYou can download my workbook from the button below and all you have to do is simply click refresh.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. 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. 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 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. 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. 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. Read more here (url).Update 9-December-2019: I recently did a webinar to cover more on this topic. 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. 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. We can expect new connectors for things like SQL Database, ODBC, Web.Contents, probably SharePoint and others like the From Folder connector.
0 Comments
Leave a Reply. |
AuthorCarlos ArchivesCategories |