Dynamic Excel name handling in Power BI

[Anonymous]

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.

[v-janeyg-msft · Accepted 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.

[PC2790]

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

[Pragati11]

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