Forum Discussion
any to text data type conversion producing unexpected values
I am consuming an Excel (.xlsx) file into Power Query. One of the source columns (call it "Month Key") has values like:
20212022.01
20212022.02
20212022.12
I need to transform this column into values like:
202101
202102
202112
When the file is read in, PBI assigns an "any" (i.e., ABC123) data type to this column. So, my transformation steps are like:
1) change data type to text (to allow for string parsing)
2) extract the first 4 characters; extract the 2 characters starting in position 9 (since indexing starts at 0 for Text.Range)
3) concatenate the strings in step #2
However, the above isn't working for all values. After step #1, some of the values are outputting an unexpected string. For example, 20212022.02 becomes 20212022.019999999. (If it helps to diagnose the problem, it seems to be happening to about half of the values.)
Why is this happening? And, how can I resolve this?
Anonymous
Try rounding the values first as mentioned on previous message.
Round the values first to 2 decimal places then convert the field to text... then check whether formulas now work
8 Replies
Anonymous
Checking the values have you tried using the functions Text.Start(field, 4) and Text.End(field, 2)?
I used Text.Start for my first 4 characters. That works fine. For the other string extraction, I need to get the 2 characters after the decimal point. So, I need to use Text.Range, not Text.End.
what is the formula that you use ... and also can you share the error with us?
A snapshot that shows which values cannot be converted
You can also try this formula:
Text.Start([Values],4) & Text.Range([Values],Text.PositionOf([Values], ".")+1,2)