Error: More columns than expected

[jagostinhoCT]

Hi,

Getting this error and cannot seem to find what is wrong.

The query does not seem to have any errors. This only shows when I try to apply the query to the report.

"Apply query changes

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataFormat.Error] There were more columns in the result than expected..'.

[jkball04]

@Greg_Deckler 

I have this issue too.

[Greg_Deckler]

Can you go into Advanced Editor and copy and paste your query? You probably need to remove or update the number of columns that you are expecting from your source?

[jagostinhoCT]

Sure.

Here.

Thanks.

let
    Source = SharePoint.Files("https://chapmantaylorinternational.sharepoint.com/sites/BIMTeam", [ApiVersion = 15]),
    #"Filtered Rows - AuditsLog Folder" = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://chapmantaylorinternational.sharepoint.com/sites/BIMTeam/Power BI Data Sources No Versioning/Audits/")),
    #"Filtered Rows - Revit 2017" = Table.SelectRows(#"Filtered Rows - AuditsLog Folder", each Text.Contains([Folder Path], "Revit 2017")),
    #"Filtered Rows - contains ModelMetrics2-4" = Table.SelectRows(#"Filtered Rows - Revit 2017", each Text.Contains([Name], "Views2-5.txt")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows - contains ModelMetrics2-4", {"Name", "Source.Name"}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each Csv.Document([Content])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content", "Extension", "Date accessed", "Attributes", "Folder Path", "Source.Name"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1"}, {"Custom.Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom.Column1", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Custom.Column1.1", "Custom.Column1.2", "Custom.Column1.3", "Custom.Column1.4", "Custom.Column1.5", "Custom.Column1.6", "Custom.Column1.7", "Custom.Column1.8", "Custom.Column1.9", "Custom.Column1.10", "Custom.Column1.11"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.Column1.1", type text}, {"Custom.Column1.2", type text}, {"Custom.Column1.3", type text}, {"Custom.Column1.4", type text}, {"Custom.Column1.5", type text}, {"Custom.Column1.6", type text}, {"Custom.Column1.7", type text}, {"Custom.Column1.8", type text}, {"Custom.Column1.9", type text}, {"Custom.Column1.10", type text}, {"Custom.Column1.11", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"2/28/2018 2:47 AM", "Date created"}, {"2/28/2018 3:46 AM", "Date modified"}}),
    #"Filtered Rows - SourceFile (Headers)" = Table.SelectRows(#"Renamed Columns", each ([SourceFilePath] <> "SourceFilePath")),
    #"Replaced Value - ct06" = Table.ReplaceValue(#"Filtered Rows - SourceFile (Headers)","\\ct06\data3\Projects\","",Replacer.ReplaceText,{"SourceFilePath"}),
    #"Split Column by Delimiter - JobFolderName" = Table.SplitColumn(#"Replaced Value - ct06", "SourceFilePath", Splitter.SplitTextByEachDelimiter({"\"}, QuoteStyle.Csv, false), {"SourceFilePath.1", "SourceFilePath.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter - JobFolderName",{{"SourceFilePath.1", type text}, {"SourceFilePath.2", type text}}),
    #"Renamed Columns - JobFolderName" = Table.RenameColumns(#"Changed Type1",{{"SourceFilePath.1", "JobFolderName"}}),
    #"Filtered Rows - NOT Record folder" = Table.SelectRows(#"Renamed Columns - JobFolderName", each not Text.Contains([SourceFilePath.2], "Record") or not Text.Contains([SourceFilePath.2], "record")),
    #"Removed Columns - Date Modified" = Table.RemoveColumns(#"Filtered Rows - NOT Record folder",{"Date modified"}),
    #"Replaced Value - No View Template" = Table.ReplaceValue(#"Removed Columns - Date Modified","","NO VIEW TEMPLATE ASSIGNED",Replacer.ReplaceValue,{"ViewTemplate"}),
    #"Split Column by Delimiter - Date" = Table.SplitColumn(Table.TransformColumnTypes(#"Replaced Value - No View Template", {{"Date created", type text}}, "en-GB"), "Date created", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date created.1", "Date created.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter - Date",{{"Date created.1", type date}, {"Date created.2", type time}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Date created.2"})
in
    #"Removed Columns1"

[Greg_Deckler]

@jkball04 @jagostinhoCT Going to take a wild guess and speculate that the problem likely rests with one of your Split Column by Delimiter steps. Probably one of the files or whatever has more delimited values than what you probably saw in the preview data for your query. That's my best guess.

[jkball04]

Through process of elimination I found that the filter rows step caused that error on my data for some reason. Not sure why. But after removing that step while still including all others, it resolved the issue.