Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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
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:
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
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
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!
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
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...
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
@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
Proud to be a Super User! |
|
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