Row action missing in Query From Folder

I have put my Query From Folder. Everything works fine with all the workbooks that I place dynamically into the folder except that rows on top and at the bottom are only deleted from the first file. Has anybody come across this weird thing?

gvg said:

I have put my Query From Folder. Everything works fine with all the workbooks that I place dynamically into the folder except that rows on top and at the bottom are only deleted from the first file. Has anybody come across this weird thing?

Click to expand...

It looks like you have to do this by using filters with "does not equal" logic. Simply specifying "Remove top rows" or "Remove bottom rows" doesn't seem to work on all worksheets in the folder.

It should do.

Can you share your M?

Did you use "Get Data from File" and then convert to a function or have you used "From Folder"?

I use "From Folder". I need to get data from one sheet from all the files in a folder. Here is my M:

let
Source = Folder.Files("C:\Biudzetas\2017\Padaliniu"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Custom.Name", "Custom.Data"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "Biudzetas2017")),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows", "Custom.Data", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Removed Top Rows" = Table.Skip(#"Expanded Custom.Data",2)
in
#"Removed Top Rows"

Combining multiple Excel files is slightly more involved.

I'm too new to this board to post links but you should be able to find a guide on how to do this by googling "power query combine excel files"

Basically:

Create your Query using one file.
Edit the generated M code to convert it into a function (Called GetData() for example.
Create a second Query using the "From folder" option and navigate to the relevant directory
PQ should show you the files in the folder (apply any filters if necessary)
Add a Custom column, the formula for which will be your function from the previous steps (GetData).
Expand the custom column to show all the data from the workbooks.

Well, it looks much more complicated than using filters. Thanks anyway.