Trying to combine some Excel files but it only incorporates first 20 rows of each file

[viltxes14]

Hi @PBCommunity 

I´m trying to combine some excel files in a folder that are exactly the same structure, but the power query is only showing the first 20 rows of each file. ¿is it because Power Query has a row limit of some kind or can it be solved?

Thanks a lot!!!

[edhans]

The preview only shows the first few rows, but when importing, all rows are selected based on your requirements. When you combine files, Power Query creates a number of helper queries and one is "Transform Sample File" and anything done in that query will affect all files the same way. Look in that query and see if there is any filter set or something limiting the rows.

otherwise, please post your M code in a Code Box (using the </> button on the toolbar).

[viltxes14]

Thanks a lot! There must be some kind of filter because when loading all data I can´t find the adittional rows. These is the code in the "Transform Sample file" but I can´t notice any filter...

let
Origen = Excel.Workbook(Parámetro2, null, true),
Datos_Sheet = Origen{[Item="Datos",Kind="Sheet"]}[Data],
#"Encabezados promovidos" = Table.PromoteHeaders(Datos_Sheet, [PromoteAllScalars=true])
in
#"Encabezados promovidos"

[edhans]

Ok, that looks good. Can you show me the M code from your main query? The one that you are generally working in.

[viltxes14]

Hi Edhans, this is main query. I can´t also notice any filter associated to when the 20 row limit appears...

let
Origen = Folder.Files("T:\Trading\Trading\Repartos ENAGAS"),
#"Filas filtradas2" = Table.SelectRows(Origen, each ([Folder Path] = "T:\Trading\Trading\Repartos ENAGAS\EDPClientes\EmiVsRep\" or [Folder Path] = "T:\Trading\Trading\Repartos ENAGAS\EDPC\EmiVsRep\") and ([Extension] = ".xlsx")),
#"Archivos ocultos filtrados1" = Table.SelectRows(#"Filas filtradas2", each [Attributes]?[Hidden]? <> true),
#"Archivos ocultos filtrados2" = Table.SelectRows(#"Archivos ocultos filtrados1", each [Attributes]?[Hidden]? <> true),
#"Duplicados quitados" = Table.Distinct(#"Archivos ocultos filtrados2", {"Name"}),
#"Invocar función personalizada1" = Table.AddColumn(#"Duplicados quitados", "Transformar archivo", each #"Transformar archivo"([Content])),
#"Columnas con nombre cambiado1" = Table.RenameColumns(#"Invocar función personalizada1", {"Name", "Source.Name"}),
#"Otras columnas quitadas1" = Table.SelectColumns(#"Columnas con nombre cambiado1", {"Source.Name", "Transformar archivo"}),
#"Columna de tabla expandida1" = Table.ExpandTableColumn(#"Otras columnas quitadas1", "Transformar archivo", Table.ColumnNames(#"Transformar archivo"(#"Archivo de ejemplo"))),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columna de tabla expandida1",{{"Source.Name", type text}, {"Column1", type any}, {"Emisiones vs Repartos Diarios Provisionales", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type text}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type text}, {"Column29", type any}, {"Column30", type any}}),
#"Filas superiores quitadas" = Table.Skip(#"Tipo cambiado",4),
#"Encabezados promovidos" = Table.PromoteHeaders(#"Filas superiores quitadas", [PromoteAllScalars=true]),
#"Columnas quitadas" = Table.RemoveColumns(#"Encabezados promovidos",{"Emisiones_vs_Repartos_Diario_20200409_173844_v1.xlsx"}),
#"Tipo cambiado2" = Table.TransformColumnTypes(#"Columnas quitadas",{{"Fecha de gas", type date}}),
#"Errores quitados" = Table.RemoveRowsWithErrors(#"Tipo cambiado2", {"Fecha de gas"}),
#"Columnas quitadas1" = Table.RemoveColumns(#"Errores quitados",{"Tipo de punto", "Red de entrada", "Red de salida", "Distribuidor", "Revisado", "Emisión a repartir (kWh)", "Todos los comercializadores/CDM", "Column12"}),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas quitadas1",{{"Column13", "Sistema - 3.4"}, {"Column14", "Sistema - No 3.4"}, {"Column15", "Sistema - Tipo 2"},{"Column11", "Sistema - Telemedida"} ,{"Column21", "EDPC - Telemedida"}}),
#"Columnas quitadas2" = Table.RemoveColumns(#"Columnas con nombre cambiado",{"Column16", "Column17", "Column18", "Column19", "0002 <> EDP COMERCIALIZADORA","Column22"}),
#"Columnas con nombre cambiado2" = Table.RenameColumns(#"Columnas quitadas2",{{"Column23", "EDPC - 3.4"}, {"Column24", "EDPC - No 3.4"}, {"Column25", "EDPC - Tipo 2"}}),
#"Columnas quitadas3" = Table.RemoveColumns(#"Columnas con nombre cambiado2",{"Column26", "Column27", "Column28", "Column29", "Column30", "Column31"}),
#"Filas en blanco eliminadas" = Table.SelectRows(#"Columnas quitadas3", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Filas filtradas" = Table.SelectRows(#"Filas en blanco eliminadas", each ([Fecha de gas] <> null)),
#"Se ha anulado la dinamización de las columnas seleccionadas únicamente." = Table.Unpivot(#"Filas filtradas", {"Sistema - Telemedida", "Sistema - 3.4", "Sistema - No 3.4", "Sistema - Tipo 2", "EDPC - Telemedida", "EDPC - 3.4", "EDPC - No 3.4", "EDPC - Tipo 2"}, "Atributo", "Valor"),
#"Dividir columna por delimitador" = Table.SplitColumn(#"Se ha anulado la dinamización de las columnas seleccionadas únicamente.", "Atributo", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Atributo.1", "Atributo.2"}),
#"Columnas con nombre cambiado3" = Table.RenameColumns(#"Dividir columna por delimitador",{{"Atributo.1", "Grupo"}, {"Atributo.2", "Peaje"}}),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Columnas con nombre cambiado3",{{"Valor", Int64.Type}}),
#"Columnas con nombre cambiado4" = Table.RenameColumns(#"Tipo cambiado1",{{"Fecha de gas", "Date"}}),
#"Filas ordenadas" = Table.Sort(#"Columnas con nombre cambiado4",{{"Date", Order.Descending}})
in
#"Filas ordenadas"

