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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Sai_Akash
New Member

OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number...

 

 I'm facing an issue when importing multiple files into Power Query in Power BI. I've tried different file formats (CSV, Excel, TXT) and removed unnecessary columns, but the problem persists. Even though I’ve set the correct data types for each column, some files either fail to load. This inconsistency is happening across different files, and I’m unable to automate the process properly. Any suggestions or solutions to ensure consistent file imports with the correct transformations would be greatly appreciated.Seeking Help to resolve the issuseSeeking Help to resolve the issuse

7 REPLIES 7
v-sgandrathi
Community Support
Community Support

Hi @Sai_Akash,
Hope you are doing well.

Thank you for being a part of the Microsoft Fabric Community Forum!

 

To ensure consistent file imports and resolve conversion errors, please follow these steps:

  • Clean Data Before Import – Open each file and ensure numeric columns are correctly formatted, removing any non-numeric characters.
  • Adjust Power Query Steps – In Applied Steps, review the "Changed Type" step. Remove it and manually set data types after cleaning.
  • Convert to Text First – If errors persist, convert numeric columns to text first to identify issues, then clean and convert them back to numbers.

For your Reference I,m providing the sloved Link you might find useful:
Solved: Error returned: 'OLE DB or ODBC error: [DataFormat... - Microsoft Fabric Community

 

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Regards,
Sahasra.

Hi @Sai_Akash,

 

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

 

Thanks for reaching out to the Microsoft Fabric Community forum.

Hi @Sai_Akash,

 

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.

Thank you for your cooperation. Have a great day.

Hi @Sai_Akash,

 

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and select "Yes" if it was helpful.

If you need any further assistance, feel free to reach out.

 

Thank you for being a valued member of the Microsoft Fabric Community Forum!

BA_Pete
Super User
Super User

Hi @Sai_Akash ,

 

Difficult to say exactly without more information, but the first thing I'd do is go to the query that is failing with this error, find any columns that are converted to a number type (Decimal or Whole Number/Integer) during the transformation, select them one by one and go to the Home tab > Keep Rows (dropdown) > Keep Errors. This should hopefully help you identify rows where a conversion error has occurred.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete,

Thank you for your suggestion! I followed your advice and checked the query, focusing on columns that are being converted to a number type during the transformation. However, even when I specifically applied this to the id column, I’m still encountering the same issue.

I went to the Home tab, selected the “Keep Errors” option, and despite this, I’m unable to isolate the root cause of the conversion error for this column.

Would you be able to provide further guidance or suggest additional steps I can take to troubleshoot this? If possible, I’d like to discuss this in a DM personally to explore potential solutions more efficiently.

Thanks again,
Sai AkashStill Facing Same Issue.png

 

 

Hi Sai,

 

The error you posted implies that you still have a row with a non-numerical value in it.

Try adding a custom column like this, which you can then filter on to identify offending rows:

if try Value.Is(Number.From([ID]), type number) otherwise false then "Numeric" else [ID]

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors