Forum Discussion

DashboardEMECH's avatar
DashboardEMECH
Helper II
5 years ago
Solved

Create a Dynamic Table which extracts value from the source

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 2020100
2nd Oct 202050
3rd Oct 202045
Total195

 

Excel Report in Email (Day 2):

1st Oct 202080
2nd Oct 202050
3rd Oct 202045
4th Oct 202050
Total225

 

Output required in PowerBI:

 

Day 1 Output:

3rd Oct 2020195

 

Day 2 Output:

3rd Oct 2020195
4th Oct 2020225

 

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.

Need Help
  • artemus's avatar
    artemus
    5 years ago

    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

     

     

2 Replies

  • DashboardEMECH's avatar
    DashboardEMECH
    Helper II
    5 years ago

    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's avatar
      artemus
      Microsoft Employee
      5 years ago

      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