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
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..
Anonymous
I couldnt replicate the error to be honest.
It seems like there is something with the source data (excel). Maybe you imported the data from a txt file and during import the data were corrupted?? of there may be some weird formatting of the values