Power Query Fails to Convert Data Type, Error Persists

Ichabod871

New Member
Joined
Jan 11, 2018
Messages
1
Hello,

I have a power query pulling from a CSV file. I have set the data type in one of the columns as text, but there are errors that claim power query could not convert the data in the column to a number. I have tried refreshing the query. I've tried converting to a different data type then converting back to text. I've tried saving and reopening. Nothing works. Please help. Thanks in advance. And let me just take this moment to say I despise Microsoft and their crappy products. There, now I feel just a little better....
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Maybe you just have a crappy query of a crappy csv file.
We can't help you if you don't share these (may be dummy data as long as it has the same issue).
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
298
Office Version
  1. 365
Platform
  1. Windows
Without the data it's hard to say. The data in the column may not be all of the type you expect. Sometimes PQ will not know which transformation to apply in order to convert the outliers. Check the column for errors to ensure that the data is all of the type you expect.

For example, PeopleSoft has a known issue where certain characters like copyright and registered symbols in a text field will blow up its export to .csv. The characters are misinterpreted as EOF or EOL and it misaligns several subsequent rows of data. We have to tweak the files so that PQ will recognize those rows as errors.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,977
Members
417,120
Latest member
Pavithra devi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top