Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to do grouping to a long list of data

Hi all,

 

I have a 2million rows table with variables customer id, customer name, customer type and ip address. I would like to perform analysis on the ip address, i would like to know the distribution of customer type for ip address appear more than 1 and equal to 1.  My thought of doing it was do the count in the graph and then do grouping for count more than 1. However, due to the unique ip address are more than 100,000, i was unable to do the grouping by clicking one by one. Is there any other alternatives that i can have?

Sample data are as below:  

customer idcustomer namecustomer typeip address
0001MaryGood10.23.10.15
0002JohnGood123.45.16.250
0003JuliaBad1.0.0.1
0004CobyNormal35.26.10.26
0005MichaelNormal162.17.18.250
0006JohnNormal15.16.158.16
0007PollyGood1.0.0.1
0008KarenGood10.23.10.15
0009CarolGood10.23.10.15
0010RobertUnknown122.46.15.16
1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

We can use the formula to create a calculated column to achieve your goal here.

 

Column = IF(CALCULATE(COUNT(Table1[ip address]),ALLEXCEPT(Table1,Table1[ip address]))>1,"more than 1","equal to 1")

Capture.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/6o70w8tlljrd1vg/How%20to%20do%20grouping%20to%20a%20long%20list%20of%20data.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

We can use the formula to create a calculated column to achieve your goal here.

 

Column = IF(CALCULATE(COUNT(Table1[ip address]),ALLEXCEPT(Table1,Table1[ip address]))>1,"more than 1","equal to 1")

Capture.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/6o70w8tlljrd1vg/How%20to%20do%20grouping%20to%20a%20long%20list%20of%20data.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
NH
Advocate II
Advocate II

 

You can try to group the IP address in Query editor and count the no of duplicated IP address. The sample query editor code as below. you can try it. Hope this help. Cheers.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBNC8IwDIb/S88jNN3azaseBEURwdPYodOBw9pAUcR/b5qD22UU8ubjgbxp2ypUhTr49GXZEt1YUIMpgSNa1RWtMtzb0T3OCJ5XFtCBsVqYMjPvMHrWtRcGND+UacX1hvq840jp6QMnpQXj8hbjhLHZx3i9+yHMMXQGsAZs/qvcZGeixAzahkWgmpsnCmF+1sxQw/XepyEuX73Knn2isIyg5uaZ+iG9OLnER6RPlO8xUGU74qb7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"customer id" = _t, #"customer name" = _t, #"customer type" = _t, #"ip address" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer id", Int64.Type}, {"customer name", type text}, {"customer type", type text}, {"ip address", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"ip address", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"ip address"}, {{"Count", each Table.RowCount(_), type number}, {"Detail", each _, type table}}),
    #"Expanded Detail" = Table.ExpandTableColumn(#"Grouped Rows", "Detail", {"customer id", "customer name", "customer type"}, {"customer id", "customer name", "customer type"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Detail",{{"customer id", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"customer id", "customer name", "customer type", "ip address", "Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Count", "Duplicated IP Count"}})
in
    #"Renamed Columns"

 

 

 

grouping.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.