Custom Function to Format a Column

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