Easy Profit and Loss and other (account) scheme reports in Power BI and Power Pivot using DAX

This is about an easy way to create typical finance reports like Profit and Loss using DAX that (unlike all other solutions I’ve come across so far) can be handled with very basic knowledge of this language like this:

Image1

The trick

The trick that makes my solution so easy lies in the fact that it requires no aggregation functions of the output-mediums like:

  • pivot-tables: who struggle with asymetric logic and are not available in Power BI so far
  • cubefunctions: who are not available in PowerBI so far

So we have to build the details as well as all aggregations into the solution as it is and don’t rely on/use any aggregation functions (This means for Excel: We have to turn off subtotals as well as totals in our pivot tables. It means for Power BI: Hurray! Finally a solution where the lack of pivot-tables doesn’t matter).

How to

The aim is to create a table/matrix with (account) details and aggregations into the rows and different slices of time-Intervalls or comparisons into the column sections. As for the columns, this will be covered by measures like [Actuals], [Budget], [PreviousPeriods], [Differences in all shapes…]. And – as the values in the columns should be the same – I’d prefer to use only one measure per column – that is fully sliceable and works on all (sub-) totals of course. … Ok – so some dreams later I found it:

MyMagicMeasure := CALCULATE([StandardMeasure], AccountsAllocation)

So you just wrap simple measures like Act=SUM(Fact[Amount]), Plan=SUM(Plan[Amount]), DiffPlan_Act=[Plan]-[Act] … into the CALCULATE together with the bridge-table as the filter-argument:

This is the many2many-technique in it’s simplest form (PostFromMarcoRusso). It all goes via simple aggregation on all accounts found in the filter context:

Image4

Our  bridge-table “AccountsAllocation” consists of one account number per simple account and has multiple rows for the (sub-)totals – being all accounts that belong to them:

Image5

The ConsKey stands for the row in our report (1) and the AccountKey_ holds the account numbers that are going to be aggregated (many (for the sub-totals) and 1 for the account-rows). So all we need is this unique and simple aggregation on AccountKey for every row in the report – with a filter from the Reports-table via our bridge table to the DimAccounts, who then filters our FactTables: 1 -> many -> 1 -> many.

Read more

Advanced type detection in Power BI and Power Query: Table.ClassifyMixedColumnTypes

This is not a proper blogpost, just a quick share of a function I’ve created today which I think will be very useful for others as well:

Automatic type detection will assign only one type to a column, but sometimes there are multiple types in it and then the type “any” will be applied, which means no type at all. In these cases, often the type of the individual elements/rows in those columns are the key to the necessary ETL-transformation operations to be performed. So you would like to be able to write statements like this:

Table.AddColumn(PreviousStep, “Event”, each if Type.Is(Value.Type([MyColumn]), type text) then ThisEvent else AnotherEvent)

(reading: Add a column with a new Event that depends on if the type of the current row in MyColumn is text: then do ThisEvent else do AnotherEvent)

My advanced type detection function will identify and allocate different data types. It will try to apply a couple of type conversion operations and in case of success, apply the type to the individual cell/record field and in case of failure move on with the next tries. If none of the type conversions succeeds, it will return the original state (any). It takes the name of your table and a list of the column names on which it should be applied as the input-parameters.

(Table, ListOfColumnNames) =>
let
Table.ClassifyMixedColumnTypes.m = Table.TransformColumns(Table, List.Transform(ListOfColumnNames, each {_, each try Date.From(_) otherwise try Number.From(_) otherwise try Text.From(_) otherwise _}))
in
Table.ClassifyMixedColumnTypes.m

So this function will apply different data types (expand as you like/need) within one column on a row-by-row basis – which is what I’ve been looking for quite a while 🙂

Thank you so much Bill Szysz for showing me how to use the List.Transform-trick in order to bulk-apply transformations!

If you’re as lazy as me, you could be tempted to pass “Table.ColumnNames” to the “ListOfColumnNames”-parameter – but this might slow your query down! (Guessing – not much practical experience gained yet)

A warning at the end, that this is of course error-prone – as some strings like “3.2” for example are not unambiguous and can get converted as a date although in the specific context should be numbers (and vice versa). So you should check properly and – if needed – perform some additional transformations before in order to guarantee the correct outcome. But at least in my case it did exactly what was needed.

Please share your thoughts/experiences with this function & stay queryious 🙂

Step2

How to analyse M-functions step-by-step?

This is a quick tutorial on how to analyse M-functions step-by-step.

Queries, that have been transformed into a function (1) can be invoked in the query-editor (2) and they will then return their output for you to check (3). But they will not show you how they did it. In the query-editor where you normally see the single steps and their individual output if you click on them, you will just see one line for the function and one for the invocation (4):

Step1

So how can we make them reveal all their secrets for us to see the quickest way?

