Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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.
Proud to be a Super User!
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)
Thanks....
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.
Proud to be a Super User!
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?
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"
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |