Pageviews into Source|Destination|Count by Session

[Anonymous]

Hello there.

I've been able to do it on paper - but I'm really struggling in PowerQuery. Every row in my data is a PageView on a website. A single session can have one or more pageviews (rows). I need to get my data into the below format as shown by "3". I don't know where to start (even in step 1 -- how do I sort by multiple columns as if I were in excel). Could someone please help me? I know its a big ask, but I've tried my best to make it easy to see my problem. 

Table of Pageviews ("1" in above)

SessionID    Timestamp        PageName

1	06/03/2023 23:56:35	Pg.A
1	06/03/2023 23:57:17	Pg.B
1	06/03/2023 23:57:31	Pg.C
1	06/03/2023 23:57:36	Pg.F
1	06/03/2023 23:55:37	Pg.B
1	06/03/2023 23:55:40	Pg.C
2	13/03/2023 09:25:35	Pg.B
2	13/03/2023 09:27:00	Pg.C
2	13/03/2023 09:29:02	Pg.F

Thanks in advance,
Michelle

[Peter_Beck]

Hi -

Yes, you can sort on multiple columns. Just select the first column you want to sort on, sort it, and then select the subsequent column you want to have sorted and sort on that one. It won't "unsort" the first colum.

To achieve step 2, here is a good example of how to do that without code:

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/p...

Basically, after sorting you create an index, and then a second column that is one greater than the index. Then you merge the tables using the index and second index column as the keys. 

After doing this, you should have step 2. The rest can be done in regular DAX (or a pivot table) as a measure of the frequence of each occurrence of each unique row. Alternatively, you can use Transform... Group By (with Count as the summary operation) if you really need to have it in the table. I have included this as part of the script below.

Here is an example you can paste into the "Advanced Editor" and walk through one step at a time:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9BDoAwCAS/Yno2KQUpKTdr4idqf+H/I2rsyfZGZjYslOKCmx1ED+QRkCYk5ajEx2l8dXX+TYgGMZr7nm66DXw0uvc8K8lzQbeBdYHWgDYFah6SIn8/5F5CFIYbkgK+N9YL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SessionID = _t, TimeStamp = _t, PageName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SessionID", Int64.Type}, {"TimeStamp", type text}, {"PageName", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Inserted Addition" = Table.AddColumn(#"Added Index", "Addition", each [Index] + 1, type number),
#"Merged Queries" = Table.NestedJoin(#"Inserted Addition", {"Index"}, #"Inserted Addition", {"Addition"}, "Inserted Addition", JoinKind.LeftOuter),
#"Expanded Inserted Addition" = Table.ExpandTableColumn(#"Merged Queries", "Inserted Addition", {"PageName"}, {"Inserted Addition.PageName"}),
#"Grouped Rows" = Table.Group(#"Expanded Inserted Addition", {"PageName", "Inserted Addition.PageName"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Inserted Addition.PageName", "PageName", "Count"})
in
#"Reordered Columns"

Hope this helps,

Peter