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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors
Top Kudoed Authors