Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
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
Proud to be a Datanaut!
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