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

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

      • arjenbos91's avatar
        arjenbos91
        Frequent Visitor
        3 years ago

        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.