Dynamic source file name change in PowerQuery

albertan

Board Regular
Joined
Nov 16, 2014
Messages
51
Office Version
  1. 365
Hello, I appreciate some advice here.
I have a connection with another csv file but its sheet's name changes every day after it gets replaced with a new extracted file. The sheet name is in this format Data20210721 and it keeps changing every day. So next day it will be Data20210722 after a new extract was done (same folder location) and the old extract will be removed.
Is there a way for me to make it dynamic to a source sheet (the file name doesn't change as well as location) which keeps on changing?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,490
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Nothing had been posted. Same request as here.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
960
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Simply read from the folder, select latest file via the filter. Since it is a date/time column it supports time intelligence. No need for formula and filepath constructions.
@Alex Blakenburg , thanks for the mention :)
 

albertan

Board Regular
Joined
Nov 16, 2014
Messages
51
Office Version
  1. 365
Simply read from the folder, select latest file via the filter. Since it is a date/time column it supports time intelligence. No need for formula and filepath constructions.
@Alex Blakenburg , thanks for the mention :)
I think you mis understood, because the file keeps changing every month when it comes to sheet name. Anyway, I got problem solved. Thanks
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
960
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Good you have a solution 👍.

To be frank, I think you misunderstood...

It's a .csv, so it does not have a sheet name, only a filename. You construct that filename based on today's date, with a formula. I suggest to read from folder, then select the last file. Regardless of its' name it will get selected.
1627490504053.png

1627490604214.png

1627490681428.png


Where I started with this list
1627490737507.png


In your example there is going to be only 1 file, since the old ones get removed automatically.
Obviously you choose what you think is best.
 

Forum statistics

Threads
1,141,818
Messages
5,708,768
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top