Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
duncanwil
Frequent Visitor

csv data type

Dear All,

 

I downloaded a CSV file from a finance web site. I have downloaded their CSV files before and use them in Power Query, Excel and so on so I know they are usually fine.

 

The first row the file contains the headers I want so there is no problem there.

 

The table in the file contains a column of account names and then the next 11 columns contain annual values for each account: really simple accounting report.

 

I noticed that a Column chart was showing the bars as COUNT so I went to Data View and tried to change the Data Type for the 11 columns from Text to Whole Number ... it says We can't automatically convert the column to Whole Number type.

 

Any idea why and what I can do as a work around? I know I can convert the file to Excel and so on but it's a step I don't want to have to take as you will appreciate.

 

Many thanks

 

Duncan

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have see this happen sometimes when you try to convert it to a whole number and it wont let you.  For me, it was because some of the fields really were NOT NUMBERS.  Double check your data to make sure that:

1. There really are only numbers in the column

2. If your header repeats itself in the file, make sure you remove that header by filtering it out.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hello,

 

be very careful with blank cells at your excel / csv file in your number columns. Make sure your blank cells are valued to '0' 

 

Thank you

Anonymous
Not applicable

Hi @duncanwil,

 

   this COUNT column contains only numbers? If the answer is yes, did you try to auto-detect the data type?

You can do it like this:

1) Go to "Edit Queries"

2) Go to "Transform"

3) In the "Any column" section select "Detect Data Type"

If the COUNT column data type turns into Any, it means that your column contains different type of values.

 

Let me know.

 

#I'M Not An Expert#

Thank you for your help.

 

I do what you say and normally it works but just now and again it flares up. It's probably me but I can't think how and why!

 

Thanks for your suggestions anyway.

 

Duncan

Anonymous
Not applicable

I have see this happen sometimes when you try to convert it to a whole number and it wont let you.  For me, it was because some of the fields really were NOT NUMBERS.  Double check your data to make sure that:

1. There really are only numbers in the column

2. If your header repeats itself in the file, make sure you remove that header by filtering it out.

Thanks Adam,

 

It's a while since I posted my request but over the last few days it's happened again. I went through the processes that I always go through but got the problem. I checked here and found that we are all thinking the same things. Still it happened.

 

So I left the problem alone for 24 hours as I thought about it. 

 

In the end, I went back to the query editor and tried one more time and it all came right. I cannot say that I did anything different this final time. Of course, I don't know whether this will happen again either.

 

Thanks for your suggestions anyway.

 

Duncan

fbrossard
Advocate V
Advocate V

Hi @duncanwil

 

  1. Just modify your query,
  2. select your column and verify the data type, in your case it should be "Text" or Any "Type"
  3. change the type to "Whole Number" => it adds a new tranformation step on the right panel
  4. Close and apply your changes

And then it should works.

 

 

Thank you for your help. It's probably me but I can't think how and why! All is well at the moment!

 

Thanks for your suggestions anyway.

 

Duncan

pqian
Employee
Employee

Can you try changing the types in the queries editor window? There should already be a change type step, which maybe incorrectly changing the types to text.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.