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

[smpa01]

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..
'.

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 

[ImkeF]

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.

[smpa01]

@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.