Perfect Analysis Services (SSAS) reports in Excel using Power Query

Excel-reports on SSAS cubes (multidimensional and tabular) can have some flaws that now can be overcome by using Power Query for sourcing your cubedata:

  • filter your cube by complete Excel-tables without loading them to the model/cube
  • Apply nice number and date formats to non-measure number and date fields in your row- or column section
  • create fast detailed reports (multiple attributes in your row sections, overcome the slow MDX that the pivots on cubes produce)

As with the recent Power Query update (26) you can now create your own MDX and DAX-statements for retrieving data from a cube, it is also possible to pass individual parameters from your Excel-sheet to the queries. This is a prerequisite for dynamically reducing the number of returned fields to the query, thereby allowing a decent performance of these reports.

So how about filtering the query by a table that sits in your local Excel file? Can we do an inner-join just like on the SQL-server-sourceRead more

How to pivot multiple measures and columns in Power Query and Power BI

The pivot-function in Power Query lets you pivot your data within the query already. This comes in handy quite often when you want to further work on those results within the query. There’s not much to parametrise in the function, so the question is how to handle it when you want more than one measure/value to be returned.
Let’s say you have a table like this:

Ausgangstabelle

And it shall look like this:

02_Ziel

Read more