Excel Forum

Power Query: Combining Excel files only imports first 255 rows

Benjamin Altman

Active Member

Members

Forum Posts: 4

Member Since:
March 20, 2024

Offline

March 26, 2024 - 9:46 am

I am combining reports located in a folder that all have the same structure. I use Get Data > From File > From Folder in Excel 365. I thought it was working perfectly but noticed that only the first 255 rows of each file are considered and if the workbook has less than 255 rows, it will pad the result with blank rows so each combined file has 255 rows in the sheet it combines them to. I need it add all the rows in each workbook without padding rows or stopping at 255.

There are queries, the part that transforms each file (Transform Sample File) and the part that acts on the final result (Other Queries [1]).

Transform Sample File:

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"

Other Queries [1]

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 added in the "Removed Blank Rows" line before I realized it was artificially padding workbooks that were less than 255 rows. I also found a post here that mentions adding "[InferSheetDimensions = true]" to make Power Query "read the sheet dimension, not one which is metadata", but I couldn't work out how to add it without getting errors.

Does anyone know what I can do to just combine all the rows without leaving out any or causing Power Query to add blank rows?

Thanks!

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Moderators

Forum Posts: 1032

Member Since:
January 31, 2022

Online

March 26, 2024 - 11:20 pm

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.

Benjamin Altman

Active Member

Members

Forum Posts: 4

Member Since:
March 20, 2024

Offline

March 27, 2024 - 7:12 am

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.

Benjamin Altman

Active Member

Members

Forum Posts: 4

Member Since:
March 20, 2024

Offline

March 27, 2024 - 7:47 am

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.