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?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Row action missing in Query From Folder
- Thread starter gvg
- Start date
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?
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.
Last edited:
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"
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.
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.
Last edited: