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"

 

jnickell's avatar
jnickell
Helper V
7 years ago

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