Forum Discussion
How to escape characters in Date.FromText and Date.ToText?
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 🙂
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.
*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
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 🙂