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
• 38,010 points
814 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
• 63,720 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, 2023 in Others by narikkadan
• 63,720 points
1,213 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, 2023 in Others by narikkadan
• 63,720 points
717 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
• 23,260 points
1,893 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
4,016 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
8,480 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,523 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
5,845 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
• 63,720 points
1,358 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