Excel Data Source Changing Daily

[joekoshar]

One of the my data sources is an Excel extract that is updated daily. The tail end of my extract includes the date of the extraction as well as a seemingly random number. I am hoping to update the data daily without having to amend the title of the extract and ignore a certain part of the file name. 

File Name: Daily Extract 10.19.18 2343243.xlsx

^^^ the numbers in red change daily

Current Source: Source = Excel.Workbook(File.Contents("C:\Users\kosharj\Desktop\Daily Extract 10.19.18 2345234.xlsx"), null, true),

Is it possible to parse this somehow so it ignores the tail end of the filename?

Example: Source = Excel.Workbook(File.Contents("C:\Users\kosharj\Desktop\Daily Extract" & * & ".xlsx"), null, true),

^^^ ideally, we can use some special character to ignore anything between "Extract" and "xlsx". 

Any thoughts? 

[BobBI · Accepted solution]

Hi,

1. Place all your file in a consistent name format in one Folder

2. Load power BI from Folder. GET DATA = Folder

3. Split File Name to get Datetime value from file name

4. Change data type to DATE or DATE Time for the column containing Datetime value .( you might have to change 10.19.18 to 19/10/18 date format.  if you have multiple file for each day consider time as well and data type to datetime.

5.  if you have to load only latest file --> file date column = Is Latest

Hope this helps,

SS

[joekoshar]

how can I import data from this file now that I have the dynamic file path? I thought about setting it as a parameter, but I am not sure if this is possible. 

[BobBI]

Hi ,

1.Place all your file into One Folder

2. Load Power bi with Get Data = Folder

3. Edit query -> File Name and DateTime part

4. Make Date column as Date or DateTime Data type ( you may to replace 10.19.18 with 19/10/18 format) or change the file name

5. Filter date columns   Data Filter = Is Latest  ( This is alway load latest file ) if you want to load all file skip this step.

screenshots beloe for easy navigation reference.

Hope this helps,

SS

[jthomson]

Try importing as a folder rather than as an Excel file, if the only thing that's going to be in that folder is that one file (or a series of them) it's trivial to just sort by the most recently modified file then expand it out, if there's more things in the folder then you could make a custom column looking at the starts of the file names in the folder and filter out anything that doesn't start "Daily Extract "

[joekoshar]

thanks for the insight! Could you provide a link to any example for the second solution? There are multiple files in the same folder, each containing a different extract name. 

"you could make a custom column looking at the starts of the file names in the folder and filter out anything that doesn't start "Daily Extract "

Joe