Forum Discussion

joekoshar's avatar
joekoshar
Regular Visitor
7 years ago
Solved

Excel Data Source Changing Daily

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 da...
Need Help
  • BobBI's avatar
    BobBI
    7 years ago

    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

jthomson's avatar
jthomson
Solution Sage
7 years ago

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's avatar
    joekoshar
    Regular Visitor
    7 years ago

    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