Power Query: Split Folder Part 10

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:

1. Take a duplicate copy of XLSM Files which I will call Process Folder
2. Modify the 'Invoke Custom Function1' step to call the appropriate combine function based on the file type
3. Remove the filter step so it may be tested with all the file types
4. Remove Monthly Expense Data, TEXT Files and CSV Files
5. 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!