Adding New Rows Automatically from Loaded Table into New Custom Table

[muhaiza]

In power bi query, I pull a table from oracle and named it Table A. In Table A there is one column named Cost. Then I clicked Enter Data option to create a new table named Pareto Table. In this Pareto Table, I created two columns which are Date and Total Cost. Next, I scheduled the query to refresh every Monday (once in a week I set this up on after published and scheduled it my workspace). The Pareto Table is not loaded, but a custom table.

How can I schedule to capture the total amount of column Cost in Table A into my Pareto Table every time the query refreshed? This means every week, Pareto Table will add new row that capture the total cost and the date is capture the data from column Cost in Table A. I want the rows added automatically every week. Which means I would not need to prefill the date and so on.

For example, after two weeks, I will see in my Pareto Table having two rows added and keep adding once in a week.

[APQueiroz]

Hello! Sorry if my English is bad. I'm still learning.

Considering that you can't have cyclic references in power query, you can create a column in Table A that identifies the cycle of the cost, then summarize the Cost value in the Pareto table. If my cycle, like in your example, is the week of the year, I can do the following:

Generating a example for table A

let
    #"Generated Series" = 
        List.Generate( 
            () => [ Date = DateTime.LocalNow(), Cost = Number.RandomBetween(10, 10000) ], 
            each [Date] > DateTime.LocalNow() - #duration(90, 0, 0, 0), 
            each [ Date = [Date] - #duration(1, 0, 0, 0), Cost = Number.RandomBetween(10, 10000) ],
            each _
        ),
    #"Generated table" = Table.TransformColumnTypes(  Table.FromRecords(#"Generated Series"), {{"Date", type date}, {"Cost", type number}} ),
    #"Add year week" = Table.AddColumn(#"Generated table", "Week", each Date.WeekOfYear([Date]), Int64.Type)
in
    #"Add year week"

Now, I can simply summarize the Cost per week in the Pareto table: 

let
    SummarizedData = 
        Table.Group( #"Table A", "Week", {"Total", each List.Sum([Cost])} ),
    #"Change Type" = Table.TransformColumnTypes(SummarizedData,{{"Total", type number}})
in
    #"Change Type"

And there you have it!

I hope that helps.