Power Query - Mixed numbers and letters error - help

[PowerBI-P]

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

[BA_Pete]

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]

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 · Accepted solution]

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

[PowerBI-P]

Hi BA_Pete,

Thanks you for your help. I have sorted the problem.

I have created file from starting then it's working.

Kind regards,

PV