Automatically update data according to the date in the file name | Author: Janey Guo

[V-pazhen-msft]

Scenario:  

Each day or Each month, an excel file with new dates’ date will be added. The data structure is the same, only data is different. How to automatically obtain the data of a new file in power bi desktop?

Sample 1: Select the latest data based on the creation date

Files in excel:

Steps in Power Query:

1. Get data in desktop: enter the folder path.

2. Filter the excel files you want based on the creation date or modification date.

3. Expand content to get data.

4. Get the latest data when refresh.

Reference: Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Docs

Sample 2: Select the latest data based on the date in the file name

Files in excel:

Data in PQ:

Operations:

We need to import the table data in the folder into excel first, and custom the code in PQ advanced editor to dynamically obtain the source data, so when refreshing the data, it will automatically find the file with the latest date in the folder.

1. The method of getting data is the same as above, then we can see original generated code:

2. Modify the code in Advanced Editor in Power Query as follows:

Step analysis:

  As you can see, we need to obtain the data source dynamic and split the second step of the first picture into three smaller steps (picture 2).

First use the temp step to customize the excel file name, which can include a combination of text and date. The date doesn’t have to be selected only today, it can be tomorrow or yesterday, or even a week ago, it depends on your needs.

Example Yesterday: Date.AddDays ( Date.From(DateTime.LocalNow()),-1)

• Use Date.AddDays() function to returns the date, datetime, or datetimezone result from adding numberOfDays days to the datetime value dateTime.
• Use Date.ToText() function to return a textual representation of date and customize the date format by yourself.
• Use Date.From() function to return a date value from the given value.
• Use DateTime.LocalNow() function to return a datetime value set to the current date and time on the system.

  Second match the same name as temp in the general table to get this table.

• Use Table.SelectRows() function to return a table of rows from the table, that matches the selection condition.

  Third get the content in the table and replace this result in the following ‘import excel’ step.

• Use Table.Max() function to return the largest row in the table.

  Finally, click OK to apply to Power Query and get the data of the corresponding date.

Here’s the final result:  

Summary: According to different needs, we can use different methods to flexibly obtain Excel file data of different dates.

Janey - daily.pbix