Create sequential numbering that restarts daily

Question

Hello,&nbsp;I have data being inputed daily by a Form for different department's next day plans. There are multiple plans being subitted for multiple work areas. I am trying to create a column in PowerQuery that would add a seqential number to each work area sort of like the output column in the table below.&nbsp;DateDisciplineWork AreaOutput6-JulDiscipline 1Area 1Area 1 - 16-JulDiscipline 2Area 1Area 1 - 26-JulDiscipline 1Area 2Area 2 - 16-JulDiscipline 4Area 1Area 1 - 36-JulDiscipline 3Area 2Area 2 - 26-JulDiscipline 4Area 2Area 2 - 36-JulDiscipline 2Area 3Area 3 - 1&nbsp;I would also want the sequential number to restart when the date changes. The point of this is that I could have cards on a dashboard be populated with the data from the same Area sequential number that would just update to today's date.&nbsp;I hope this makes sense.&nbsp;Thanks!

v-yingjl · Accepted Answer

Hi&nbsp;larrylob&nbsp;,
You can try this query:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtP1Ks1R0lFyySxOzizIycxLVTAEch2LUhOBjFgdrCqMCKqAm2GES4UJQTOMiTcDpwq4S43BKiwJ+haLCiOCKtB8i0WFCUEzjIk3A6cKZN/GAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Discipline = _t, #"Work Area" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Discipline", type text}, {"Work Area", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] = Date.From(DateTime.LocalNow())),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Work Area", Order.Ascending}, {"Discipline", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Date", "Work Area"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table [Date=nullable date, Discipline=nullable text, Work Area=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Discipline","Index"}, {"Discipline","Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Output", each [Work Area] &amp; " - " &amp; Text.From([Index]),type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

&nbsp;
Best Regards,Community Support Team _ Yingjie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Create sequential numbering that restarts daily

2 Replies

Related content

Sequential Order column based on multiple criteria

Sequentially number duplicates

Sequential Assigned Number

Restart Capacity

Create 3 digit sequential unique key

Date	Discipline	Work Area	Output
6-Jul	Discipline 1	Area 1	Area 1 - 1
6-Jul	Discipline 2	Area 1	Area 1 - 2
6-Jul	Discipline 1	Area 2	Area 2 - 1
6-Jul	Discipline 4	Area 1	Area 1 - 3
6-Jul	Discipline 3	Area 2	Area 2 - 2
6-Jul	Discipline 4	Area 2	Area 2 - 3
6-Jul	Discipline 2	Area 3	Area 3 - 1