Power Query: Split Folder Part 11

Welcome to our Power Query blog.  This week, I continue 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.

Last week, I turned my attention to the current query structure:

In particular, I sought to filter the files based upon their extension to create XLSM Files, TEXT Files and CSV Filesand 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 files:

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 can 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.

Last week, I took a duplicate copy of XLSM Files to create Process Folder.

This week, I will amend Process Folder to select the combine method based on the Extension value.  I will change step 'Invoke Custom Function1'  to do this.  Before I change anything, let's look at the table returned by this step:

The current M code is:

I need to change this to check the value in the column Extension.  I can do this in stages.  First, I add a check that the Extension is ".XLSM" which is currently filtered for in the previous step.  I must add this after the each statement to perform the check for each row. 

I need to check the results for each change I make:

This all looks good.  Next, I expand the if statement to consider the other file types.  I have adjusted the format of the code to make it easier to read:

Again, I test the result:

Now comes the critical test.  I need to remove step 'Filtered Rows'.  Removing the filter on XLSM files will test step 'Invoke Custom Function1' for the other types.  When I try to delete the step, I get a warning:

I am happy to continue and I select Delete.  When I check the 'Invoke Custom Function1' step, it is now working with the other file types:

I check the full query and the results look promising:

Next time, I'll tidy up my solution and change the data to test it.

Come back next time for more ways to use Power Query!