Applying Query Changes Issue OLE DB or ODBC error: [DataFormat.Error] File Contains corrupted data..

[Anonymous]

Greetings,

I am having an issue with my file where I am trying to update and apply changes that @Anonymous  helped put together for me and I am getting an error message that I can't seem to get around. I tried created another .pbix using the same data but I keep getting this error. The data looks fine in Power Query.

Here is what it looks like.

let
Source = Folder.Files("\\swgas.com\swgshr\lvo\snvshr\GOSS\Employees\Salinas\Testing"),
#"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",".xlsx","",Replacer.ReplaceText,{"Name"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Name.3", "Year"}, {"Name.2", "Month"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Month"},DimMonthNum,{"Month"},"DimMonthNum",JoinKind.LeftOuter),
#"Expanded DimMonthNum" = Table.ExpandTableColumn(#"Merged Queries", "DimMonthNum", {"Month Number"}, {"Month Number"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded DimMonthNum",{{"Year", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Name.1"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns1", "Transform File from Testing", each #"Transform File from Testing"([Content])),
#"Removed Columns2" = Table.RemoveColumns(#"Invoked Custom Function",{"Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns2", "Month of Subtable", each Table.AddColumn(
[Transform File from Testing],
"Month Number",
each
Date.Month( [RRC] )
)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Year of Subtable", each Table.AddColumn(
[Month of Subtable],
"Year",
each
Date.Year( [RRC])
)),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom1",{"Transform File from Testing", "Month of Subtable"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns3",{"Year of Subtable", "Month", "Month Number", "Year"}),
#"Removed Columns4" = Table.RemoveColumns(#"Reordered Columns",{"Month"}),
#"Expanded Year of Subtable" = Table.ExpandTableColumn(#"Removed Columns4", "Year of Subtable", {"RRC", "COST_ELEMENT", "AMOUNT", "Employee Number", "Month Number", "Year"}, {"RRC", "COST_ELEMENT", "AMOUNT", "Employee Number", "Month Number.1", "Year.1"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Year of Subtable", "Keep or Remove", each if
[Month Number.1] = [Month Number]
and
[Year.1] = [Year]
then
"Keep"
else
"Remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Keep or Remove] = "Keep"))
in
#"Filtered Rows"

What do you think the issue might be? Thoughts/Experiences? 

Thank you,

Michael

[Anonymous · Accepted solution]

Okay So I was off for the weekend so that is why it took so long. The issue was that the file wasn't hiding hidden files. I'll have a screenshot kind of explaining what I mean. Even when I added the step in, It would glitch and unhide it in the next step. So I had to delete the next step and reinput it in Power Query.

[v-juanli-msft]

Hi @Anonymous 

It seems the data format issue in your original data.

you connect to a folder as a data source, could you show an example of the source before any transformation and what the final table should be?

Best Regards

Maggie

[Anonymous]

@v-juanli-msft 

Hi Maggie,

Well the issue only appears once I apply Power Query. It loads fine Otherwise