Forum Discussion
change daily data source automate
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
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
you need to use above query in advance editor.
GUI way to do it is below. this explains better
- Connect to folder as datasource and go to the first step in the editor. You will get something like this:
- Filter rows by extension (xlsx o xls maybe)
- Filter name of file in the column -> Filter by text -> Starts with...
- Once filtered, order by DESC date
- Keep the first row. (based on your requirement you can change)
- at last expand the content that will have data ( you can remove other columns if you want)
- Connect to folder as datasource and go to the first step in the editor. You will get something like this:
4 Replies
- Ritaf1983
Super User
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
NithinrResolver III
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
you need to use above query in advance editor.
GUI way to do it is below. this explains better
- Connect to folder as datasource and go to the first step in the editor. You will get something like this:
- Filter rows by extension (xlsx o xls maybe)
- Filter name of file in the column -> Filter by text -> Starts with...
- Once filtered, order by DESC date
- Keep the first row. (based on your requirement you can change)
- at last expand the content that will have data ( you can remove other columns if you want)
- Connect to folder as datasource and go to the first step in the editor. You will get something like this:
