cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Repeating Conditional Index

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.

Picture1.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Repeating Conditional Index

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
Highlighted
Community Champion
Community Champion

Re: Repeating Conditional Index

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Advocate II
Advocate II

Re: Repeating Conditional Index

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.

Highlighted
Helper III
Helper III

Re: Repeating Conditional Index

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors