Forum Discussion

davidgray42's avatar
davidgray42
Frequent Visitor
2 years ago
Solved

Index column for only certain rows?

Hi all,  Can anyone please tell me how I can add an index colum but only for certain rows?  So for example in the data below  I would like rows 4 and 5 to be 1 and 2 respectively.   Thanks 
Need Help
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi davidgray42, check this.

     

    Result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1ScC/KTEvLzFOK1YlWCi5JzFMIzs0syQBzPfJzgQqCM3MLivMhCsIyizNL8osw2F75eQou+amo4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Person"}, {{"All", each _, type table [Person=nullable text]}}),
    Ad_AllWithIndex = Table.AddColumn(#"Grouped Rows", "All With Index", each if Text.StartsWith([Person], "Visitor", Comparer.OrdinalIgnoreCase) then Table.AddIndexColumn([All], "Index", 1, 1, Int64.Type) else [All], type table),
    ExpandedAllWithIndex = Table.ExpandTableColumn(Ad_AllWithIndex, "All With Index", {"Index"}, {"Index"}),
    RemovedColumns = Table.RemoveColumns(ExpandedAllWithIndex,{"All"})
in
    RemovedColumns