Forum Discussion
power query - loading files from different file paths
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 ParametersNavigate 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 codelet
// 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
FinalCombinedApply 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:
- 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
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, -nikThe 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_TableIf 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...