Yes, we could edit the code and replace every parameter that has been passed. But this can get a bit tedious, especially if you haven’t written the code by yourself and don’t know where and how often the parameters are used. A very quick and safe way is to replicate the parameters within the let-expression by copy-&pasting like this:

AnalyzeM-Functions

That way you just define them once and don’t have to care about how often they are used within the following query. And it is very quick to do as you can just copy the parameter-expression and comma-separate your inputs.

Step2

I bet you want to check this out on Chris Webb’s latest blogpost here: DynamicDateSelections.zip

Enjoy & stay queryious 🙂

Multiple replacements or translations in Power BI and Power Query

A common task when cleaning data is to perform multiple replacements at once. When dealing with “translation tables” you might have come across Chris Webb’s solution using List.Generate to deal with this. This works fine – but might deliver unexpected results if you don’t consider it’s implications properly. Say we add lines like shown below, then we might expect the result as highlighted in yellow:

Image2

Multiple replacements using 2 different techniques

So hey – what happened here?: Our catamaran turns out as a bearamaran and the chair turns to chwater. Read more

How to hack yourself in Power BI (and Power Pivot?)

Reading Gerhard Brueckl’s post on how to visualize SSAS calculation dependencies reminded me of my post about a similar technique from December last year.

His solution has features that would do my version good as well:

  1. Directly connect to the model to be analysed without clumsy export of measures to txt via DAX Studio
  2. Including calculated columns? No!: Who does calculated columns in DAX in PBI? Do them in the query editor using M instead (more functions, better compression, easier merge of model to SSAS once needed)

So wouldn’t it be cool if we could just add a documentation page to our current model – “all in one” so to speak? Here you find how to “hack”-connect with Excel to your current Power BI Desktop-Model.

So what works with Excel should work with PBI as well – just that we need to connect via the query-editor, using M. And of course: As we’re hacking ourselves here (i.e. the file we’re currently working on), we need to save our changes in order to make them being shown.

Read more

PowerBI Best Practice: Views for non-database data

Marco Russo has shared bunch of very valuable PowerBI best practice tricks for designing your PowerBI model here: http://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/?platform=hootsuite

One of them being never to directly import a database table, but to use a view instead as a kind of abstraction layer. It separates your business logic (that determines how the tables should be structured that will be passed into the data model) from your actual/incoming data (your source systems, whose table structure you cannot change and which might change itself over time).

This is really important in my eyes, as it will be only a question of time when your underlying data will change and the last thing you want to do is to edit all datamodels or even reports in your (multiple and possibly decentralized) PowerBI files!

But what do you do if your data don’t come from databases or you have no possibility to create views on them? Read more

TableTransformation

How to share M-code in PowerBI and Power Query

One of M’s advantages is that you can share M-code in textform and it will run instantly on a different computer. So no need to actually exchange a file. Provided that you pass the data on as well with your code, like you can see in this example.

But it would be a bit tedious to type in all the data manually – so let’s M do this automatically for us:

1 Automatically transfer a list into textform to share M-code

let
qList= {"This", "is", "a", "pretty", "short", "list"},
Source = qList,
CoreString = Text.Combine(List.Transform(Source, each Text.From(_)), """, """),
FullString = "= {"""& CoreString &"""}"
in
FullString

2 Automatically transfer a table into textform to share M-code

let
qTable= Table.PromoteHeaders(Table.FromColumns({ {"Column1" ,"This" ,"an" ,"shorter"}, {"Column2" ,"is" ,"even" ,"table"} })),
Source = qTable,
DemoteHeaders = Table.DemoteHeaders(Source),
ListOfColumns = Table.ToColumns(DemoteHeaders),
ConvertToTable = Table.FromList(ListOfColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
CoreString = Table.AddColumn(ConvertToTable, "Custom", each Text.Combine(List.Transform([Column1], each Text.From(_)),""" ,""")),
FullString = "= Table.PromoteHeaders(Table.FromColumns({ {"""& Text.Combine(CoreString[Custom], """}, {""")&"""} })),"
in
FullString

Read more

How to handle M-code samples

If you use Power Query or the query editor in Power BI every step you take will be recorded and translated to M-code. You can share this code and it will run in the new environment – so no need to actually exchange a file.

1 Check out what the M-code does

In order to make this code-sharing easy for beginners, I’m sharing my code in a way that it will run instantly with the test-data provided within the code. So you just copy my code examples into the advanced editor like this (example given for Power BI):

Home -> External Data -> Get Data -> Blank Query -> Home -> Query -> Advanced Editor.

There you replace all the content in the window: Check existing content, paste and click Done.

PasteCode

That’s all, now check out how it works: On the Home-tab check Close -> Close & Apply. This will load the data to the data model and you can build your reports with it.

2 Check out how the M-code does it

Read more