change daily data source automate

[Biprajit]

Hi,

I am new to this forum; i have searched for solution to my problem but could not find any. 

I have a PowerBi report with multiple excel data files as source. The data in these files is refreshed daily and with it the names also change. The names have a similar format, for e.g - open orders 26.12; only the date part changes for this files. Is it possible to change the M code in source in power query to automatically refresh with today(current) date files, when i refresh the files in folder?

Any help would be much appreciated, Thank you

[Nithinr]

@Biprajit   This post should help Solved: Re: Importing excel data by Filename partial match... - Microsoft Fabric Community

[Biprajit]

Hi Nithinr, 

Thank you for the reply; however when i am putting the below formula(in source formula bar in power query) according to the solution in original post, i get "Token Eof expected" error

=Folder.Files("C:\Planning\Daily reports\Pending orders Powerbi\"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Name], "Source Open Order")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Date modified", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)
in
#"Kept First Rows"

Please explain

[Nithinr · Accepted solution]

you need to use above query in advance editor. 

GUI way to do it is   below. this explains better

1. Connect to folder as datasource and go to the first step in the editor. You will get something like this:
2. Filter rows by extension (xlsx o xls maybe)
3. Filter name of file in the column -> Filter by text -> Starts with...
4. Once filtered, order by DESC date
5. Keep the first row. (based on your requirement you can change)
6. at last expand the content that will have data ( you can remove other columns if you want)

[Ritaf1983]

Hi @Biprajit 
You can have a folder as a data source and then rake only the last file.
For more information please refer :

https://www.youtube.com/watch?v=pHDRKNjuwmU

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly