Querying single data points from the Excel Data Model / Power Query (Get & Transform Data)

Question

I'm using an up-to-date version of Excel 2016 (via O365 E3 license) and using Power Query / Get & Transform Data. I can successfully create queries and load them to the page. I have also successfully created Power Pivot reports.

I would like to query single data points from the data loaded via Power Query. For instance, imagine a dataset called DivisionalRevenue with:

Date        Division    Revenue
2016-01-01  Alpha       1000
2016-01-02  Alpha       1500
2016-01-01  Beta        2000
2016-01-02  Beta         400

I could easily load that to an Excel workbook or include it in the data model and create a power pivot. However, Power Pivot doesn't always meet my requirements, particularly around how the data is displayed on the page. In order to achieve my goal I may want to be able to query individual data points.

I would like to have a cell on the page with a formula in it that I can use to query individual data points. If it was in a pivot table I could use something like:

=GETPIVOTDATA("Revenue",$A$3,"Date",DATE(2016,1,1),"Division","Alpha")

The lookup values (date and division) could be retrieved from a cell on the page or hard-coded into the formula. This is a requirement for several reports I'm working on.

Or, I could add a combined lookup column with Date and Division concatenated and use a vlookup to pull the values like:

=VLOOKUP("42371Alpha",I9:L13,4,FALSE)

Finally, I could use a combination of INDEX and MATCH to identify the correct row number and then pull the data.

All of these solutions require the data to be loaded onto a sheet. One requires a pivot table that has to be refreshed to work properly. The other two require creating arbitrary lookup columns so that you can match a row based on more than one field (date and division in this example), and you have to ensure that that lookup field's formula is properly extended down the length of the data table. In both cases I would have concerns when sharing this workbook with my colleagues in case someone affects the rather fragile setup of the pivot table or the lookup.

So, what I truly want to find is something equivalent to pivot table querying against a dataset.

** This doesn't exist, but I would like to know if something like it does **
=GETQUERYDATA("Revenue","DivisionalRevenue","Date",DATE(2016,1,1),"Division","Alpha")

Does such a thing exist? Can such a thing be done? Can I retrieve arbitrary data points from the dataset created through Power Query / Get & Transform Data?

Answer 1

I think that what you want are cubefunctions:

Some Background

How to easy create cubefunctions from a pivot table

Answer 2

There is a feature in Excel that allows you to query off of a PowerPivot model, but it's not highly advertised for some reason.

Once you have the data in your PowerPivot model, go to your Excel -> Data tab -> Existing Connections -> Tables tab

From there, choose the table that you want to start with. Once that table's data is on your excel sheet, you can actually right click that table -> go to "Table" -> "Edit DAX"

From there you can enter the following DAX function, as an example

EVALUATE
FILTER(SampleData,[Date]=DATE(2016,1,1) && SampleData[Division]="Alpha")

Make sure to choose Command Type=DAX in the drop-down. Here's how it looks on my screen:

To further improve your querying power, you can install the optional "DAX Studio" plugin for Excel, which allows you to write custom DAX queries and then export the results directly back to an Excel sheet.