cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KL718 Frequent Visitor
Frequent Visitor

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

1 REPLY 1
TijnOnline Regular Visitor
Regular Visitor

Re: Load Latest file from Folder keeping Historical files

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. 

Knipsel.PNGPowerQuery 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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)