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.

Can anyone give me some advice on how to get started with this?

Thanks,

Scott

[v-yuta-msft]

@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.

[Anonymous]

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

[jdbuchanan71]

Try adding this as a custom column in PowerQuery

if [DateKey] < 0 then null else Date.From(Text.From([DateKey], "en-US"))

Then if you need to you can delete the old [DateKey] column and rename the new column to [DateKey] 

[Anonymous]

Hi jdbuchanan71,

the code you have exactly mimics what we're doing in PowerQuery currently - we change the integer DateKey column into text, then convert to date (although I've seen some users change it to text column, split into year / month / day columns using split by number of characters, and then build a true date column out of that.

The experience I was hoping to get was some sort of custom function where a user could just create a column and paste in something like "fConvertKeyToDate(AppointmentDateKey)" or something similar to have it. Ive seen YouTube videos that makes it appear that we can write custom functions...but none of them seem to deal with things like taking an existing column, performing transformations on it, and then changing the datatype of the column.

Actually thinking now I should have called this topic "Help writing a PowerQuery custom function"...

Thanks,

Scott