Power Query: Split Folder Part 10
4 June 2025
Welcome to our Power Query blog. This week, I begin to simplify the way that I combine the file types into one result.
I have covered the topic of getting files from a folder in several blogs; the latest series was Excel Files from a Folder Fiddle. In this series, I will look at how I may extract files from a folder, where some of the files require different transformations to others. The folder shown below contains expense data for May 2024, but not all formats are the same:

My task is to transform all the data and append into a single output Table.
In this series, I have used the ‘From Folder’ connector to extract data from the folder and transformed and filtered the filenames selected to create a Folder Filtered query. I then took reference copies of this query which I filtered for each file type and used the Combine functionality to bring the data together for each file type.
I appended my data to create a new query Monthly Expense Data and sorted the data in ascending Date order:

Over the series, I have refined the solution using parameters whist keeping this structure, culminating in last week's blog where I looked at how to transform an external parameter so that it would be recognised as a Power Query parameter.

This week, I turn my attention to the current query structure. I can view this in the 'Query Dependencies' dialog which I may access from the View tab.

The diagram is now quite complicated, but when I select Monthly Expense Data, I can see the queries that are used to create it.

In particular, I filter the files based on their extension to create XLSM Files, TEXT Files and CSV Files and then combine them to make Monthly Expense Data.

When I have numerous files of each type to process, this dividing and recombining will add to the time taken to produce the results I need. The query that is split into XLSM Files, TEXT Files and CSV Files is Folder Filtered. If I can run the appropriate combine process for each extension type from this query, I can speed up the solution.

Folder Filtered is used as the reference query for XLSM Files, TEXT Files and CSV Files. However, I cannot remove this query as it is also used by the Power Query helper queries:

To simplify the process and reduced the number of queries, I am going to approach this in steps:
- Take a duplicate copy of XLSM Files which I will call Process Folder
- Modify the 'Invoke Custom Function1' step to call the appropriate combine function based on the file type
- Remove the filter step so it may be tested with all the file types
- Remove Monthly Expense Data, TEXT Files and CSV Files
- Load Process Folder to the Excel workbook.
I will begin by taking a duplicate copy of XLSM Files. I need it to be duplicate copy as I will be amending the existing steps:

This gives me the basis of the new query Process Folder.

Next time, I will amend Process Folder to select the combine method based on the Extension value.
Come back next time for more ways to use Power Query!