Forum Discussion

PowerBI-P's avatar
PowerBI-P
Frequent Visitor
3 years ago
Solved

Power Query - Mixed numbers and letters error - help

Hi,

Please help me on this issue.

 

I've changed the Stock Code column to text then used custom columns to created separate columns like text and numbers using below formulas then applied and getting same error. 

 

I didn't understand what is the issue here.

 

 

=Text. Select([Stock Code],{"a".."z","A".."Z","-"})

= Text. Select([Stock Code],{"0".."9"})

 

Kind regards,

PV

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    It seems there's something in your code that's making Power Query try to change the data type of your [Stock Code] column to a number.

    Can you copy the whole query code from Advanced Editor and paste it into a code window ( </> button) here please?

    Feel free to anonymise the connections strings etc.

     

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi PowerBI-P ,

     

    Power Query can't convert this to a number because it ends in the letter "B".

    If all of your [Stock Code] values have the same format, it might be easier if you just select your [Stock Code] column, then go to the Transform tab > Split Column > By Digit to Non-Digit.

     

    Pete

    • PowerBI-P's avatar
      PowerBI-P
      Frequent Visitor
      3 years ago

      Hi BA_Pete,

       

      Thanks for your help.

       

      It's not all of same format, only few. I have two files with similar data then one file has done successfully with below formulas but other file couldn't do it. See the example. First, I have changed Stock Code data type to text then split the columns with below formulas.

       

      =Text. Select([Stock Code],{"a".."z","A".."Z","-"})

      = Text. Select([Stock Code],{"0".."9"})

       

      not sure, why other file is not working. 

      I tried your method as well but didn't work either. Any suggestion after seeing above data.

       

      Kind regards,

      PV

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        It seems there's something in your code that's making Power Query try to change the data type of your [Stock Code] column to a number.

        Can you copy the whole query code from Advanced Editor and paste it into a code window ( </> button) here please?

        Feel free to anonymise the connections strings etc.

         

        Pete