Forum Discussion

DanielaFerreira's avatar
DanielaFerreira
Regular Visitor
4 years ago
Solved

Create sequence based on another power query column

Hi guys, 

Could you help me with difficulty bellow?

I need creat a sequencebeging from 1 acording with the colum "Picking hour", but when the colum NF change I need to restart the sequence from 1 again. 

 

Bellow one exemple that I need

 

 

Thanks a lot

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can do this by grouping on [NF] keeping All Rows

    And then edit the code generated to add an index column to each group.

     

    Full M query example you can paste into the Advanced Editor after creating a new Blank Query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI1VYrVgTLNEEwLMNMJyDQwRDBNwExnINPECME0RjCRFJgrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NF = _t, #"Picking hour" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"NF"}, {{"Subtable", each Table.AddIndexColumn(_, "Order", 1, 1), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Subtable", {"Picking hour", "Order"}, {"Picking hour", "Order"})
in
    #"Expanded Count"

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    You can do this by grouping on [NF] keeping All Rows

    And then edit the code generated to add an index column to each group.

     

    Full M query example you can paste into the Advanced Editor after creating a new Blank Query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI1VYrVgTLNEEwLMNMJyDQwRDBNwExnINPECME0RjCRFJgrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NF = _t, #"Picking hour" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"NF"}, {{"Subtable", each Table.AddIndexColumn(_, "Order", 1, 1), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Subtable", {"Picking hour", "Order"}, {"Picking hour", "Order"})
in
    #"Expanded Count"