Search code examples
indexingcountpowerquerygroup

Index Unique Combinations in Power Query

I am trying to aggregate and index by two columns in Power Query. For every number in Column A I want to aggregate the numbers in column B and only count up the index when it changes. Index resets when A ticks over to a new value

A B Count
123 456 1
123 456 1
123 457 2
123 458 3
124 459 1
124 459 1
124 460 2
124 460 2

The scenario is A is a Transport number and B is a Customer Account reference - the Count is therefore a stop sequence for the vehicle.

I want to do this in Power Query since it is part of a daily transformation of data out of one system to be uploaded into another system to be done by the user

I have tried the Transfer > Group by and have managed to get an index on A counting up and resetting when a new value appears in A using https://www.myonlinetraininghub.com/numbering-grouped-data-power-query but I am struggling with how to do it only iterating up when a new B is there

Solution

  • Is this what you are trying to do?

    It sums B by A, and indexes each B by A, resettting each time A resets

    Group on A, sum B, add operation for all rows, then replace each _ with each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type) and expand

    enter image description here

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"Count", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"BSum", each List.Sum([B]), type nullable number}, {"data", each Table.AddIndexColumn(_,"Index", 0, 1, Int64.Type), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"B", "Count", "Index"}, {"B", "Count", "Index"})
in #"Expanded data"

    Alternate Version

    It sums B by A, and indexes each A

    Group on A, sum B, add operation all rows. Then add index. Expand

    enter image description here

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"Count", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"BSum", each List.Sum([B]), type nullable number}, {"data", each _, type table }}),
#"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
#"Expanded data" = Table.ExpandTableColumn(#"Added Index1", "data", {"B", "Count"}, {"B", "Count"})
in #"Expanded data"