Results 1 to 2 of 2

Thread: Get Top n records using power query

  1. #1

    Get Top n records using power query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    HI,

    I have table with columns- date, edgeip, httpstatuscode, id, start.
    Using power query I need to show For the last 10 minutes (rolling window) , show the aggregated number of HTTP responses by HTTP status ("status") for the top 10 Edge Server IPs.
    So I i used group by on edgeip and then status and taken count(all rows).
    My data is like-
    date edgeip id hhtpstatuscode start
    11/7/2015 2:51:42 PM 80.239.237.101 123 200 11144
    11/7/2015 2:48:34 PM 72.246.43.216 345 200 22243
    11/7/2015 2:52:52 PM 165.254.144.9 567 200 333
    11/7/2015 2:49:52 PM 165.254.144.9 678 300 1232
    11/7/2015 2:53:33 PM 2.20.191.78 789 300 2345
    11/7/2015 2:47:29 PM 216.151.187.192 809 304 4353
    11/7/2015 2:45:13 PM 216.151.187.192 135 400 45645
    11/7/2015 2:54:22 PM 165.254.144.9 269 200 6345
    11/7/2015 2:50:52 PM 2.20.191.78 379 300 34536
    After grouping as ->
    edgeip status count
    80.239.237.101 200 1
    72.246.43.216 200 1
    165.254.144.9 200 2
    165.254.144.9 300 1
    2.20.191.78 300 2
    216.151.187.192 304 1
    216.151.187.192 400 1
    After grouping i want out put as top 3 edgeip records. In above case the out put should be as shown below table-
    165.254.144.9 200 2
    165.254.144.9 300 1
    2.20.191.78 300 2
    216.151.187.192 304 1
    216.151.187.192 400 1

    for edgeip 216.151.187.192 total count is 2(304-1, 400-1), 165.254.144.9 count is 3 (200-2,300-1), 2.20.191.78 count is 2(300) so they will come in top 3. edgeip 72.246.43.216 & 80.239.237.101 will not come as total count is 1 for each.
    Last edited by Krishna007; 2015-11-07 at 09:59 AM.

  2. #2
    Super Moderator miguel's Avatar
    Join Date
    Nov 2015
    Posts
    18
    Articles
    0
    Excel Version
    Office 365
    this is my start table (table1):
    edgeip status count
    80.239.237.101 200 1
    72.246.43.216 200 1
    165.254.144.9 200 2
    165.254.144.9 300 1
    2.20.191.78 300 2
    216.151.187.192 304 1
    216.151.187.192 400 1

    this is my code:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"edgeip", type text}, {"status", Int64.Type}, {"count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"edgeip"}, {{"sUM", each List.Sum([count]), type number}, {"Column", each _, type table}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"sUM", Order.Descending}, {"edgeip", Order.Ascending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",3),
    #"Expanded Column" = Table.ExpandTableColumn(#"Kept First Rows", "Column", {"count", "status"}, {"count", "status"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column",{"sUM"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"edgeip", "status", "count"})
    in
    #"Reordered Columns"

    This is the output:
    edgeip status count
    165.254.144.9 200 2
    165.254.144.9 300 1
    2.20.191.78 300 2
    216.151.187.192 304 1
    216.151.187.192 400 1

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •