let
Source = Excel.Workbook(Parameter1, null, true),
sheet2 = Source{[Name="sheet1"]}[Data],
#"Removed Top Rows" = Table.Skip(sheet2,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
#"Promoted Headers"
let
sourcePath = Excel.CurrentWorkbook(){[Name="DataSourceTable"]}[Content][DataSource]{0},
reportsPath = Excel.CurrentWorkbook(){[Name="DataSourceTable"]}[Content][DataSource]{1},
fullPath = Text.Combine({sourcePath, reportsPath}, ""),
Source = Folder.Files(fullPath),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"}),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"
I couldn't replicate your issue with files of my own. Can you upload three or four of your files (remove anything confidential but leave their structure as is)? Make sure some have more than or equal to 255 rows and some have less.
I'll set up the files and post shortly. It also happens when I start from scratch and just combine them without any transformations and just load them. I am wondering if there is some kind of configuration setting.
I overwrote the fields in 2 reports with junk, saved the files, and the import worked perfectly. Testing right now, if I open the original files and save them without changes and close them it also then works, but using the original files returns to the behavior I described. Unfortunately, I can't post the original files though. The files I am using are downloaded from PeopleSoft as .xls files so the format is older. I am not sure if that has anything to do with it. I wonder if there is a quick way to pre-convert all the files to make it work.
