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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
smpa01
Super User
Super User

Urgent help req: Debugging OLE DB or ODBC error: [DataFormat.Error]

I am getting this error when I am trying the follwoing PQ on a dataset to be published in the workspace

 

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataFormat.Error] External table is not in the expected format..
'.

 

smpa01_0-1677644919410.png

 

let
    Source = SharePoint.Files("https://xyz.sharepoint.com/teams/Analytics", [ApiVersion = 15]),
    #"Filtered Rows4" = Table.SelectRows(Source, each [Folder Path] = "https://xyz.sharepoint.com/teams/Analytics/Shared Documents/Analysis/ABCAnalysis/"),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows4", "Custom", each let 
a=[Content],
b= if [Extension]=".csv" then 
Table.PromoteHeaders(Csv.Document(a,[Delimiter=",", Columns=19, Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]) else 
let
#"Added Custom" = Table.AddColumn(#"Filtered Rows4", "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Name.1", "Data"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each Text.Contains([Name.1], "Finance", Comparer.OrdinalIgnoreCase)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true])   
in
    #"Promoted Headers"
in b),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"a", "b", ...., "z"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"a", type text}, {"b", type text}, ...... ,{"z", type text}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type",{"a", "z"}),    
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1")
in
    #"Removed Duplicates"

 

It fails on dataflow as well and generates following error message

 

Error: DataSource.NotFound: Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software  visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987. . RootActivityId = someID.Param1 = DataSource.NotFound: Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software  visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.  Request ID: someID.

 

 

@AlexisOlson @ImkeF 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
2 REPLIES 2
ImkeF
Community Champion
Community Champion

Hi @smpa01 ,
Could it be that you have an xls or other filetype still in the list of files?
I would add a filter for filetype csv and xlsx.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF  thanks for looking into it.

Yes the shaepoint folder contains both types (.xlsx and .csv not .xls) and I hoped to handle them both with a ternary operator but it is not working. I wonder why

 

To confirm, filter works.

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.