Forum Discussion

arjenbos91's avatar
arjenbos91
Frequent Visitor
3 years ago
Solved

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

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.

 

 

 

Need Help
  • arjenbos91's avatar
    arjenbos91
    3 years ago

    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 🙂

8 Replies

  • arjenbos91's avatar
    arjenbos91
    Frequent Visitor
    3 years ago

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

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    Hi arjenbos91 ,

     

    Try this instead:

     

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

     

     

    Markdown is with the ' </> ' button.

     

    Pete

    • arjenbos91's avatar
      arjenbos91
      Frequent Visitor
      3 years ago

      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's avatar
        BA_Pete
        Icon for Super User rankSuper User
        3 years ago

         

        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

  • shukla's avatar
    shukla
    Regular Visitor
    9 months ago

    As per this page from PowerQuery documentation:

    To prevent a character from being interpreted as a format specifier, you can:

    • Precede it with a backslash.

    • Surround it with a single quote.

    • Surround it with two double quotes.

    I tried below code and it works fine:

    =Table.AddColumn(#"Added Custom", "Custom", each Date.ToText(#date(2022, 1, 1), [Format = "yyyy-MM-dd-'m'"]))