Power Query: Split Folder Part 5

Welcome to our Power Query blog.  This week, I begin to refine the query I have created by changing and adding data.

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 Part 1, I used the ‘From Folder’ connector to extract data from the folder, and transformed and filtered the data to ensure I only had the expense data. 

I took three [3] reference copies of Folder_Filtered, one for each file type:

In Part 2, I transformed the data in XLSM Files and TXT Files.  

In Part 3, I transformed the CSV Files. Intially, there was only one CSV file, but I assumed that there could be more when I refreshed the data, so I used the ‘Combine Files’ approach.  

I appended my data to create a new query Monthly Expense Data, and sorted the data in ascending Date order:

Last time, I tested the process by changing and adding data.  Now I have a working process, I would like to refine it.  When I created the Folders_Filtered query, I filtered based on the strings that I expected to be in the data.

In the ‘Filter Rows’ dialog, I have the option of using parameters:

I choose to create a ‘New Parameter’ from here:

When I click OK, the parameter is selected:

I also create a parameter for the TXT files:

When I click OK, the query looks the same, but I am now using parameters for this step:

The parameters I have created are internal to Power Query, and may be amended from the ‘Manage Parameters’ dropdown:

The value may also be changed in the Queries pane:

Whilst this is better than hard coding, this is only suitable for users who are comfortable using Power Query.  Next time, I will look at getting this information from the Excel sheet instead.

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