Data Format Error - Couldn't Convert to Number

[Jessica553]

I am very new to power query, the most basic you can get. I have pulled these files into power query and I can't transform the data type. I have tried setting it to Text but I still get the below error.

I followed a tutorial to use Get Folder to bring all the files in a folder into power query, then did some normalising, changing columns etc. But I just can't get it to show these numbers that have a format ( 1111.A0) number/period/letter/number.
Any suggestions would be much appreciated.

 

[Jimmypop]

Hi there...

Untested...

If you want to change the data type of the column to a custom format such as "1111.A0", you can use the "Replace Values" feature to remove any characters that are not part of the desired format, and then use the "Text.ReplaceRange" function to insert the period and letter characters in the appropriate positions.

Here are the steps to do this:

1. Select the column you want to change the data type of.
2. Go to the "Transform" tab and click on "Replace Values."
3. In the "Replace Values" dialog box, enter any characters that are not part of the desired format (e.g. digits or spaces) in the "Value to Find" field, and leave the "Replace With" field blank. Then click "OK" to close the dialog box. This will remove those characters from the values in the column.
4. Go to the "Add Column" tab and click on "Custom Column."
5. In the "Custom Column" dialog box, enter the following formula to insert the period and letter characters in the appropriate positions:
   Text.ReplaceRange(Text.From([Column1]), 4, 0, ".") & "A0"
   Note: Replace "Column1" with the actual name of your column.
6. Click "OK" to close the dialog box. This will create a new column with the custom format "1111.A0".
7. If you want to remove the original column, select it and click on "Remove Columns."

I hope this helps! Le

 

[Jessica553]

Hi there...

Untested...

If you want to change the data type of the column to a custom format such as "1111.A0", you can use the "Replace Values" feature to remove any characters that are not part of the desired format, and then use the "Text.ReplaceRange" function to insert the period and letter characters in the appropriate positions.

Here are the steps to do this:

1. Select the column you want to change the data type of.
2. Go to the "Transform" tab and click on "Replace Values."
3. In the "Replace Values" dialog box, enter any characters that are not part of the desired format (e.g. digits or spaces) in the "Value to Find" field, and leave the "Replace With" field blank. Then click "OK" to close the dialog box. This will remove those characters from the values in the column.
4. Go to the "Add Column" tab and click on "Custom Column."
5. In the "Custom Column" dialog box, enter the following formula to insert the period and letter characters in the appropriate positions:
   Text.ReplaceRange(Text.From([Column1]), 4, 0, ".") & "A0"
   Note: Replace "Column1" with the actual name of your column.
6. Click "OK" to close the dialog box. This will create a new column with the custom format "1111.A0".
7. If you want to remove the original column, select it and click on "Remove Columns."

I hope this helps! Le

Thank you very much for your help. Unfortunately the replace has still left the error there. I tried to add the column anyway in case however the suffix A0 needs to change ie it can be any letter a-z and either a 0, 1 or 2.

 

[Jimmypop]

Hi... then I am not sure how to at the moment....sorry

 

[RoryA]

You can't convert 1111.A0 to a number because it isn't one. I'd guess the error is in your Changed Type step.