How to escape characters in Date.FromText and Date.ToText?

[arjenbos91]

When using Date.FromText or Date.ToText and putting text values (not to be parsed) it returns an error while recognizing them as time format strings. (From the official docs here)

Example (Power Query)

= Date.ToText(#date(2022, 1, 1), [Format = "yyyy-MM-dd"]) 
// 2022-01-01

= Date.ToText(#date(2022, 1, 1), [Format = "yyyy-MM-dd-QZ"])
// 2022-01-01-QZ

= Date.ToText(#date(2022, 1, 1), [Format = "yyyy-MM-dd-m"])
// Parameter.Error: We couldn't convert the text value to date using the specified format. The format includes a time component.

How to escape the lowercase `m`?

I've tried escaping the `m` with a backslace `\m` and single quotes `'m'` but no success.

[BA_Pete]

Hi @arjenbos91 ,

Try this instead:

= Date.ToText(#date(2022, 1, 1), [Format = "yyyy-MM-dd"]) & "-m"

Markdown is with the ' </> ' button.

Pete

[arjenbos91]

Thanks Pete. My code above was just an example and I can confirm your solution works for that case. However I need to parse (Date.FromText) and format (Date.ToText)

"22m01d01"

 Do you have a solution for that as well?

[BA_Pete]

I'd probably add a custom column something like this:

let tDate = Text.Select([Column1], {"0".."9"}) in
Text.Combine(
    {
        "20" & Text.Start(tDate, 2),
        Text.Middle(tDate, 2, 2),
        Text.End(tDate, 2)
    },
    "-"
)

Example output:

Working example query:

let
    Source = "22m01d01",
    convToTable = #table(1, {{Source}}),
    addTextDate = Table.AddColumn(convToTable, "textDate", each let tDate = Text.Select([Column1], {"0".."9"}) in
Text.Combine(
    {
        "20" & Text.Start(tDate, 2),
        Text.Middle(tDate, 2, 2),
        Text.End(tDate, 2)
    },
    "-"
))
in
    addTextDate

Pete

[arjenbos91]

Thanks for your solution Pete. Too bad there is no escape character. Also no support for . or * wildcards it seems.

Your function works but it seems quite overkill. It decreases readability and therefore increases chance of errors. Especially when other developers will work on my code in the future. The entire report is already complex by design, so I prefer compact high quality code.

I stuck with converting the string to uppercase and "escaping" the M and D by using a nested string with single quotes:

= Date.FromText(Text.Upper("22m01d01"), [Format = "yy'M'MM'D'dd"])

which is still kinda meh.

The official powerquery docs Date.ToText refer to the dotnet docs for date/time formatting strings. Too bad it doesn't follow the \backslash\ escape character mentoined there.

[BA_Pete]

*EDIT* Actually, ignore this. It won't convert naturally to an ISO date as you need to prefix the "20" onto the year for recognition. Oh well, may be food for thought.

---------------------------------------------------------------------------------------

Yes, I agree the lack of escape/wildcards is frustrating, but rarely insurmountable.

Here's a slicker version of what I provided previously if it's any good for you?

Text.Combine(
    Splitter.SplitTextByPositions({0, 2, 4})
    (Text.Select([Column1], {"0".."9"})),
    "-"
)

This doesn't output into the exact yyyy-MM-dd format you specified previously but, if you just want to get a date from your input, I think this is a good option.

Pete

[arjenbos91 · Accepted solution]

Nice creative tricks, those Text.Select with {0..9} and the Splitter By Position.  I'm sticking to my convert-to-uppercase workaround. But your code will probably come in handy doing other text manipulation! Thanks 🙂

[arjenbos91]

Also, how to use markdown in this forum? I notice `quotes` do not produce inline code blocks here