Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
theopa
Frequent Visitor

power query grouping by name and sort and cumulative

hi all.

 

i have a question about some product table

 

there are 
[Prodcode], [porductNM] , [Date], [salesamount], [salesprice]

 

that i want is addcolumns which one value is cumulative value with [salesamount] and [salesprice]
with date
if change next year -> reset cumulative

for example 

[porductNM] , [Date], [salesamount], [salesprice]
candy | 2021-01-01 | 3 |  2 |
cookie | 2021-01-01 | 5 | 4 |
chocolate | 2021-01-01 | 7 | 6 |
candy | 2021-02-01 | 3 |  2 |
cookie | 2021-02-01 | 5 | 4 |
chocolate | 2021-02-01 | 7 | 6 |
candy | 2021-03-01 | 3 |  2 |
cookie | 2021-03-01 | 5 | 4 |
chocolate | 2021-03-01 | 7 | 6 |
candy | 2022-01-01 | 3 |  2 |
cookie | 2022-01-01 | 5 | 4 |
chocolate | 2022-01-01 | 7 | 6 |
candy | 2022-02-01 | 3 |  2 |
cookie | 2022-02-01 | 5 | 4 |
chocolate | 2022-02-01 | 7 | 6 |
candy | 2022-03-01 | 3 |  2 |
cookie | 2022-03-01 | 5 | 4 |
chocolate | 2022-03-01 | 7 | 6 |

=>

[porductNM] , [Date], [salesamount], [salesprice], [Csalesamount], [Csalesprice]
candy | 2021-01-01 | 3 |  2 | 3 | 2
cookie | 2021-01-01 | 5 | 4 | 5 | 4
chocolate | 2021-01-01 | 7 | 6 | 7 | 6
candy | 2021-02-01 | 3 |  2 | 6 | 4
cookie | 2021-02-01 | 5 | 4 | 10 | 8
chocolate | 2021-02-01 | 7 | 6 | 14 | 12
candy | 2021-03-01 | 3 |  2 | 9 | 6
cookie | 2021-03-01 | 5 | 4 | 15 | 12
chocolate | 2021-03-01 | 7 | 6 | 21 | 18
candy | 2022-01-01 | 3 |  2 | 3 | 2
cookie | 2022-01-01 | 5 | 4 | 5 | 4
chocolate | 2022-01-01 | 7 | 6 | 7 | 6
candy | 2022-02-01 | 3 |  2 | 6 | 4
cookie | 2022-02-01 | 5 | 4 | 10 | 8
chocolate | 2022-02-01 | 7 | 6 | 14 | 12
candy | 2022-03-01 | 3 |  2 | 9 | 6
cookie | 2022-03-01 | 5 | 4 | 15 | 12
chocolate | 2022-03-01 | 7 | 6 | 21 | 18

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

hi, @theopa 

    sort = Table.Sort(your_table, {"porductNM", "Date"}),
    f = (t as table) as table => 
        [rec = List.Buffer(Table.ToRecords(t)),
        gen = List.Generate(
            () => [i = 0, r = rec{0}, rt = [Csalesamount = r[salesamount], Csalesprice = r[salesprice]]],
            (x) => rec{x[i]}? <> null,
            (x) => 
                [i = x[i] + 1, r = rec{i}, 
                rt = 
                    [Csalesamount = x[rt][Csalesamount] + r[salesamount],
                    Csalesprice = x[rt][Csalesprice] + r[salesprice]]
                ],
            (x) => x[r] & x[rt]
        ),
        z = Table.FromRecords(gen)][z],
    g = Table.Group(
        sort, {"porductNM", "Date"},
        {{"rows", each f(Table.Sort(_, "Date"))}},
        GroupKind.Local,
        (s, c) => Byte.From(Date.Year(s[Date]) <> Date.Year(c[Date]) or (s[porductNM] <> c[porductNM]))),
    expand = Table.ExpandTableColumn(g, "rows", {"salesamount", "salesprice", "Csalesamount", "Csalesprice"})

View solution in original post

2 REPLIES 2
AlienSx
Super User
Super User

hi, @theopa 

    sort = Table.Sort(your_table, {"porductNM", "Date"}),
    f = (t as table) as table => 
        [rec = List.Buffer(Table.ToRecords(t)),
        gen = List.Generate(
            () => [i = 0, r = rec{0}, rt = [Csalesamount = r[salesamount], Csalesprice = r[salesprice]]],
            (x) => rec{x[i]}? <> null,
            (x) => 
                [i = x[i] + 1, r = rec{i}, 
                rt = 
                    [Csalesamount = x[rt][Csalesamount] + r[salesamount],
                    Csalesprice = x[rt][Csalesprice] + r[salesprice]]
                ],
            (x) => x[r] & x[rt]
        ),
        z = Table.FromRecords(gen)][z],
    g = Table.Group(
        sort, {"porductNM", "Date"},
        {{"rows", each f(Table.Sort(_, "Date"))}},
        GroupKind.Local,
        (s, c) => Byte.From(Date.Year(s[Date]) <> Date.Year(c[Date]) or (s[porductNM] <> c[porductNM]))),
    expand = Table.ExpandTableColumn(g, "rows", {"salesamount", "salesprice", "Csalesamount", "Csalesprice"})

thank you a lot

it's very good solution

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors