Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Help writing an M function

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 1...
Need Help
jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago

 

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's avatar
Anonymous
Not applicable
7 years ago

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