locked
”constant” index column, which only changes, when the step name changes RRS feed

  • Question

  • Hello,

    As part of a data set, I have a column named “Step”:

    Step

    Preparation

    Preparation

    Preparation

    Action

    Action

    Verification

    Verification

    Verification

    Verification

    I would like to add with Power Query  a ”constant” index column, which only changes, when the step name changes.

    The new column should look like:

    Index

    1

    1

    1

    2

    2

    3

    3

    3

    3

    Upfront I don’t know:

    -          The step name (as a consequence conditional logic is no option)

    -          The frequency of the step names

    How could this be realized with a single Power Query query?

    Thanks in advance!

    Frank

    Saturday, August 18, 2018 9:13 PM

Answers

  • Another option would be to add the following step to your code:

    AddedCustomIndex = Table.AddColumn(<PreviousStepName>, "Index", each List.PositionOf(List.Distinct(<PreviousStepName>[Step]), _[Step]) + 1)
    

    • Marked as answer by Frank data Sunday, August 19, 2018 9:36 PM
    Sunday, August 19, 2018 6:50 PM

All replies

  • Excel 2016 Pro Plus with Power Query (aka Get & Transform)
    Index repeating items.
    http://www.mediafire.com/file/7q8mkqk7iwsq9yf/08_18_18.xlsx/file

    Saturday, August 18, 2018 9:59 PM
  • Another option would be to add the following step to your code:

    AddedCustomIndex = Table.AddColumn(<PreviousStepName>, "Index", each List.PositionOf(List.Distinct(<PreviousStepName>[Step]), _[Step]) + 1)
    

    • Marked as answer by Frank data Sunday, August 19, 2018 9:36 PM
    Sunday, August 19, 2018 6:50 PM
  • Dear Herbert and Colin,

    Thanks for your responses.

    Both solutions were succesful.

    Much appreciated!

    Frank


    Sunday, August 19, 2018 9:36 PM