Power Query: Split Folder Part 8

Welcome to our Power Query blog.  This week, I examine the difference between the types of parameters I have been using.

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:

In Part 4, I tested the process by changing and adding data.

In Part 5,  I refined the process by using internal Power Query parameters in the Folder Filtered query.

The values of the parameters were "EXPENSE" and "NDL".

When I clicked OK, the query looked the same, but I was using parameters for the 'Filtered Rows' step:

In Part 6, I investigated what happened if there wasn't a file selected of each type and I changed the queries XLSM Files, TXT Files and CSV Files to always have at least one row and changed Monthly Expense Data so that it would ignore the extra column and null row that the solution could produce.

Whilst having internal Power Query parameters is better than hard coding, this is only suitable for users who are comfortable using Power Query.  Last time, I changed the process to use cells from the Excel workbook for the parameters.

Note that they do not look like the parameters I created in Part 5.

If I go back to the Folder Filtered query and use the cog next to the 'Filtered Rows' step, I am not able to select the parameters from the Excel worksheet:

To use these filters, I must adjust the M code in the step instead.  I change it from

to

Note that the IntelliSense does recognise the new parameters:

The output remains the same:

This made the process easier for the users to change, but it does leave several questions: why can't I pick the parameters I have created in the 'Filtered Rows' step?  Is there a way to make these parameters look like Power Query parameters so we may select them?  Yes there is, though it does involve some M code.

Let's start by comparing the existing M code in the Advanced Editor for XLSM_String and P_XL_XLSM.

The M code for XLSM_String is:

whereas the M code for P_XL_XLSM is

The M code for P_XL_XLSM is easy to follow:

• the Source step extracts the cell identified by the Named Range P_XL_XLSM
• the 'Column1' step then drills down into the value in the cell.

The M code for XLSM_String may seem less familiar.  This is defining metadata for the parameter.  In a new Blank Query, I may query the existing metadata for XLSM_String:

The M code I have used is:

The syntax for Value.Metadata is:

The function returns a record containing the metadata of value.  If I try to do this for P_XL_XLSM I get a different result:

This means that P_XL_XLSM is a query and the only metadata available relates to whether it may be folded.  I may view this metadata by clicking on the word "Record" in the previous image.

For more on query folding, please see Power Query: Folding Table.

If I want P_XL_XLSM to be treated like a Power Query parameter, I need to assign metadata so that it has the properties of a parameter.  As I will show next time, this is possible, although it does not get treated exactly like a parameter created from the User Interface.

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