CODEWITHSUNDEEP
excelpowerbipowerquerypowerbi-desktopm
dirkthepieman
dirkthepieman

Reputation: 55

Power query editor, increment column group value based on change in value in another column

I have a table that has 3 columns [Index], [Type], & [Count Sales]. I need to add 3 columns [Increment Group], [Cumulative Count Sales] and [Max for Group] using power query editor in PowerBI. I've tried to add a custom column but without any luck. If it can't be done with power query editor, then DAX could be a back up way, but I couldn't get that working either.

The screenshot below shows how I would implement [Increment Group] in Excel. Notice that "Bikes" on rows 11:13 is group 5 and not equal to group 1 like "Bikes" is equal to 1 on rows 2:4. I want it like this, which makes this slightly different to other questions and answers I've seen on stackoverflow. [Cumulative Count Sales] just does a cumulative count of sales by group. And [Max for Group] is the largest value from [Cumulative Count Sales] for each [Increment Group].

Any help would be much appreciated.

enter image description here

Upvotes: 1

Views: 1156

Answers (1)

Ben
Ben

Reputation: 493

You can use the following PowerQuery script as one way to achieve what you're after

let
    //ignore the first 2 steps, essentially start with your table then add from the Grouped Rows step
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Type", type text}, {"Count Sales", Int64.Type}}),
    //
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"Min Index", each List.Min([Index]), type nullable number}, {"Max for Group", each List.Sum([Count Sales]), type nullable number}, {"data", each _, type table [Index=nullable number, Type=nullable text, Count Sales=nullable number]}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Increment Group", 1, 1, Int64.Type),
    #"Expanded data" = Table.ExpandTableColumn(#"Added Index", "data", {"Index", "Count Sales"}, {"Index", "Count Sales"}),
    ListOffset = Table.AddColumn(#"Expanded data", "ListOffset", each [Min Index] - 1, type number),
    ListItems = Table.AddColumn(ListOffset, "ListItems", each [Index] - [Min Index] + 1, type number),
    #"Sorted Rows" = Table.Sort(ListItems,{{"Increment Group", Order.Ascending}, {"Index", Order.Ascending}}),
    ListSales = Table.AddColumn(#"Sorted Rows", "ListSales", each List.Range(#"Sorted Rows"[Count Sales],[ListOffset], [ListItems])),
    #"Cumulative Sales" = Table.AddColumn(ListSales, "Cumulative Sales", each List.Sum([ListSales]), type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Cumulative Sales",{"Index", "Type", "Count Sales", "Increment Group", "Cumulative Sales", "Max for Group"})
in
    #"Removed Other Columns"

The #"Grouped Rows" step starts as a 'normal' Group By (I've used the sum of sales for the Max as this is what it appears to be in your data), which will give you the output shown below enter image description here

But if you go to the formula bar and add in , GroupKind.Local between the last brace and last bracket, it starts a new group every time the Type column changes. Have a look at https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/ or https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-power-query/ for some more info on local grouping.

enter image description here

The #"Added Index" and #"Expanded data" steps are self explanatory, the ListOffset, ListItems and #"Sorted Rows" steps create values/ordering that are used in a List.Range formula in the ListSales step to create a running list of sales amts.

enter image description here

The #"Cumulative Sales" just sums the ListSales values and #"Removed Other Columns" tidies it up

enter image description here

Upvotes: 1

Related Questions