Power Query - Ordinal row number based on unique id and date/time?

Microsoft Employee

7 years ago

Hi Anonymous ,

You could refer to below code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc5BCoAgEIXhq8SsAx11LF276QziQsJFm4Lw/hQyBdny8fHDixGWvZYzr3U79mEJMELItUAaI6DS9zTCCCXRDai9lB+YGOYe5rfQX0BkwSfRhpoQA/WAkkX9RPXHyE5NLIPrwTFYjwQpXQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Interaction ID", Int64.Type}, {"Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Interaction ID"}, {{"A", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded A" = Table.ExpandTableColumn(#"Grouped Rows", "A", {"Date", "Index"}, {"A.Date", "A.Index"})
in
    #"Expanded A"

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

Index.pbix29 KB

Forum Discussion

Power Query - Ordinal row number based on unique id and date/time?