Forum Discussion

mboulos11's avatar
mboulos11
New Member
3 years ago
Solved

Indexing based on two columns

Hello guys, I have this table in power query, and I need to do indexing based on "UnitID" & "Attribute" columns.   I want to index to start every time the Attribute is equal to "Installe...
General Comment
Tips and Tricks
  • Jakinta's avatar
    Jakinta
    3 years ago

    You can try with steps below after the step your table looks like on an image.

     

     

     

    let
    #"Added Index" = Table.AddIndexColumn( PreviousStepName, "Index", 0, 1, Int64.Type),
    Custom1 = Table.AddColumn(#"Added Index", "Extra_Index", each if [Index] = 0 then 1 else if #"Added Index"{[Index]}[Attribute] = "Installer" then 1 else 0),
    Grouped = Table.RemoveColumns ( Table.Group(Custom1, {"UnitID"}, {{"A", each  Table.FromColumns (Table.ToColumns(_) & {List.Skip(List.Accumulate(_[Extra_Index],{-1}, (s , c) => s & {List.Last(s) + c }))}, Table.ColumnNames(_) &{"i"}), type table }}), {"UnitID"} ),
    Expanded = Table.ExpandTableColumn (Grouped, "A", Table.ColumnNames ( Grouped [A]{0})),
    FINAL = Table.RemoveColumns(Expanded,{"Index", "Extra_Index"})
in
    FINAL

     

Jakinta's avatar
Jakinta
Solution Sage
3 years ago

You can try with steps below after the step your table looks like on an image.

 

 

 

let
    #"Added Index" = Table.AddIndexColumn( PreviousStepName, "Index", 0, 1, Int64.Type),
    Custom1 = Table.AddColumn(#"Added Index", "Extra_Index", each if [Index] = 0 then 1 else if #"Added Index"{[Index]}[Attribute] = "Installer" then 1 else 0),
    Grouped = Table.RemoveColumns ( Table.Group(Custom1, {"UnitID"}, {{"A", each  Table.FromColumns (Table.ToColumns(_) & {List.Skip(List.Accumulate(_[Extra_Index],{-1}, (s , c) => s & {List.Last(s) + c }))}, Table.ColumnNames(_) &{"i"}), type table }}), {"UnitID"} ),
    Expanded = Table.ExpandTableColumn (Grouped, "A", Table.ColumnNames ( Grouped [A]{0})),
    FINAL = Table.RemoveColumns(Expanded,{"Index", "Extra_Index"})
in
    FINAL