Daily Data Dump to be appended to existing dataset

[AutoKris]

Hi,

Trying to find best and simplest solution for this ask.

I would like to avoid creating a database using SQL or Python, as I know basics how to use Microsoft 365 tools, Sharepoint with all Power Tools.

Scenario is:

Every day a data dump in form of an excel is sent to my email.

I need to produce a Power BI dashboard showing last 7 days status.

So the daily report needs to be added to an existing dataset everyday.

Below image of the model, Any suggestions for a simple solution to this?

[AutoKris]

Hello @v-shex-msft ,

Thank you for your efforts.

However I'm looking more for a solution to append data to existing data, meaning I have excel Day 1, I receive new excel Day 2, how to append automatically data from Day 2 to Day 1...etc...?

Is more of an issue how to automatically combine data from new files received on my e-mail daily?

For now I'm using simple solution using Power Automate to save file from my e-mail to a onedrive folderm so adding a new daily file with the specific date in the file name. Then Power BI gets data from the folder. However this data is in multiple files, in the future Power BI will take a lot of time to refresh data set from all those single files.

How to just use one source of data that is getting appended, how to do that?

[v-shex-msft]

HI @AutoKris,

I think there should be more related to operate on the excel sheets.

Perhaps you can take a look at the following link about append data to existing worksheet or create a new file and use it combine different files.

Append existing excel sheet with new dataframe using python pandas - Stack Overflow

Then you only need to use power bi to getting data from the result worksheet.

Reards,

Xiaoxin Sheng

[v-shex-msft]

Hi @AutoKris,

You can extract the numeric part from day field to store them in a new column, then you can write a measure formula to use this field as index to calculate the rolling amounts.

Calculated column:

Day Index =
SUBSTITUTE ( Table[Day reported], "Day ", "" )

Measure formula:

formula =
CALCULATE (
    COUNTROWS ( VALUES ( Table[ID] ) ),
    FILTER ( ALLSELECTED ( Table ), [Day Index] <= MAX ( Table[Day Index] ) )
)

Regards,

Xiaoxin Sheng