Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a excel sheet named as ProductSales_YYYYMMDD.xls. and placed in a folder location.
Everyday in the same folder a new excel sheet get placed with current date. The new files will be in same column structure.
Now how to handle this scenario in Power BI for accepting new files every day with different name and same structure.
Solved! Go to Solution.
Hi, @Anonymous
Yes, you can use 'Date.ToText( Date.From(DateTime.LocalNow()),"YYYYMMDD")' to select files dynamically.
Try like this in PQ :
let
Source = Folder.Files(root&"FolderName"),
temp = "ProductSales_"&Date.ToText(Date.AddDays( Date.From(DateTime.LocalNow()),1),"YYYYMMDD"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name],temp)),
custom = Table.Max(#"Filtered Rows","Name")[Content],
#"Imported Excel" = Excel.Workbook(custom),
Table1_Table = #"Imported Excel"{[Item="Table1",Kind="Table"]}[Data]
in
Table1_Table
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Yes, you can use 'Date.ToText( Date.From(DateTime.LocalNow()),"YYYYMMDD")' to select files dynamically.
Try like this in PQ :
let
Source = Folder.Files(root&"FolderName"),
temp = "ProductSales_"&Date.ToText(Date.AddDays( Date.From(DateTime.LocalNow()),1),"YYYYMMDD"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name],temp)),
custom = Table.Max(#"Filtered Rows","Name")[Content],
#"Imported Excel" = Excel.Workbook(custom),
Table1_Table = #"Imported Excel"{[Item="Table1",Kind="Table"]}[Data]
in
Table1_Table
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
As @Pragati11 mentione,d you wouldneed to go for folder approch.
You M query would look like something like this:
Source = Folder.Files(root&"FolderName"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "ProductSales_"))
As the structuring and columns of all the files are same, you can go for merging all the files as per your requirement
Hi @Anonymous ,
You can use folder approach in Power BI as a solution for this. Refer the following article:
https://powerbi.tips/2016/06/loading-data-from-folder/
Thanks,
Pragati