any to text data type conversion producing unexpected values

[Anonymous]

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?

[themistoklis]

@Anonymous 

Checking the values have you tried using the functions Text.Start(field, 4) and Text.End(field, 2)?

[Anonymous]

@themistoklis 

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.  

[themistoklis]

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)

[themistoklis]

@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

[Anonymous]

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.

[themistoklis · Accepted solution]

@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]

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..

[themistoklis]

@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