AuthorAnthony ArchivesCategories |
Back to Blog
Does Excel For Mac Have Pivot Chart Wizard10/10/2021
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. Power Query can run up to 600% faster on Excel for MacYES! – I’m not kidding!! I’m almost as shocked as you are.This blog post won’t be about which OS is better than the other. Read more…The program is called Wizard, and to be perfectly honest with you, I think it’s the most exciting desktop software since the spreadsheet.Wizard is built from the ground up to make data analysis… well, pleasant.You won’t find any command line or programming language in Wizard.To summarize a variable… just click on it.You’ll instantly see a picture like this:Wizard knows what you want based on context.And Wizard runs appropriate statistical tests to give you the answers you’re looking for, even if you don’t know exactly the right question to ask.So if you’re new to statistical computing, you’ll find that Wizard is the absolute best choice for your first statistics program.Bar-none, hands-down, no question about it.Wizard has a gentle learning curve, and mistakes are difficult to make with Wizard’s intuitive, informative interface.If you’re a business user, you’ll love Wizard’s ability to export PDF graphics and interactive Excel spreadsheets.Wizard will help you present and explain statistical findings, and assist colleagues in making better business decisions using Wizard’s powerful modeling techniques.Finally, professional researchers will find that Wizard Pro is an excellent companion to SAS, Stata, and SPSS, helping you to do more research in less time.All versions of Wizard have advanced analytic capabilities, including: I’ve designed a new kind of statistics program.Because it doesn’t matter whether you’re a seasoned statistician, or are just getting started with data analysis.I know that you want a program that’s easy to use, that produces research-quality output, and that gives you advanced insights instantly. No artifical limits on the size of your dataIf you ever need more than Wizard can offer, the Pro version lets you copy-paste generated commands from Wizard into your favorite stats program and take it from there.But you may never need to: Wizard handles millions of rows of data with ease.I could go on about how great Wizard is, but nothing explains Wizard better than the fun and excitement of using Wizard for the first time.So go ahead: download the free trial and start experiencing the joy of Wizard for yourself. I have seen some posts from 2016 that suggest that ATP-VBE caused problems and may have been removed as an option by Microsoft.What can I do with Power Query for Excel on Mac?The most important part about Power Query, besides its user interface, is the actual engine that runs all of the transformations. 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. 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.Testing Power Query in Excel for MacMy first intention was to figure out what M functions are already available inside of this integration. You can get queries from other folks (who create them in Windows Excel) and you can refresh them inside of MacOS. Excel files (not from Table / Range yet)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.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. Refresh an existing query inside of a WorkbookThe only supported connectors right now are for local files: 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: 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.
![]() Does Excel Have Pivot Chart Wizard Mac And ThisAbout my Hardware used in the testsI do have an acceptable hardware in my own opinion. It’s basically M code in its purest form, so you don’t have to worry about any prompts or issues. 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. ![]() 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. 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. I’ll be updating this post as soon as new features get added to the tool.Support for new connectors is a given.
0 Comments
Read More
Leave a Reply. |