Add index or rank number based on groups in Power Query

[cathoms]

Hi! I'm helping someone with a Power BI report. We need to report the next 12 available appointment slots by slot length. Most slots are 15 minutes, but the end-user wants to group adjacent open 15 minute slots and view by slot length. So, if a provider has open 15 minute slots at 10:00, 10:15, 10:30, and 10:45, they would have 15-minute slots open at each of those times, 30-minute slots open at 10:00, 10:15, and 10:30, 45-minute slots open at 10:00 and 10:15, and a 60-minute slot open at 10:00.

We have SQL code to group and identify those various slots. What I'm looking for is a way to number the slots of each type for a given provider. That is, all 15 minute slots for PROV_ID 77 should be numbered sequentially starting with the earliest date-time. So all 15 minute slots are numbered 1, 2, 3, 4, etc. All 30 minute slots are numbered 1, 2, 3, 4, etc.

See the following image for examples, where slot types are highlighted in the same color and then numbered. What we need is the numbering as shown in red on the right-hand side

Example 1 - multiple providers

Example 2 - Single provider

[danextian · Accepted solution]

Hi @cathoms 

You will need to use the Group By multiple times and create an index column within one of the tables created by grouping.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRT0lEyMNE3NNQ3MjAyVTA0sTIwAAoZmir4ZuaVlqQWK8XqRCsZG6EpM7YyMcVUZm5OkTJjA6KUmRAwDbsXyFdmiMVthmZoAWeJ1TRcyog0zYSgaYZYgheLMmNi3GZoiumHWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROV_ID = _t, SLOT_BEGIN_TIME = _t, SLOT_LENGTH_MINUTES = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROV_ID", Int64.Type}, {"SLOT_BEGIN_TIME", type datetime}, {"SLOT_LENGTH_MINUTES", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PROV_ID", "SLOT_BEGIN_TIME"}, {{"Group", each _, type table [PROV_ID=nullable number, SLOT_BEGIN_TIME=nullable datetime, SLOT_LENGTH_MINUTES=nullable text]}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"PROV_ID"}, {{"Group", each _, type table [PROV_ID=nullable number, SLOT_BEGIN_TIME=nullable datetime, Group=table]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Group With Index", each Table.AddIndexColumn([Group], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Group"}),
    #"Expanded Group With Index" = Table.ExpandTableColumn(#"Removed Columns", "Group With Index", {"SLOT_BEGIN_TIME", "Group", "Index"}, {"SLOT_BEGIN_TIME", "Group", "Index"}),
    #"Expanded Group" = Table.ExpandTableColumn(#"Expanded Group With Index", "Group", {"SLOT_LENGTH_MINUTES"}, {"SLOT_LENGTH_MINUTES"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Group",{{"SLOT_BEGIN_TIME", type datetime}, {"SLOT_LENGTH_MINUTES", type text}, {"Index", Int64.Type}})
in
    #"Changed Type1"

[cathoms]

This seems to have worked, thank you!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...