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
• 28,700 points
69 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
• 53,160 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 4 days ago in Others by narikkadan
• 53,160 points
20 views
0 votes
0 answers

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

I want the code to first determine ...READ MORE

18 hours ago in Others by Kithuzzz
• 28,700 points
13 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,940 points
737 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,010 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,439 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,098 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,806 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
• 53,160 points
168 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