multiple files, import only 1st 500 rows

[bobaol]

Hello, I have 4444 files in one folder, all files have two columns. Each file varies by the number of rows, some have 2 rows, some have 244000 rows. How do i use PowerQuery to import all files, but only the 1st 500 rows of each file? any help is appreciated. Thanks in advance.

 

[Matt Allington]

Have a read of my article here

Understanding Power Query Combine

Level: Intermediate Sometime late in 2016 Microsoft deployed a new “Combine” feature in both Power Query for Excel (Get and Transform) and also in Power BI Desktop (Get Data). The new capability makes it easier to combine multiple copies of similar workbooks into a single table without hand...

exceleratorbi.com.au

You should filter the sample query to keep the top 500 rows. I’m not sure what will happen if a file has less than 500 rows; I assume nothing bad will happen (ie no errors)

 

[bobaol]

Hi Matt, thanks for your response, I tried the "Remove Rows" button, but it did not seem to work. I also tried "Keep Rows", but it only kept 500 records in total. Here is the code.
let
Source = Folder.Files("C:\Temp2\6266b"),
#"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"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",499)
in
#"Removed Bottom Rows"

Thank you.

 

[Matt Allington]

When editing the queries, you need to edit the sample query, not the combine query.

 

[bobaol]

yes, that worked. I added instructions for in case I need to refer back to this again. thank you!