Rank for data

[Anonymous]

I would like to create a rank by date and time, based on this information in the picture, the result needs to be
10/07/2022 23:00 36065
10/07/2022 22:00 35848

I am using the power query via power query,

[MarkLaf]

In Power Query, just sort the table so it is in your desired order, then add an index column.

[Anonymous]

By inserting this index, I make a simple rank, I need the breakdown of the rank by hour...,

[MarkLaf · Accepted solution]

Ah, sorry, did not quite understand the requirements reflected from your desired output. It looks like you want to get the breakdown by hour and get the earliest integer from [pedidos_vtex], right?

The approach to take is to add a column that provides the datetime rounded down to start of the hour, group on that column, and get the minimum pedidos_vtex value. The following M code can achieve this:

let
    Source = SampleData,
    AddByHour = 
    Table.AddColumn(
        Source, 
        "Datetime By Hour", 
        each Time.StartOfHour([data_consul]), 
        type datetime
    ),
    GroupByHourMinVtex = 
    Table.Group(
        AddByHour, 
        {"Datetime By Hour"}, 
        {{
            "pedidos_vtex_FirstInHour", 
            each List.Min([pedidos_vtex]), 
            type nullable Int64.Type
        }}
    )
in
    GroupByHourMinVtex

The above code is working with the following sample data:

data_consul	pedidos_vtex
10/7/2022 11:52:52 PM	36196
10/7/2022 11:47:34 PM	36184
10/7/2022 11:04:46 PM	36065
10/7/2022 10:59:26 PM	36054
10/7/2022 10:38:01 PM	35979
10/7/2022 10:00:42 PM	35848
10/7/2022 9:55:31 PM	35151
10/7/2022 9:40:39 PM	35148
10/7/2022 9:00:00 PM	35145
10/7/2022 8:59:59 PM	35140
10/7/2022 8:23:35 PM	35138
10/7/2022 8:10:10 PM	35132

And produces the following output:

Datetime By Hour	pedidos_vtex_FirstInHour
10/7/2022 11:00:00 PM	36065
10/7/2022 10:00:00 PM	35848
10/7/2022 9:00:00 PM	35145
10/7/2022 8:00:00 PM	35132

(Edit: simplified M, specifically AddByHour - Time.StartOfHour actually can take time, datetime, or datetimezone as an input, so using Time.From and adding to Date.From was unnecessary)

[Anonymous]

Thanks a lot, it worked!