Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sebastian
Advocate II
Advocate II

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?

1 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

15 REPLIES 15
Vvelarde
Community Champion
Community Champion

@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)




Lima - Peru

Thanks....

 

SabineOussi
Skilled Sharer
Skilled Sharer

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.

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

.

.

.

.

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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. 

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!

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

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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.

"each" is missing in the 3rd argument

"seq_value" becomes "seq_val"

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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.

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

 

ListGenerate errors.png

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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. 

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!

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.