Create a Dynamic Table which extracts value from the source

[DashboardEMECH]

Hey Guys,

I am using an excel report which is emailed to my Outlook account on a daily basis.  I can extract the table in the excel report in PowerBI.  However, I want PowerBI to store the previous data value from the table and add the new data value below it.  Below example will clarify:

Excel Report in Email (Day 1):

1st Oct 2020	100
2nd Oct 2020	50
3rd Oct 2020	45
Total	195

Excel Report in Email (Day 2):

1st Oct 2020	80
2nd Oct 2020	50
3rd Oct 2020	45
4th Oct 2020	50
Total	225

Output required in PowerBI:

Day 1 Output:

3rd Oct 2020

195

Day 2 Output:

3rd Oct 2020	195
4th Oct 2020	225

Similary for Day 3, it will add another row below with the total of the table attached in the emai on Day 3.

Any idea how can I achieve this?

Not sure I was able to explain my requirements clearly, but any help would be appreciated.

Thanks in advance.

[DashboardEMECH]

Just to clarify in the above post:

The table is not displayed properly. All the tables in the original post are two Column Table with Date in First Coloum and amount appearing after the date in the 2nd Column.

[artemus · Accepted solution]

Without incremental refresh Power Bi isn't persistant. I.e. you will need to load all previous data again for each day.

That being said, if you have all the previous Excel sheets in a single folder you can combine it into 1 dataset.

To do this

1. Create a folder connection to the location of your ExcelSheets, then in the first column click the combine button.
2. Do a group by using the Source.Name as the key in the group, with
   1. Max of Date Aggregate
   2. Sum of Amount Aggregate
3. Finally, simply reemove the Source.Name column