Forum Discussion

Skemaz's avatar
Skemaz
Advocate II
9 years ago
Solved

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]...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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"

     

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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"

 

Skemaz's avatar
Skemaz
Advocate II
9 years ago

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.