Forum Discussion

josephrandall's avatar
josephrandall
Regular Visitor
3 years ago
Solved

Index column based on value in another column

I know my title isn't explaining my problem well. Let me elaborate.

 

I have a column (end, far right, pic1) where I placed a 1 in the rows where I would like that index number to end. the other rows are null.

I would like the index column to update after each 1 (like the index column in pic2)

 

Is this possible ?

 

Please see my pictures for a better understanding. 

Need Help
  • 6677028's avatar
    6677028
    3 years ago

    Sent you.

    let

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ScoreText", type text}, {"DriveText", type text}, {"HomeScore", Int64.Type}, {"End", Int64.Type}}),

        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),

        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [End]=null then null else Number.ToText([End])&Number.ToText([Index])),

        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),

        #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each _, type table [ScoreText=nullable text, DriveText=nullable text, HomeScore=nullable number, End=nullable number, Index=number, Custom=nullable text]}}),

        #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),

        #"Expanded Count" = Table.ExpandTableColumn(#"Added Index1", "Count", {"ScoreText", "DriveText", "HomeScore", "End", "Index", "Custom"}, {"Count.ScoreText", "Count.DriveText", "Count.HomeScore", "Count.End", "Count.Index", "Count.Custom"}),

        #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Custom", "Count.Index", "Count.Custom"})

    in

        #"Removed Columns"

3 Replies

  • 6677028's avatar
    6677028
    Frequent Visitor
    3 years ago

    Sent you.

    let

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ScoreText", type text}, {"DriveText", type text}, {"HomeScore", Int64.Type}, {"End", Int64.Type}}),

        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),

        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [End]=null then null else Number.ToText([End])&Number.ToText([Index])),

        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),

        #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each _, type table [ScoreText=nullable text, DriveText=nullable text, HomeScore=nullable number, End=nullable number, Index=number, Custom=nullable text]}}),

        #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),

        #"Expanded Count" = Table.ExpandTableColumn(#"Added Index1", "Count", {"ScoreText", "DriveText", "HomeScore", "End", "Index", "Custom"}, {"Count.ScoreText", "Count.DriveText", "Count.HomeScore", "Count.End", "Count.Index", "Count.Custom"}),

        #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Custom", "Count.Index", "Count.Custom"})

    in

        #"Removed Columns"

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi josephrandall,

     

    I've prepared 2 versions of code for you:

    • v1 is slower but easier to understand (using Index Helper column. If you doesn't have a lot of rows of your data - it is ok to use also this version)
    • v2 is the only propper way how to achive this - using List.Generate function (it is really fast solution)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorVQSYMcfPRpbARWLTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [End = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"End", Int64.Type}}),
    v1_AddedIndexHelper = Table.AddIndexColumn(ChangedType, "Index Helper", 0, 1, Int64.Type),
    v1_AddedIndex = Table.AddColumn(v1_AddedIndexHelper, "Index", each List.Sum(List.FirstN(ChangedType[End], [Index Helper])), Int64.Type),
    v1_RemovedIndexHelper = Table.RemoveColumns(v1_AddedIndex,{"Index Helper"}),
    v2_AddedIndex_Faster = [ l = List.Buffer(ChangedType[End]),
    lg = List.Generate(
            ()=> [ x = 0, i = l{x} ],
            each [x] < List.Count(l),
            each [ x = [x]+1, i = List.Sum({[i], l{x}})],
            each [i]
    ),
   toTable = Table.RemoveLastN(Table.FromColumns(Table.ToColumns(ChangedType) & {{null} & lg}, Table.ColumnNames(ChangedType) & {"Index"}), 1)
  ][toTable]
in
    v2_AddedIndex_Faster