Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 int...
Need Help
  • themistoklis's avatar
    themistoklis
    5 years ago

    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

themistoklis's avatar
themistoklis
Community Champion
5 years ago

Anonymous 

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      themistoklis
      Community Champion
      5 years ago

      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's avatar
        themistoklis
        Community Champion
        5 years ago

        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