PowerBI is the Microsoft offer for applied Business Intelligence.
To abstract that solution in a raw way, it can be seen as a front and back end part.
Even more raw, the involved languages for both parts are respectively DAX and
M.
M serves as query language, which means querying various databases and preprocessing that data into a
format that is then later used by the front end visuals.
As a query language, M brings, if implemented consequently, a clear and stepwise data processing.
A lot of those steps can even be created through the back office tool - PowerBI Query Editor - visual elements and buttons.
Later for fine tuning those visually created steps can be modified by the Advanced Editor. In that very raw IDE, the
steps are finally written as M code. The default variable naming, generated through the visual approach are not the most readable ones.
Already this fact makes it worth to have a look into or write the M code directly by hand.
This very structured approach has some pecularities though.
Besides that issue, that the Advanced Editor is not a real IDE, some of the concepts which come very natural in other
languages, do not come that natural in M. At least not from first sight. One of those concepts, that come surprisingly
special is the for-loop.
M includes the concept of objects and functions on that objects. When it comes to non standard uses cases, which shall be
presented here through an example, some implementations comenot too intuitively.
One of those classes that M has, is called List.
A list is an object containing N elements of the same type.
In the following example, the list includes timestamps, which forms another class
DateTime
in M. In case one wants to calculate
the time delta between every two sequential timestamps, i.e. timestamp_1 - timestamp_0, timestamp_3 - timestamp_2, timestamp_5 -
timestamp_4, etc., with the final aim of calculating the total amount of those time deltas, then a for-loop can be a simple approach.
A use case could that those timestamps are in and out times of a alternating process and one wants to calcuate
the times to calculate the money spent in the meanwhile. Another approach would be to create a data type e.g.
Alternation with in and out time as property and a function on that class which returns the delta.
But the fastest way probably remains the iteration on the List object.
To calculate this sum of deltas, a first step is to hand over the list of timestamps to a function, defined to solve the
described problem called fxCalcDeltaSum.
The list of timestamps will be present in each row of a column called Timestamps.
After the transformation on that column, it might make sense to call it Timedeltas afterwards
through Table.RenameColumns().
The idea is, to first generate a list of accumulated deltas and then take
the last value from that list.
This can easily been done through the List function
List.Last()
Another approach would be to create a list of deltas and then sum up all the list elements.
preprocTable = < Code to generate time deltas lists >,
timeDeltaSum = Table.TransformColum(
preprocTable,
"Timestamps",
fxCalcDeltaSum),
fxCalcDeltaSum = ( ls as list ) =>
let
acc = List.Generate(
),
lastAcc = List.Last(acc)
in
lastAcc
The calculation of the delta logic and finally the for loop
has to be included into the List.Generate() function.
preprocTable = < Code to generate time deltas lists >,
timeDeltaSum = Table.TransformColum(
preprocTable,
"Timestamps",
fxCalcDeltaSum),
fxCalcDeltaSum = ( ls as list ) =>
let
acc = List.Generate(
() => [acc = #duration(0, 0, 0, 0), i=0],
each [i] < List.Count(ls),
each if (List.Count(ls)>1) and
(Number.Mod([i], 2)=0) then
[acc = [acc] + ls{[i]+1} - ls{[i]}, i=[i]+1]
else
[acc = [acc], i=[i]+1]
each [acc]
),
lastAcc = List.Last(acc)
in
lastAcc
The for-loop, realized through the List function starts with an initial condition.
Here, two elements acc and i are initialized,
which will be updated during the iteration of the
for-loop. The second term after the first each, describes the stop condition.
In that case each element of the given list ls should be processed, so after List.Count(ls)
elements, the for-loop is finished. This obviously has the consequence, that i has to be updated per iteration.
This is done in the section of the second each. In M terminology this is called next.
To define what happens next and what will be checked by the condition.
The last each, is called selector. This one is optional and only of usage in case of multiple arguments
in the intial. As this is the case and the iterator i is not of further usage, only acc has to be
accumulated.
The initially logic is packed into the second each section. First a list with one single element
cannot have a delta with the following one. So this is the first exclusion criteria. Besides that every
second element is chosen for a calculation through the Modulo function of the Number object type.
The delta is finally determined through ls{[i]+1} - ls{[i]}, so the choosen list element by
the modulo and its following one.