Forum Discussion

DashboardEMECH's avatar
DashboardEMECH
Icon for Helper II rankHelper II
5 years ago
Solved

Adding 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 ta...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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's avatar
negi007
Icon for Community Champion rankCommunity Champion
5 years ago

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