Forum Discussion

Sebastian's avatar
Sebastian
Advocate III
10 years ago
Solved

create a Counter which adds +1

Hi all,   is it possible to create a new column which acts as Counter? The Column starts with 1 and adds +1 in every row  or if a value in another Column change.   how could i realize this?
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    I'm guessing this is related to the MonthIndex you were asking about in that other thread. Even if it is not you should still be able to use the same pattern. That's the only way I could come up with of sequentially incrementing a previous number that only changes on arbitrary rows. ImkeF might have another method though. I'll have to try that one out.

     

    Edit: now that I've said that, I just came up with a way to do it in DAX. See the link for the solution. I don't want to clutter up this thread with extra code if that solution doesn't quite apply to this question.

Sebastian's avatar
Sebastian
Advocate III
10 years ago

Ok, thanks for yout answer.

 

And if the counter only should add +1 if the value in another field change?

 

For example:

 

 

Date                 Counter

30.05.2016           1

30.05.2016           1

30.05.2016           1

02.06.2016           2

03.06.2016           3

03.06.2016           3

.

.

.

.

SabineOussi's avatar
SabineOussi
Skilled Sharer
10 years ago

I guess that's a row comparison. I'm not sure we can index the column and increment that index.

 

I will wait for an answer just like you!

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    This is a step-by-step-method: Group on date to create a table with distinct dates, add your index column and then use this as a lookup-table by merging it to your source:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Date"}, {}),
    IndexColumn = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(Source,{"Date"},IndexColumn,{"Date"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Index"}, {"Index"})
in
    #"Expanded NewColumn"