Adding the data into new row everytime there is an update

[DashboardEMECH]

I receive a CSV file daily on my email id.  I can perform a powerBI query on the same and arrive at a number.  (For eg:  Sum of a specific column).  I want to have this output store in a different table (say Table 2) with today's date and output result.  The next day, powerBI query should perform the same query on the new file recieved on the email id and the output needs to be added to the Table 2 below the previous day data.

Ex:

My csv file of DAY 1 (12/23/2020) contains the following:

North	100
East	200
West	300
South	400

PowerBI will perform the sum with the output of 1000.  This needs to be stored in a new table (Table 2) as below:

Date	Value
12/23/2020	1000

On Day 2 (12/24/2020), the query reads the latest email and performs the same function:

North	200
East	100
West	400
South	500

The output in Table 2 gets revised to below:

Date	Value
12/23/2020	1000
12/24/2020	1200

How can I achieve the above?

[v-yingjl · Accepted solution]

Hi @DashboardEMECH ,

You can import all your files into a folder, create a table for each day, add custom columns to get the sum value and the created date, for example:

let
    Source = Folder.Files("C:\Users\xxx\Desktop\files"),
    #"C:\Users\xxx\Desktop\files\_DAY1 xlsx" = Source{[#"Folder Path"="C:\Users\xxx\Desktop\files\",Name="DAY1.xlsx"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"C:\Users\xxx\Desktop\files\_DAY1 xlsx"),
    Table1_Table = #"Imported Excel"{[Item="Table1",Kind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(Table1_Table, "Date", each Source{[#"Folder Path" = "C:\Users\xxx\Desktop\files\",Name = "DAY1.xlsx"]}[Date created]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type datetime}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Date"}, {{"Sum", each List.Sum([Value]), type nullable number}})
in
    #"Grouped Rows1"

intial tableresult table

Create a blank query to combine these tables:

Before closing power query, you can disable loading other source tables to improve performance:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@DashboardEMECH  So there are two problem you are trying to resolve here

1. You want to extract your data from your emails and saved those file in your local or any other drive

2. Once files are saved, you wish to view a summary of daily total from the file data

1. For the first problem, you will have to use some VBA code that can help you in extracting data from your mails and save it in a folder. You may have to explore lot of option. But let me tell you this is possible

2. For the second problem, you save your file in a folder and name each file like 

dd.mm.yyyy -> this will allow you to use your file name as date 

Below option will allow you to extract data from a folder and its file. Every time you refresh the data in the powerbi, it will automatically use all the files saved in the drive. You only need to save your new file everytime, you wish to refresh the data

then select combine and transform option in the next screen

now you can use file name as date by adding a new column and converting data type to date

final output is below

[Jimmy801]

Hello @DashboardEMECH 

all of your table have to be available in your mail. Filter all mails, that only this mails are showing up. Use the appendix to extract the sum of your tables and the incoming date. This should give you the desired result

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy