Custom Function to Format a Column

[NickTT]

Is it possible to create a Custom Function to format a date column? I have a data source where dates were saved as string in a funky format. To make them real dates, we have to do the following steps:

  // Format UPDATE DATE as DATE from STRING
    #"UPDATE DATE - ADD 19000000" = Table.AddColumn(#"Merged Columns1", "UPDATE DATE FIX", each Date.FromText(Number.ToText([UPDATE DATE] + 19000000))),
    #"UPDATE DATE - Change Type To Text" = Table.TransformColumnTypes(#"UPDATE DATE - ADD 19000000",{​{​"UPDATE DATE FIX", type text}​}​),
    #"UPDATE DATE - Change Type to Date" = Table.TransformColumnTypes(#"UPDATE DATE - Change Type To Text",{​{​"UPDATE DATE FIX", type date}​}​),
    #"UPDATE DATE - Drop Original" = Table.RemoveColumns(#"UPDATE DATE - Change Type to Date",{​"UPDATE DATE"}​),
    #"UPDATE DATE - RENAME" = Table.RenameColumns(#"UPDATE DATE - Drop Original",{​{​"UPDATE DATE FIX", "UPDATE DATE"}​}​)

I have thousands of date fields like this. It would be great to just create a simple function that I can call to fix it and I can reuse.

Example Data

Record	Date
1
2	9999999
3	1070206
4	0
5	1070401
6	0981215

9999999 = null

" " = null

0 = null

Dates that start with 1 = Year 2000

Dates that start with 0 = Year 1900

[edhans · Accepted solution]

Hi @NickTT thanks for the data. Paste this in a new Blank Query.

(Input) =>
    let
        DateConversion = try Date.FromText(Number.ToText(Input + 19000000)) otherwise null
    in
        DateConversion

Rename the blank query from Query1 to fxDateConversion. (You can call it whatever you like. I like using fx as a prefix so I know it is a custom function. It must all be 1 word with no periods too, so it cannot be fxDate.Conversion() that mimics Power Query function formats)

Now in a new custom column, use fxDateConversion([Date]) and see how it goes. It works with your sample data. I didn't need the extra date to text to date conversions.

EDIT: Here is an alternative way to do it too.

I duplicated your Date column to Date2. Then I did an inplace conversion.

The easiest way to do this is to select the column you want and go to the Transform ribbon, and select Format, Add Prefix, then just type the letter X. Doesn't matter. It will generate this code, which is what we need to work with:

= Table.TransformColumns(#"Duplicated Column", {{"Date2", each "x" & Text.From(_, "en-US"), type text}})

Now get rid of everything after "each" and before the ending "}})"

Replace it with 

each fxDateConversion(_), type date

Final line looks like this:

= Table.TransformColumns(#"Duplicated Column", {{"Date2", each fxDateConversion(_), type date}})

 The "_" is a placeholder for the current column.

You can dispense entirely with the custom function too. Just use the original formula:

= Table.TransformColumns(#"Duplicated Column", {{"Date2", each try Date.FromText(Number.ToText(_ + 19000000)) otherwise null, type date}})

[NickTT]

That one line of code has taught me a lifetime skill, Thanks!

[edhans]

Excellent @NickTT - glad I was able to help. Only took me 2-3 years of Power Query to learn that. 😂

[edhans]

Can you provide sample data we can work with? I don't think converting that specific code to a function is the optimal solution, but I cannot test my theory without original data to transform.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[NickTT]

Added sample to original post. We have the cleanup process down. Just want to see if there is a "Faster" way of doing it. Currently we just copy paste the steps in advance editor and clean up the formating. Was wondering if a faster solution would be to have a function we could call to quickly clean these up. Some tables have 50 dates in them and we have lots of data to cleanup before we can really start doing any reporting.

[Anonymous]

Did you consider creating a new column using FORMAT function pointing to the existing column?

Also go for changing data type of the existing column to date type.

Appreciate your kudos.

Mark my post as solution if this helps.