Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sequential Assigned Number

I'm trying to assign "audit numbers" to order information per store.  I have the Order, Store Information in one table, and a list of employees and store assignments on another table.  

I want to take the table "order", find the store number, reference the "store" table to see what the maximum number of employees there are, and assign a sequential number based on the max number of employees in that store...

 

Such as, I have 4 employees in store # 127496.  I want to add a column in my "Order" table to start at the first entry as 1, and then sequentially add 2 to the second order, 3 to the third, etc.  Once it gets to adding a 4, if there is another order for that store, it would start over again with 1, and repeat the pattern until that store is finished.  When the store number changes in the data, then it would look up the max # of reps in that store, which is 5, and start the sequence for that store.

 

 

Is this possible using DAX?

 

 

 

order #StoreAssigned outcome
817127496              1
828127496              2
416127496              3
300127496              4
500127496             1
502127496             2
507127496               3
538127496             4
527127496            1
859127496            2
756127496            3
354127496            4
342127496            1
476127496             2
824127496           3
919127496           4
810127496          1
426134765            1
812134765           2
745134765            3
722134765           4
534134765           5
565134765          1
956134765          2
270134765          3
210134765          4
800134765          5
746134765         1
348134765          2
310134765         3
926134765        4
720134765           5
984134765          1
836134765           2
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    Does the numbering have to start at 1?

     

    Table Stores:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyN7E0U9JRcgRiQ6VYHSQhJyA2QhVyBmJjVCEXIDaBCBmbmJuZArmuCLNgQm4Is2BC7gizYEIemGZ5ArGpUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Employee = _t, #"Employee #" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}, {"Employee", type text}, {"Employee #", Int64.Type}})
in
    #"Changed Type"

     

    Table Orders:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+7EcQwCEXRXhQ7ED+BavG4/zbs8WywXKdn4D04z1GS4xii6XuN63hAq4PL6mBzdogvKAEtYWgJ5R2xO2TwjnCAo9Zz8TmsbEFLCX5xfTPsyYrfhHZID4BiIswBCys70KI5AQKoOXkHMswLwIzN51I5UTi97H/lugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"order #" = _t, Store = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order #", Int64.Type}, {"Store", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Counter", (k)=> Table.SelectRows(Stores,each [Store]=k[Store])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each 1+Number.Mod([Index],Table.RowCount([Counter]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"order #", "Store", "Custom"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Custom", Int64.Type}})
in
    #"Changed Type1"
  • lbendlin's avatar
    lbendlin
    3 years ago

    These are Power Query queries. How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

     

    If the seqence has to start at 1 then the Orders table would be slightly different:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+7EcQwCEXRXhQ7ED+BavG4/zbs8WywXKdn4D04z1GS4xii6XuN63hAq4PL6mBzdogvKAEtYWgJ5R2xO2TwjnCAo9Zz8TmsbEFLCX5xfTPsyYrfhHZID4BiIswBCys70KI5AQKoOXkHMswLwIzN51I5UTi97H/lugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"order #" = _t, Store = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order #", Int64.Type}, {"Store", Int64.Type}}),
    AddCount = (t as table)=> let
    #"Added Index" = Table.AddIndexColumn(t, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Counter", (k)=> Table.SelectRows(Stores,each [Store]=k[Store])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each 1+Number.Mod([Index],Table.RowCount([Counter])))
     in
    #"Added Custom1",
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store"}, {{"Count", each _, type table [#"order #"=nullable number, Store=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each AddCount([Count])    ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"order #", "Store", "Index", "Counter", "Custom"}, {"order #", "Store", "Index", "Counter", "Custom.1"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"order #", "Store", "Custom.1"})
in
    #"Removed Other Columns1"

     

12 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Does the numbering have to start at 1?

     

    Table Stores:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyN7E0U9JRcgRiQ6VYHSQhJyA2QhVyBmJjVCEXIDaBCBmbmJuZArmuCLNgQm4Is2BC7gizYEIemGZ5ArGpUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Employee = _t, #"Employee #" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}, {"Employee", type text}, {"Employee #", Int64.Type}})
in
    #"Changed Type"

     

    Table Orders:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+7EcQwCEXRXhQ7ED+BavG4/zbs8WywXKdn4D04z1GS4xii6XuN63hAq4PL6mBzdogvKAEtYWgJ5R2xO2TwjnCAo9Zz8TmsbEFLCX5xfTPsyYrfhHZID4BiIswBCys70KI5AQKoOXkHMswLwIzN51I5UTi97H/lugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"order #" = _t, Store = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order #", Int64.Type}, {"Store", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Counter", (k)=> Table.SelectRows(Stores,each [Store]=k[Store])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each 1+Number.Mod([Index],Table.RowCount([Counter]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"order #", "Store", "Custom"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Custom", Int64.Type}})
in
    #"Changed Type1"
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for your question... Yes, the number has to start with 1..

      Where would these 2 codes be placed in relation to the 2 tabled queries I have set up?

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        These are Power Query queries. How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

         

        If the seqence has to start at 1 then the Orders table would be slightly different:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+7EcQwCEXRXhQ7ED+BavG4/zbs8WywXKdn4D04z1GS4xii6XuN63hAq4PL6mBzdogvKAEtYWgJ5R2xO2TwjnCAo9Zz8TmsbEFLCX5xfTPsyYrfhHZID4BiIswBCys70KI5AQKoOXkHMswLwIzN51I5UTi97H/lugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"order #" = _t, Store = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order #", Int64.Type}, {"Store", Int64.Type}}),
    AddCount = (t as table)=> let
    #"Added Index" = Table.AddIndexColumn(t, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Counter", (k)=> Table.SelectRows(Stores,each [Store]=k[Store])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each 1+Number.Mod([Index],Table.RowCount([Counter])))
     in
    #"Added Custom1",
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store"}, {{"Count", each _, type table [#"order #"=nullable number, Store=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each AddCount([Count])    ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"order #", "Store", "Index", "Counter", "Custom"}, {"order #", "Store", "Index", "Counter", "Custom.1"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"order #", "Store", "Custom.1"})
in
    #"Removed Other Columns1"