Power Query Management Studio reloaded: Now supports MDX

Very happy to see that my Power Query Management Studio isn’t just perceived nerdy but useful as well 🙂 Thanks to Dusty for his nice review.

So let’s push it a bit further and add some MDX functions to it that cannot be done by DMVs:

  • get a list of all unique fields used in a specific MDX query
  • translate your code to a different cube using a simple field-translation table

How to use this for MDX:

Read more

Use Timeline or Slicers to filter your Power Query import

A question in the Mr. Excel forum this morning reminded me that the technique I’ve blogged about here could also be used to do simpler things like using a timeline slicer to filter your Power Query imports. So here’s a quick shot on this:

  1. Import your calendar table into the data model (load only)
  2. Create a pivot on it with just one field: Date
  3. Put your timeline on it
  4. Return the resulting filtered pivot back to PQ by using an offset-function in a named range (no way to push this pivot directly back into PQ)
  5. Import the table to be filtered in the next step and merge in modus: JoinKind.Inner. This will only return the rows that have a match on both sides, thereby act as the filter we want. As this will allow query folding to happen (speed up your queries if accessing a SQL relational DB), do this as your first step before doing any other transformations on your source data.

Have a look at the file:

TimelineFilterPQ.xlsx

 

You will also find some exercises on filters on multiple columns in there if that is of interest.

Enjoy & stay queryious 🙂

Create a Dimension Table with Power Query: Avoid the case sensitivity bug!

Creating a Dimension table from a fact table using Power Query is really straightforward using the Remove Duplicates function.

However – you might experience a problem if the key to your Dimension table that you’re extracting from the Fact table is text and not number format. Power Query is case sensitive and will consider “Car” and “car” as different, returning both after the remove duplicates step. Once you load this into your Power Pivot data model, it will be shown there as “Car” and “Car” or “car” and “car”, depending on which term was the first in the list (will always take the first one).

This further means that you will not be able to connect you new Dimension table to your Fact table as the Dimension table now has dups.

To overcome this (and because it might be good practice anyway):

Read more

Is it time to remove detail fields from our cubes?

During my evaluation of Power Query as a reporting engine I wondered why we should keep detail fields in our cube at all if the preferred output is a flat table anyway. Cubes are meant for aggregation, aren’t they?

Especially in the Finance- & Accounting area you will come across many cubes with detail fields because sometimes you simply need to perform analysis on ledger entry level. But this seems like a loose/loose scenario in my eyes: Not only do these detail reports often perform badly, their biggest negative impact might lie in the fact that they cause the fact tables to be x-times bigger than the next aggregation level, thereby decreasing the overall performance of the cube.

So how about this approach then: Use Power Query for your reports on detail level: Directly connect to your fact table in the DWH and merge to your SSAS-data in order to retrieve the attributes/filters only. Or keep your fact tables in a dedicated DB if your DWH serves other purposes as well and you fear the performance impacts of those queries.

So this would leave the cubes’ fact tables with much less data -> improving performance.

I tried some scenarios that worked fine. But putting the fact tables into a separate tabular model instead of a relational DB performed quite badly.

Does anyone have experience with this approach? If you know someone who might, please forward.

What do you think about this approach, any other obstacles I’ve missed?

Report Design with Power Query (1): Cascading Time Granularities

Cascading Time Granularities

When the client is not happy with the pivot report layout options on cubes in Excel, my usual reaction is “OK no problem, then we’re going to use cubefunctions instead”. This went well until recently: My client told me that he wanted a report like above: Years totals first, followed by quarters & months totals, but with growing number of years! All nicely close together, no gaps. Normally I don’t mind cubereports with dynamic table length (will blog about this later), but this is basically 3 dynamic reports under each other (yes they are still alive and will probably stay 😉 ):

  Screen_3Years

So it was time to test what Chris Webb has talked about in this blogpost: Using Power Query as a report authoring tool in Excel. The idea was to use Power Query’s append-function to attaching 3 different report: Year, Quarter, Month. They could then have different lengths, the append-operation would seamlessly stitch them together. The connection to the cubedata was easy enough, thanks to his brilliant step-by-step guide. But then there were some challenges to solve:

Read 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

Search Options in Power Pivot

Search Options in Power Pivot

1) In the Power Pivot window: Ctr+F acts as a metadata-search that not only finds strings in columns but also in measurenames

2) Want to search for strings within your measureformulas: Create a list of all your measures in an Excel-sheet using Dax-Studio: See this Blogpost

 

Waterfall Chart using Power Query

How to create a Waterfall Chart using Power Query?

Target is a chart like above: Starting point, several movements & then an ending sum. Every movement starts at the end of the previous movement, upsides in green colour, downsides in red.

The source data for this kind of charts are normally missing the last line like this example with a P&L:

SourceData

So this makes it always a bit clumsy to integrate in an automatically refreshing reporting package, especially if the number of lines/bars to report is dynamic.

So here comes the Power Query solution: Fully automatic & dynamic – calculation & charting.

Simply update your source data (Table: “Sourcedata”) & the rest will be done automatically. In this simple example the order of the Sourcedata-table will be kept in the chart.

Refresh the query or check “Data – refresh all” – done.

You can use this as it is – just prepare your table accordingly (fill in your numbers manually, create links or a power query to your source data… however), no need to adjust the query or even have a look at it.

Waterfall

 

But if you’re interested in how this works, just read on 🙂

Read more