Forum Discussion
Helper IIAdding the data into new row everytime there is an update
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?
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.
3 Replies
Jimmy801Community Champion
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- 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
v-yingjlCommunity Support
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.
