Selected path to be used as Source Parameter to update Power Query's source path

[albertan]

I have a VBA code that can replace the source path in a Power Query and refresh it. However, it takes time.
This source path is selected from a list of folders that are populated daily in a folder.
My report compares the data today to data from whichever file I pick from that folder. I.e. if I pick a folder from Dec 31, 2022, the table will show comparison of data for Year To Date.

Is there a way for me to use Parameter that could be dynamic, i.e. for example if I bring a Power Query table with all listed source path locations (in one column) and create another column with a Date (of each file), is it possible to have this path to be automatically updated when I select a slicer for Date and then it can update the path of the file which is associated with that Date?

Here's what I got from from web resources, but I couldn't get it worked out:

To use a drop-down slicer to select the value that will be used as a parameter in Power BI, follow these steps:

Open your Power BI report and go to the page that contains the table or visualization you want to filter using the drop-down slicer.



Add a slicer visual to the report page by selecting "Slicer" from the "Visualizations" pane.



Select the column that you want to use as the parameter from the "Fields" pane and drag it onto the "Values" field of the new slicer visual. This will create a drop-down list of values that can be selected to filter the table or visualization.



Customize the slicer visual to suit your needs by adjusting the formatting, layout, or other properties as required.



Test the drop-down slicer by selecting different values from the list and observing how the table or visualization is filtered accordingly.



Use the selected value from the drop-down slicer as the parameter in your calculations or other expressions. For example, you could create a measure that uses the SELECTEDVALUE function to return the selected value from the slicer:



Selected Value = SELECTEDVALUE('Slicer'[Column])



Use the selected value from the slicer in other expressions or calculations by referring to the measure you created in the previous step. For example, you could use the selected value to filter a table using the FILTER function:



Filtered Table = FILTER('Data Table', 'Data Table'[Column] = [Selected Value])

 

[RoySimpson]

To dynamically update the source path in Power Query based on a selected date using a slicer:

1. Create a parameter table with the source paths and dates.
2. Load the parameter table into Power BI as a separate query.
3. Add a slicer visual connected to the date column.
4. Modify the Power Query code to reference the selected date from the slicer and update the source path.
5. Refresh the data to reflect the updated source path based on the selected date.

Example code:

vbaCopy code
let
selectedDate = SELECTEDVALUE('Slicer'[Date]),
selectedPath = Table.SelectRows(ParameterTable, each [Date] = selectedDate)[Source Path]{0}
// Use selectedPath in your Power Query transformations and load the data
in
// Your Power Query code

Ensure to replace 'Slicer'[Date] with your slicer column name and ParameterTable with your parameter table name.

This allows the source path to be automatically updated based on the selected date from the slicer for dynamic comparisons.