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,
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.
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.
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.
for %%i in (*.csv) do set /P "header=" < "%%i" & goto continue
for %%i in (*.csv) do (
for /f "usebackq skip=1 delims=" %%j in ("%%i") do echo %%j
) > .\merged\merged_file.CSV