Excel Power Query import same file but with different month name

0 votes
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.
Dec 12, 2022 in Others by Kithuzzz
• 35,840 points
121 views

1 answer to this question.

0 votes

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),
answered Dec 13, 2022 by narikkadan
• 61,360 points

Related Questions In Others

0 votes
1 answer

Power Query M Lookup on Same table with Filtering

In power query, merge the table on ...READ MORE

answered Feb 2 in Others by narikkadan
• 61,360 points
122 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6 in Others by narikkadan
• 61,360 points
81 views
0 votes
1 answer
0 votes
1 answer

How to import excel file in Oracle SQL live

Hello, there are a few steps You'll ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,970 points
906 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,166 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
7,610 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,146 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
4,953 views
0 votes
1 answer
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 61,360 points
364 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP