Repeating Conditional Index

[Skemaz]

Hi

I have the following (incomplete) DAX code shown in (1) in the screen-shot below).

= Table.AddColumn(#"Renamed Columns", "SectionNumber", each if [HorseName] = [HorseNameNext] then [IndexOne] else 1)

What I want to happen is that each time (2 in the screen-shot below) and (3 in the screen-shot below) differ, for the index shown in (4 in the screen-shot below) to be reset to 1 and then incremented by 1 in each row thereafter until the next miss-match between (2) and (3).

Any assitance will be greatly appreciated.

Thanks, Mark.

[MarcelBeug · Accepted solution]

First: your screen shot is clearly from the query editor, so it's not DAX, but Power Query (a.k.a. M).

You can simply group by horsename, with operation "All Rows", next adjust the generated code to have an index added to each table for each group, and then expand the nested tables (excluding column "Horsename").

Generated/adjusted code as follows (I used just 1 column for "OtherColumns", next to HorseName):

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"HorseName"}, {{"AllData", each Table.AddIndexColumn(_,"SectionNumber",1,1), type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"OtherColumns", "SectionNumber"}, {"OtherColumns", "SectionNumber"})
in
    #"Expanded AllData"

[jnickell]

Thanks,  This helped me when I was trying to manipulate a dataset.  

To add a little more for anyone else that might come across this,  you have to manually edit the M code either in Advanced Editor, or the 'formula bar'. You can't "just" use the buttons at the top of Power BI

You'll need to manually type in the new Index column you generated.  When you try to "expand" the column via the tool bar it will not give you access to the new Index column you created.

Thanks Marcel

[Skemaz]

Hi Marcel

Thank you very much for your solution.

I'm finally starting to get my head around how to transform data.

Your solution was a great learning experience.

Thanks and best regards, Mark.