Use the same Power Query script (M Script) on different files

Question

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 path and filename could be stored in the Excel file and used as a parameter in the M script. — Storax
– Storax, Commented Aug 14 at 9:54

Máté Juhász · Accepted Answer · 2025-08-14 10:57:35Z

3

If you store your files in the same folder and you've a way for filtering them (only those files are .csv in the folder, naming pattern ...) then you can pick the latest one and use in your script, doesn't even need to take care of any parameters.

let
    Source = Folder.Files("<.. path ..>"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
    myFile = #"Sorted Rows"{1}[Content],
    #"Imported CSV" = Csv.Document(myFile,[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])
in
    #"Imported CSV"

answered Aug 14 at 10:57

Máté Juhász

2,3552 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

davidebacci · Accepted Answer · 2025-08-14 07:55:16Z

1

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.

answered Aug 14 at 7:55

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

1 Comment

Stephan Aug 14 at 8:39

That is a good suggestion indeed. I will give it a go and post the solution.

Storax · Accepted Answer · 2025-08-14 10:13:36Z

I would recommend integrating a filename from an Excel cell into your Power Query. Here's how you can do that with a flexible approach:

First, in your Excel worksheet, create a named range (for instance, call it fullFilename) for cell A1, and then put the complete filename into that cell

Next, within Power Query, create a new query (you can also name this fullFilename for consistency). This query will simply pull the filename from your named range:

let
    Source = Excel.CurrentWorkbook(){[Name="fullFilename"]}[Content],
    fullFilename = Source{0}[Column1]
in
    fullFilename

Finally, in your main Power Query where you're loading data, you can simply reference the fullFilename query you just created to dynamically get the filename from your Excel sheet. This allows you to easily change the filename directly in Excel without needing to edit the Power Query code itse

let
    Source = Csv.Document(File.Contents(fullFilename),[Delimiter=";", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source

This setup makes your Power Query more adaptable, as the filename can be adjusted right from your Excel sheet

Collectives™ on Stack Overflow

Use the same Power Query script (M Script) on different files

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related