power query - loading files from different file paths

hood2media Try using

let
// Define parameters
YearFrom = 2016,
YearTo = 2018,
Station = "JFK",

// Generate list of years
Years = List.Numbers(YearFrom, YearTo - YearFrom + 1),

// Generate list of file paths
FilePaths = List.Transform(Years, each "C:\Users\nik\H2M\H2M - us.data\data\" & Text.From(_) & "\" & Station),

// Function to get files from a folder
GetFilesFromFolder = (folderPath as text) =>
let
Source = Folder.Files(folderPath),
FilteredFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx"))
in
FilteredFiles,

// Get all files from the generated file paths
AllFiles = List.Combine(List.Transform(FilePaths, each GetFilesFromFolder(_))),

// Load and combine the Excel files
CombineExcelFiles = Table.Combine(
List.Transform(AllFiles[Content], each Excel.Workbook(_, null, true)[Data])
)
in
CombineExcelFiles

An alternative solution created through the UI entirely:

let
    // Get all file details from the root folder
    Source = Folder.Files("C:\Users\keess\OneDrive\Documents\- Tools en Programmeren\Power Query\Fabric Community\power-query-loading-files-from-different-file-paths"),
    // Add a column for Station
    #"Add Station" = Table.AddColumn(Source, "Station", each Text.BetweenDelimiters([Folder Path], "\", "\", {1, RelativePosition.FromEnd}, 0), type text),
    // Add a column for the Year
    #"Add Year" = Table.AddColumn(#"Add Station", "Year", each Text.BetweenDelimiters([Folder Path], "\", "\", {2, RelativePosition.FromEnd}, 0), type text),
    // Set the data type of the year column to "Whole Number"
    #"Changed Type" = Table.TransformColumnTypes(#"Add Year",{{"Year", Int64.Type}}),
    // Filter the .xlsx files based on the parameters given
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Year] >= YearFrom and [Year] <= YearTo and [Station] = Station and [Extension] = ".xlsx")
in
    #"Filtered Rows"

Essentially:

• This gets all files in the root folder and it's subdirectories
• Adds 2 additional columns for the Station and the Year
• Filters Station, Year from the Parameters and file type (extension) .xlsx 

Ending up with the list of Excell files like this:

Further processing can be done in several ways, but I assume you know what you want to do...

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel