Excel Power Query import same file but with different month name

Question

I have to automate the import of reference data each month, but the Excel file has a different name.

January 2022 Monthly Data File.xlsx February Monthly Data File 2022.xlsx

Please direct me to the appropriate route.

Answer 1

Use the name manager function in Excel to choose a cell and assign it a range name, such as NameVariable.

In that designated cell, enter your file path and filename as C:tempMonthly Data File January 2022.xlsx. When the filename later changes, modify the range's content as necessary.

In the home... advanced editor... create a formula that corresponds to the range name, similar to this: Load one file into PowerQuery.

MVar = Excel.CurrentWorkbook(){[Name="NameVariable"]}[Content]{0}[Column1],

And change any hard-coded references to the filename to use MVar instead:

As an example, change

let Source = Excel.Workbook(File.Contents("C:\temp\Monthly Data File January 2022.xlsx"), null, true),

To be:

let MVar = Excel.CurrentWorkbook(){[Name="NameVariable"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(MVar), null, true),