I have a file which contains an M Script executed on the connected data. However, the file where the data resists (source) changes every 3 months. Also the filename changes so I have to change the source in de script.
So I was thinking maybe there is something like creating a template containing the script and making the source (in the M Script) dynamic (as a parameter?)
Maybe it can be done using VBA, macros, but I'm looking for a direction.
UPDATE
With all the comments here and some googling I did end up using a combined solution.
I did create a named range where store the filepath and filename.
this named range is used as a source in the query. I didn't need to read CSV files but just one Excel Sheet.
The first few line of the new query:
let
Bron = Excel.CurrentWorkbook(){[Name="DataSourceFile"]}[Content],
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Bronbestand", type text}}),
Bronbestand = #"Type gewijzigd"{0}[Bronbestand],
Content = Excel.Workbook(File.Contents(Bronbestand),null,true),
tbDataManager_Table = Content{[Item="tbDataManager",Kind="Table"]}[Data],
This YouTube video also helpt me
Since @davidebacci was the first one putting me in this direction,I will accept his answer.
The best way is to store a file like a CSV somewhere with the file name and path of the file you will use as input data for your M query. Your M query then reads this CSV file each time it runs to find out where to get the source data. If your source data changes, you just change this one line on the CSV to point to a new directory or file and as long as the structure is the same, the M query will run as normal using updated data.