Power Query: Split Folder Part 9
28 May 2025
Welcome to our Power Query blog. This week, I look at how I can convert a parameter extracted from the workbook into a Power Query parameter.
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. In Part 7, 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
= Table.SelectRows(#"Uppercased Text", each Text.Contains([Name], XLSM_String) or Text.Contains([Name], TXT_String))
to
= Table.SelectRows(#"Uppercased Text", each Text.Contains([Name], P_XL_XLSM) or Text.Contains([Name], P_XL_TXT))
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 can select them? Yes there is, though it does involve some M code.
Last time, I compared the existing M code in the Advanced Editor for XLSM_String and P_XL_XLSM, and found that XLSM_String had been defined as a Power Query parameter by assigning metadata values.
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. Currently, the M code for P_XL_XLSM is:
let
Source = Excel.CurrentWorkbook(){[Name="P_XL_XLSM"]}[Content],
Column1 = Source{0}[Column1]
in
Column1
I need to take the final value and assign metadata values so that it has the same properties as a Power Query parameter, using code similar to the M code for XLSM_String, which is:
"MARY" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
My first attempt was to add a step:
Convert_to_Parameter = Column1 [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
This triggered a syntax error:
I tried skipping the step name, but this is not allowed in a query:
Next, I decided to mimic the Power Query parameter by entering a new Blank Query:
When I selected Done, the results look promising. I can't see the value of the parameter, but it does look like a parameter!
I rename Query5 to Param_XL_XLSM.
The value is not shown here either. I try using it in the Folder Filtered query:
It works when I enter it in the M code, so I try using the cog next to the 'Filtered Rows' step to see if I may select it from there:
It is recognised and I may select it in the dialog. Note that the second parameter P_XL_TXT is not shown as this is still a query rather than a Power Query parameter.
Param_XL_XLSM will be updated whenever the user changes the value in the workbook since it uses the P_XL_XLSM parameter. The only issue is that the value of Param_XL_XLSM is not displayed since it is using the result of a query as the value.
Come back next time for more ways to use Power Query!
