Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NickTT
Helper II
Helper II

Custom Function to Format a Column

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

RecordDate
1 
29999999
31070206
40
51070401
60981215

 

9999999 = null

" " = null

0 = null

 

Dates that start with 1 = Year 2000

Dates that start with 0 = Year 1900

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.

edhans_0-1602691389044.png

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:

edhans_1-1602691804547.png

 

 

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

edhans_2-1602691947929.png

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}})

 

edhans_3-1602692020145.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

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.

edhans_0-1602691389044.png

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:

edhans_1-1602691804547.png

 

 

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

edhans_2-1602691947929.png

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}})

 

edhans_3-1602692020145.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

 

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors