Raw transactional data is rarely aggregated by month, quarter, or year for you.

However, managers often want reports by month or quarters instead of detail by day.

Here are several interesting things to note about the resulting pivot table.

First, notice that Quarters and Years have been added to your field list.

Keep in mind that your source data hasnt changed to include these new fields; instead, these fields are now part of your pivot table.

This figure shows the newly created time groupings to show sales for each market by quarter for 2010.

You can use your newly created time dimensions just like a typical pivot field.

He has more than 15 years experience consulting and developing Office solutions and has been named a Microsoft MVP for his ongoing contributions to the Excel community.

Excel Powerpivot Mac Mini And

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

Whether you pick the red or blue pill, youll 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 Id like to tackle this question in two parts.

In the first one, lets 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 Its the MacOS specific version of Excel.

Excel Powerpivot Full Desktop Experience

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, lets 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 (Microsofts Excel PM) from here.

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.

I didnt think that Id 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 itll never see the light on MacOS.

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.

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 Youre 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 dont try other connectors just yet.

This makes me believe that only a Implicit authentication is available at this point and you cant 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 ones that are missing are the ones related to Connectors, but theres 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 Thats when it hit me.

Sadly, we cant access the trace log (I have no idea if its 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).

Excel For Mac Powerpivot