Sub totals in Excel's Pivot Tables - which total should I sub?

This wonderful feature shows you why Pivot Tables (Power Query or other pivot table) wins hands-down vs Power BI, the much hyped MS app.
Let's say you have a Pivot Table (which I do, plenty of them).
And let's say they are summed by different values: Year, Quarter, and forecast vs. Actual (just an example). But you want to pick which columns (or rows) you want the sub totals to run on.
You can choose if you want to sub total all column or only few of them.
Let's look at an example:
This is what the header looks like:

Now I would like to have the total on the Year, but not on the Quarter.
First of all I need to go to PivotTalbe tools -> Design -> and pick the Subtotals that I want
( chose the Show all Sub Totals at the bottom of the group; you can pick top, but don't chose non).

I can go to the columns definition, right click the setting (FyQ, in my case) , choose the Field Settings, and then I have the choice of Subtotals: Automatic or None. So If I want it to be summed I would and if not then I don't.

The beauty here is that I don't need complicated MDX functions in order to design my Pivot table. Great!

MDX with Excel - for future reference

Let's say you have data model that you want to enquire on. If you're using Power Query (in Excel) you might want to investigate a little further. In order to do that you need to unleash the power of MDX, which is complete and utterly crazy. The problem with MDX (unlike plain Excel functions) is that it's really hard to know what you're doing and there's very little documentation. Nevertheless it's important to read the documentation, and you'll get the hand of it - after a while.

So, how do I find out what was the last date in my cube that actually gave me some data?

My model consist of an SQL database, in which there's a table called dimPeriod, and measures which are stored in a Fact Table. The data is queried from Excel using Stored Procedures, and reported via PowerQuery. Obviously enough, the dimPeriod table includes days from the last couple of years to the next 20+ years. That's enough, and it's great as it warns me if I'm trying to enter the wrong date (yes, I'm using foreign keys to enforce the referential integrity).
Let's just say that [ALL ACP] is the table where there are measures. So I thought of using something like TopCount function to create a set. Unfortunately, it didn't give me the correct results:

=CUBESET("ThisWorkbookDataModel","TopCount([ALL ACP].[Datekey].Children,5)","Max Date Reported")


Explanation:
Cubeset - creates a cube set
"ThisWorkbookDataModel" - the data model that is stored in this workbook. That's true for Excel 2013, and basically, once you type the "Cubeset" function, open bracekts and the double qoute, it comes up with the name of the model.
TopCount - give you the first number
[All Acp] - the table that has measures in it
DateKey - yep, that's the datekey
Children - otherwise you'll get "all"
5 - just a number. I could (or should) have used 1, but 5 gives me a range of numbers to work with.

After inserting that set to a cell in Excel, all I needed was to enquire after that set:

=CUBERANKEDMEMBER("ThisWorkbookDataModel",$G$30,1)


G30 is where the code above is inserted.
A problem: I got the minimum date.
Solution:

=CUBESET("ThisWorkbookDataModel","BottomCount([ALL ACP].[Datekey].Children,5)","Max Date Reported")


Changing from TopCount to BottomCount gave me the latest date to come in. Please note that there are no measures in the query.

You have two options to query on the result, which is in DateKey format.
The first one is to query the relevant data member. Let's say, you're looking for FY Year, you would use the following code:

=CUBEMEMBER("ThisWorkbookDataModel","EXISTS([Dim Period].[FY Month Name].children, [Dim Period].[DateKey].["&G32&"])")

Where G32 is where you result is
Alternatively, just get out the year, the month and the date using:
Year

 =LEFT(G32,4)

Month:

=MID(G32,5,2)

Day:

=RIGHT(G32,2)

Combining the Excel Formulas with the MDX ones gives you lots of power to manipulate the end results.

pfhtreadpool.cpp, line 970, function PFThreadPool::QueueWorkItemMultiple

If you get this error it is very simple:
Your Power Query in Excel is tired.
Restart your computer (or at least, log off and log back on again)

Connection providers that Excel uses for Power Pivot and Power Query

For Power Query Excel (2013) uses Microsoft.Mashup.Oledb.1
If you don't have Power Query installed you'll get the following error message:

Microsoft.Mashup.OleDb.1 provider is not registered on the local machine

(Had anyone ever tried marking this message as "unhelpful"?)

In the Data ribbon, 
If you go to Existing Connections -> Select a Connection or Table -> Mark the connection and right click it -> Edit Connection Properties -> Definition Tab

you will see that the connection string starts with:
Provider=Microsoft.Mashup.OleDb.1


If you use only Power Pivot and you try to link to a connection, then the connection string looks like this:
Provider=SQLOLEDB.1;Integrated Security=SSPI;

Why is it mixed? Why are there two types of connection strings?

It depends on who started the Power Pivot query and when. There is also a difference if you have Power Query Installed on your machine or not. Unfortunately, if you installed Power Query and then upgraded office you might need to install Power Query again (or, at least, Repair it). 

As far as I'm concerned this is the main problem with Excel as a Reporting tool:
Excel is amazing. It could do many things, use many languages (DAX & MDX) but if you can't share it easily between people since it is flavor-dependent (are you using 2013 or 2010 or 2016 or Office 365? do you have Power Query installed or not?) then it causes huge problems. Ribbons disappearing, Queries have to be rewritten, connection strings have to be rewritten... Version control shouldn't be such a huge issue, IMHO.

Updating source for Power Query In Excel after changing the table name

If you work with Excel 2013 and you create Power (either Power Pivot or Power Query) queries, and you had changed the name of the underlying SQL server database table,

you need to do the following in order to re connect :


First, go to the Power Query tab and then choose either Show Pane or Launch Editor

The Workbook Queries will appear on the right.
If you don't have Power Query tab in your ribbon (it appears after ADD-INS), you need to install it
(correctly).

You will see all the queries marked with yellow exclamation mark. You click on them and change them.

Once you double click on the Query in question the Query Editor will come up.

If you changed the database Choose in Applied Steps the Option of Source, and choose the name of the new database.

Go to Query Editor - View (last tab)

Choose Advance Editor:

And now you can change the table name to the desired name.

If you want to change it to a Stored Proc in Excel, type in the query details in the query name, like this:

And change the Source in the "Advanced Editor" as follows (

It is called M Programming language for Power Query)

So you don't need to name the type here, you just say it is the source, and that works!

No, I couldn't find any documentation for this thing, I just played around. 

I hope it helps :)