create a Counter which adds +1

[Sebastian]

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?

[Vvelarde]

@Sebastian

You can do it with a RankX; (depending of what type of values is your column)

Counter = RANKX('Dates with Counter';'Dates with Counter'[Fecha];;ASC;Dense)

[amejiai]

Thanks....

[SabineOussi]

Hi Sebastian,

If you want to have a column that numbers the row, you can go to Edit Queries and add an Indexed Column and choose it to start at 1.

[Sebastian]

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

.

.

.

.

[KHorseman · Accepted solution]

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.

[Anonymous]

Thought I would just post this as I have done something like this at the moment.

You could use List.Generate() as follows

List.Generate
(() =>
     [counter = 1, seq_val = 1], // this is our initial values 
     each [counter] <= List.Count(my_list), // our terminating condition
     each [
        seq_val = if my_value{[counter]} <> my_value{[counter] - 1} then [seq_val] + 1 else [seq_val],
        counter = [counter] + 1
     ], // change seq_val if your value (which should be a list) changes
     each [seq_val] // now output the list
)

This gives you a list which you then need to integrate into your table. 

[plukacsi]

Could you please ellaborate on "integrate the list"? I have got a list in every cell but I don't know how to use them.

Thank you!

[MarcelBeug]

Why do you react on a topic that is more than a year old and provide code that doesn't even work?

[Anonymous]

Thought someone might find it useful as I was passing through.

Code should work fine. I am using something like it right now. Just change the names of the variables as you require.

Provides another way to solve the problem.

[MarcelBeug]

"each" is missing in the 3rd argument

"seq_value" becomes "seq_val"

[Anonymous]

I think you need to refresh your browser.

It looks fine to me. I did edit it after the initial post to fix this error.

Thanks for the comments though.

[MarcelBeug]

I just refreshed my browser. Here is my screenshot with the issues.

[Anonymous]

Nice spotting! Have edited it again to fix that.

Hopefully this helps someone.

Also I should add to this, you might find performance improvements if you usef List.Buffer() around any lists you are using.

I have used this type of approach where the tables don't perform well when using Power Query joins.

If using large data sets I would try and do all this in SQL, especially if you will be refreshing in the service. 

[SabineOussi]

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]

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"