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
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
Rounding to 2 decimal places before doing any data type conversion worked. I don't understand why it worked, though. I also had to do an additional Text.PadEnd step to slap on a zero at the end. So, a lot of steps before the extraction and concatenation simply because there seems to be something wrong in the Excel file..