Forum Discussion

KL718's avatar
KL718
Frequent Visitor
6 years ago

Load Latest file from Folder keeping Historical files

HI All,

 

I know there are lot of disussion on how to pull latest file from Folder. But I could not find answer to my question. Hence asking for help.

 

My Problem- I have connected Power BI to folder, which has Excel files as below,

2019-10-10 DMD

2019-09-10 DMD

2019-08-10 DMD

.

.

I save new file every month in same Folder. When I refresh Power BI it refresh all the files. WHich is fine but I want to pull only latest file keeping historical as it is. So if I push new file in same folder 2019-11-10 DMD i want to pull only this file keeping historical data/files as it is in Power BI that way I can use the data to build visuals etc.

 

 

Regards

K**bleep**ij

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I would consider appending your files in either power query or excel. Both can be automated. 

     

    For powerquery consider the Append Queries option and simply stack your query results into a new dynamic table. 

    The downside is of course that you will have to do this on a daily basis, as each file is a new query. 

    PowerQuery helper

     

    Another option would be to append the separate excel files into one larger excel file and automate this using a simple command script. 

     

    Put all your files in one folder and create an empty subfolder named "Merged". Add the following code to a text file and rename it to script.cmd. Execute it by double clicking. Now add the newly created file as your sole power bi source.

     

    @ECHO off 
del ".\merged\merged_file.csv"
setlocal
for %%i in (*.csv) do set /P "header=" < "%%i" & goto continue
:continue
(
   echo %header%
   for %%i in (*.csv) do (
      for /f "usebackq skip=1 delims=" %%j in ("%%i") do echo %%j
   )
) > .\merged\merged_file.CSV