[edhans]

At this point @viltxes14 I'd have to see some data. You have a lot of filtering going on. There are 5 Table.SelectRows and while none of them in and of themselves would limit records to 20, the results of the query might depending on the data.

[viltxes14]

Thanks! there are select.rows but they refer to the file finding within the folder... How can I share with you the data?

[edhans]

If the data isn't confidential, you can just share a link to a zip file with 2-3 files in it via OneDrive or Dropbox. If it is confidential, but you trust me, you can use the Private Messaging to share a link and I can play with it.

otherwise you'd need to modify the data so it wasn't confidential.

[viltxes14]

Hi! it is not confidential, here is the link: 

https://edponcloud-my.sharepoint.com/:u:/g/personal/e343432_edpenergia_es/EaC1sEBiqkRBoHXTAEO_s1QBGM...

[edhans · Accepted solution]

There is something wrong with those files. I suspect an external system is exporting them, and it isn't doing so correctly. All of those files are 70kb in size. When I open, save, and close them in Excel, they all grow to 84kb in size. Once I do that, all 200+ rows from the files are imported just fine into Power Query. So there is something that export system is putting in the files that is making PQ think that is the end of the file.

The print range the file is being pre-set with only goes to row 37, even though each file has over 200 rows.

Opening, saving, and closing doesn't change that print range, but it does cleanup whatever else that vendor is doing.

So for now you need to open the files in Desktop excel and force a save, or contact the vendor to fix their export routine.