Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 id | customer name | customer type | ip address |
0001 | Mary | Good | 10.23.10.15 |
0002 | John | Good | 123.45.16.250 |
0003 | Julia | Bad | 1.0.0.1 |
0004 | Coby | Normal | 35.26.10.26 |
0005 | Michael | Normal | 162.17.18.250 |
0006 | John | Normal | 15.16.158.16 |
0007 | Polly | Good | 1.0.0.1 |
0008 | Karen | Good | 10.23.10.15 |
0009 | Carol | Good | 10.23.10.15 |
0010 | Robert | Unknown | 122.46.15.16 |
Solved! Go to Solution.
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")
For more details, please check the pbix as attached.
Regards,
Frank
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")
For more details, please check the pbix as attached.
Regards,
Frank
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"
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |