Forum Discussion
any to text data type conversion producing unexpected values
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
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)
Anonymous
Have you also double checked the values in Excel? It seems that you only display 2 decimal places in excel file while there are more than that
If this is the case then when you load the data on power query, round the values to 2 decimal places
Number.Round([Values],2)
Then change the datatype to text.
Finaly check whether the above formulas work
Below are my transformation steps:
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fiscal Month Key", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each Text.Start(Number.ToText([Fiscal Month Key]), 4)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Fiscal Month Number", each Text.Range(Number.ToText([Fiscal Month Key]), 9, 2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Fiscal Month Key - modified", each [Fiscal Year] & [Fiscal Month Number])Again, I'm not experiencing a true error. Rather, because of the unexpected precision being used in the any-to-text type conversion, the 2 characters after the decimal aren't as expected. Perhaps the below will show it better:
beforeafter
Again, as an example, look at row 20212022.11. It is getting converted as 20212022.109999999. So, Text.Range is returning "10", not the expected "11".
I have checked the Excel file, and the values seem to be represented correctly.
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