How to number website interactions in Power BI?

[Anonymous]

Hi, I have this table:

And this is what I want to achieve: 

It's basically like an index yet it starts again every time there is a new ID in its way. I guess it should be possible in Power Query, do you have any idea how to achieve this? 

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEyMNQHIiMDIwMFMysDC6CIf1FKapFSrA4uNeZAkcCYUgMDIzNHPMqMgCIe+bmpCgGJ6algdUZGRoaGqOpMrUzMUazErsYU1axYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Time = _t, Page = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Time", type datetime}, {"Page", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Time"}, {{"All", each _, type table [ID=nullable number, Time=nullable datetime, Page=nullable text]}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"ID"}, {{"All", (tableint) => let sort = Table.Sort(tableint,{"Time", Order.Ascending}), AddIndex = Table.AddIndexColumn(sort,"Index",1) in AddIndex}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"Time", "Index"}, {"Time", "Index"})
in
    #"Expanded All"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tahreem24]

@Anonymous  - Create a calculated column with below DAX:

Column = RANKX(Table,Table[Page],,ASC,Dense)

[Anonymous]

Hi  @Tahreem24 ,

I am having troubles using the formula: 

This is my table: 

[Tahreem24]

@Anonymous  Have you created measure or calculated column?

[Anonymous]

That was quick! Thanks a lot, I actually want to rank it based on the time. So what was the first, second, and...  interaction on the website. Also, is it somehow possible to transform this measure into a column using Power Query?