Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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.
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.
@Anonymous - Create a calculated column with below DAX:
@Anonymous Have you created measure or calculated column?
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?
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |