Sequential Assigned Number

[Anonymous]

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 #	Store	Assigned outcome
817	127496	1
828	127496	2
416	127496	3
300	127496	4
500	127496	1
502	127496	2
507	127496	3
538	127496	4
527	127496	1
859	127496	2
756	127496	3
354	127496	4
342	127496	1
476	127496	2
824	127496	3
919	127496	4
810	127496	1
426	134765	1
812	134765	2
745	134765	3
722	134765	4
534	134765	5
565	134765	1
956	134765	2
270	134765	3
210	134765	4
800	134765	5
746	134765	1
348	134765	2
310	134765	3
926	134765	4
720	134765	5
984	134765	1
836	134765	2

[lbendlin · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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"

[Anonymous]

Just so I'm clear, I would need to modify the source to reference the table (query) I already have set up in my Power BI report currently.  Is that correct?

[lbendlin]

Correct.

[Anonymous]

I'm running into an error on the Counter and Custom.1 columns...  It literally says "error" in both columns as the result.  Any suggestions?

[lbendlin]

show your code? or a sanitized version.

[Anonymous]

Thanks... It's actually the same code that was supplied in the thread..

[lbendlin]

Click in the white area next to one of the "Error"  links  and share the result

[Anonymous]

Expression.Error: The import Stores matches no exports. Did you miss a module reference?

[lbendlin]

Looks like you don't have a Stores table among your queries?  (like the one I provided in my example)

[Anonymous]

I see what you're calling out... Let me change a few things...