Forum Discussion
Help writing an M function
Anonymous,
Hi all, I'm trying to write a custom M function that I can use in multiple Power BI files. Our fact tables all contain DateKey columns which are integers in the format of YYYYMMDD...for example Oct 15, 2018 has a DateKey of 20181015. There are also DateKey values of -1, -2, and -3, which mean "unknown", "missing", and "deleted".
I'd like to write a super simple M function that would simply take a DateKey column and transform it into a date column, with blanks for any of the DateKeys that are < 0. But I'm finding this hard to do - all of the "custom M function" examples really deal with parameters instead of transforming a column.
Could you share more details about your requirement and give the expected result?
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Jimmy! We have fact tables with many date keys - links to a date dimension. All of the keys are stored in a format of YYYYMMDD - for example, the date key for Oct 20, 1967 is 19671020 (stored as an integer). I'd like a reusable M function that an end user could call to convert from a DateKey integer to an actual Date...without resorting to having to either use the PowerQuery "Merge" function to join to the date table 50 times...and without having to write a SQL override to do the same.
What I'd really like to do is have a M function where I could just pass it the name of a column, and have it "change" the column into a date datatype, with the proper date stored in it. For example:
Before (the DateKey columns are integer data types)
AppointmentDateKey AppointmentScheduledDateKey ...other columns...
20181215 20180617
20181215 20180321
Then I'd call the M function, something like fConvertToDate(AppointmentDateKey) followeb by fConvertToDate(AppointmentScheduledDateKey), and the resulting table would look like this:
...now the columns are DATE types
AppointmentDate AppointmentScheduledDate ...other columns...
12/15/2018 06/17/2018
12/15/2018 03/21/2018
Even better would be an M fucntion where I could just call it, and it would convert ALL columns that end with the suffix "DateKey" into Date columns.
We end up currently writing PowerQuery steps that take the DateKey column, change it into text, then text split into three columns so we get the year portion, month portion, and day portion in separate columns, then recombine to get an actual date. Works fine for one or two columns - but it's a lot of writing when we have a ton of date columns. I was hoping to get a simple function created that end users could use to combine all these steps into a simple function call.
Thanks,
Scott