power query - loading files from different file paths

[hood2media]

hi,
i am doing analysis on airport-flight operations. i have monthly records (.xlsx format) for different years (from 2015-2025).

i have created following parameters-

   'YearFrom'
   'YearTo'
   'Station'

such that i may select records from '2016' (YearFrom) thru 2018 (YearTo) and for 'JFK' (Station), as an example.

the root-base file path is-

"C:\Users\nik\H2M\H2M - us.data\data\".

the data files are stored / may b found in subfolders according to the selected 'YearFrom', 'YearTo' and 'Station' parameters.
for example-

   "C:\Users\nik\H2M\H2M - us.data\data\2016\JFK"
   "C:\Users\nik\H2M\H2M - us.data\data\2017\JFK"
   "C:\Users\nik\H2M\H2M - us.data\data\2018\JFK"
 
(based on-
   'YearFrom' is '2016', 
   'YearTo' is '2018'
   'Station' is 'JFK') 

or 

   "C:\Users\nik\H2M\H2M - us.data\data\2020\LAX"

(based on-
   'YearFrom' is '2020', 
   'YearTo' is '2020'
   'Station' is 'LAX')
 
the monthly .xlsx files are named like-

2015-01.xlsx
2015-02.xlsx
...
2015-12.xlsx

(for 2015 monthly records).

all the .xlsx files that comes under the filter criteria will then be combined as 1 for further analysis / reports creation.

i'd appreciate any help to get  the right power query/m-language formula to meet that filter & file combine requirement.

thanks, -nik

[hood2media · Accepted solution]

thanks again @PwerQueryKees.

i also noticed that the table names are auto-generated for different years when i created them individually separately for each year. each file only has 1 table.

i'll try your solution as soon as i get on my computer later & revert as necessary.

krgds, -nik

[v-sshirivolu · Accepted solution]

Hi @hood2media , 
Thank you for reaching out to Microsoft fabric community.
Try these steps

Create Parameters

Navigate to Home > Manage Parameters > New Parameter and set up the following:

YearFrom – type: Number (e.g., 2016)

YearTo – type: Number (e.g., 2018)

Station – type: Text (e.g., JFK)
You can later link these to slicers or keep the default values.

Use this M code 

let
// PARAMETERS
StartYear = YearFrom,
EndYear = YearTo,
Station = Station,
BasePath = "C:\Users\nik\H2M\H2M - us.data\data\",
// Create list of years (2016, 2017, 2018)
YearList = List.Numbers(StartYear, EndYear - StartYear + 1),

// Build folder paths
FolderPaths = List.Transform(YearList, each BasePath & Text.From(_) & "\" & Station),
// Function to get .xlsx files from a folder
GetFilesFromFolder = (folderPath as text) =>
let
Files = Folder.Files(folderPath),
ExcelFiles = Table.SelectRows(Files, each Text.EndsWith([Extension], ".xlsx"))
in
ExcelFiles,
// Loop through folders and collect Excel file metadata
AllFilesTables = List.Transform(FolderPaths, each try GetFilesFromFolder(_) otherwise null),
NonNullTables = List.RemoveNulls(AllFilesTables),
AllFiles = Table.Combine(NonNullTables),
// Load actual Excel content from each file
AddContent = Table.AddColumn(AllFiles, "Data", each Excel.Workbook(File.Contents([Folder Path] & [Name]), true)),
// Expand the first table in the Excel file
Expanded = Table.ExpandTableColumn(AddContent, "Data", {"Data"}, {"Data"}),
// Combine all data into one table
FinalCombined = Table.Combine(Expanded[Data])
in
FinalCombined

Apply and Load

Select Close & Apply (top left corner) to load the query to the Power BI Data Model.

Create Table Visual

In the Fields pane, expand CombinedData and drag in the following fields:

FlightID

Departure

Arrival

Status

This will show the combined flight data based on your selected parameters.

Please find the below attached .pbix file for your reference.

Regards,
Sreeteja

[PwerQueryKees]

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

[hood2media]

hi @PwerQueryKees,
thanks for your response too.

i managed to get an output as per your screenshot.

when i tried to combine the files, i, however, got an output for just 1 month instead of all the months based on the selected years. 

kindly assist on how to resolve this.
krgds, -nik

[PwerQueryKees]

In the column heading of Content, you see a little icon on the right. Wit arrows pointing down.

What happens when you click it?

It will probably not give you the result you want, but it should give you all data. Share the result!

[hood2media]

hi again @PwerQueryKees,

the zipped file of the result after clicking the merge files icon can be found in the following weblink-

https://drive.google.com/file/d/1i2N04Ol_o0-8FvVIis4b1J1hDmxw-jP_/view?usp=sharing

krgds, -nik

[PwerQueryKees]

The problem is that the individual files store their data in a table with a different name.

Can't test because I don't have your files.

Try changing the query "Transform Sample File" into:

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
    Data_Table = #"Filtered Rows"{0}[Data]
in
    Data_Table

If your excel files have multiple tables with the data, you have to tweak the "Transform Sample File" query using different sample files (by changing Parameter1) until it produces your desired results for all files...

[hood2media · Accepted solution]

thanks again @PwerQueryKees.

i also noticed that the table names are auto-generated for different years when i created them individually separately for each year. each file only has 1 table.

i'll try your solution as soon as i get on my computer later & revert as necessary.

krgds, -nik

[bhanu_gautam]

@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

[hood2media]

hi @bhanu_gautam,
thanks for responding.

it seems that all is fine till the function (defined as 'FilePaths') to generate list of file paths .

the process stops at the function (defined as 'GetFilesFromFolder') to get files from the folder(s). it invokes a request to enter parameter for folderPath which i believe should come from the previous function called  'FilePaths'.

how do i proceed?
kind rgds, -nik