Date.fromtext passing too early of a date to dataverse

[dsjaff]

I'm trying to sanitize some records with bad dates that were originally stored as text by copying them into a new field. For the records that are stored as alpha characters, this line is correctly passing a null to the new date column :

try Date.FromText([cr09e_column11]) otherwise null

but if the date it tries to pass is something like 12/31/159, dataverse errors out as the date it too far in the past. How do I add another error check that passes null if the date is prior to dataverse's maximum date of 01/01/1753?

[watts_jim · Accepted solution]

You'll want something along the lines of this :

if Number.FromText(Text.AfterDelimiter([cr09e_column11],"/",1)) < 1953 then null else [cr09e_column11]

[dsjaff]

Can you tell me why "try Number.FromText(194*)  otherwise null" fails? If that value it come across isn't a number, because of the astericks, why is the code failing? Is "194*" a number to power query?

[dufoq3]

Because * is character for multply. I you try this one - it will return null.

= try Number.FromText("194*") otherwise null

[dufoq3]

Hi @dsjaff, what about this?

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzbUNzIwMlaK1YFxDU0twTxkIUtTAzDXwFDfAMwFaogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Date"}),
    AddedCustom = Table.AddColumn(ReplacedValue, "Custom", each try if Date.From([Date], "en-US") < #date(1953,1,1) then null else Date.From([Date], "en-US") otherwise null, type date)
in
    AddedCustom

[dsjaff]

This has way too much going on for me to understand. I've only used power query a handful of times.

[dufoq3]

Of cource if you do not use PQ often, it is not easy to understand some queries. Were you able to use my query? If no check note below my posts or just use part of code form AddedCustom step and edit with your needs (column name). Add it as custom column.

try if Date.From([Date], "en-US") < #date(1953,1,1) then null else Date.From([Date], "en-US") otherwise null

[watts_jim · Accepted solution]

Null is a special case - try Googling 'power query null'.

You'll want something like this:
if [cr09e_column15] = null then null else if Number.FromText([cr09e_column15]) < 1753 then null else try Date.FromText(Text.Combine({[cr09e_column14],[cr09e_column13],[cr09e_column15]},"/")) otherwise null

[watts_jim · Accepted solution]

You'll want something along the lines of this :

if Number.FromText(Text.AfterDelimiter([cr09e_column11],"/",1)) < 1953 then null else [cr09e_column11]

[dsjaff]

I got it to work as this:

if Number.FromText(Text.AfterDelimiter([cr09e_column11],"/",1)) <1753 then null else try Date.FromText([cr09e_column11]) otherwise null

I thought this would be easy to adapt for my next field, but I'm getting errors when testing another field. This one I am trying to make into a full date based on separate month/day/year columns:

if Number.FromText([cr09e_column15]) < 1753 then null else try Date.FromText(Text.Combine({[cr09e_column14],[cr09e_column13],[cr09e_column15]},"/")) otherwise null

I'm unclear why this errors if column 15 is null. Isn't null less than 1753?