In a certain way this article is the sequel to “Power BI Data Modeling – Events and projections”, but here I’m using Power Query to create the same fact table, or at least almost the same table, the name is slightly different. If you are not aware what this is about, please take the time and read the article from above.
I tend to create tables using M or a combination of point-and-click and M, because these tables are treated as base tables if the model is refreshed and for this reason these tables will benefit from all the compression magic, whereas tables created using DAX are not that well compressed.
For this reason I just focus on the M part to create the fact table “Fact Projection M”. This is the M that creates the table, as you can see it’s a combination of point-and-click, but it also
considers some advanced techniques (at least the techniques are advanced from my point of view 😉).
Here you will find the pbix file that now also contains the M
code creating the table "Fact Projection M"
This is the first part of the M code I consider interesting:
// get the frequency from the Period table, of course this can also be achieved by using a Merge transformation
// this later used to determine the projection date
AddCol_NoOfMonth = Table.AddColumn(
#"Namen van kolommen gewijzigd"
, "Frequency"
, each
let
p = [Period],
f = List.First(Table.SelectRows(Period , each [Period] = p)[Frequency])
in f
),
The interesting part is how to pass the [Period] field from the outer table by using a let expression. This allows to use the outer field [Period] to query the remote table Period.
The 2nd interesting part is this:
// this adds a list, the nomber of items in this list represents the number of projections
AddCol_Projection = Table.AddColumn(
AddCol_NoOfMonth
, "Projection"
, each
let
p = [Period],
bd = [Begindate],
ed = if [Untildate] = null then CalendarMaxDate else [Untildate],
//the number of months
nom = fnDateDiffNumberOfMonths( bd , ed ),
//the frequency
f = List.First(Table.SelectRows(Period , each [Period] = p)[Frequency]),
//the number of projections
nop = Number.IntegerDivide( nom , f ) + 1,
//a list that contains the
projections
projection = {1..nop}
in projection
),
The above code returns a list the contains the number of projections. Within this code I use a little custom function that returns the number of months between two dates, unfortunately there is no M function like the DAX function DATEDIFF(smallerdate , greaterdate, MONTH). For this reason I wrote a little function.
Writing DAX, I quite often use the functions GENERATESERIES(…). In M the syntax to create a list of integer values with an increment of 1, is this {start..end}.
Thanks for reading!
Kommentar schreiben