Forum Discussion
Mobile Data Usage Disparities
Hello,
Using power bi I am trying to find the largest disparities between users monthly date usages. I am having trouble trying to get this.
Here is an example data set, I am not sure if I should add a column to put the calculations in. I want to be able to have show through a visual too.
| Username | Data usage (GBs) | Month |
| csherborn0 | 36 | Jan |
| llorenzetti1 | 84 | Jan |
| thuddlestone2 | 86 | Jan |
| tleseka | 56 | Jan |
| csherborn0 | 80 | Feb |
| llorenzetti1 | 72 | Feb |
| thuddlestone2 | 63 | Feb |
| tleseka | 69 | Feb |
| csherborn0 | 92 | Apr |
| llorenzetti1 | 83 | Apr |
| thuddlestone2 | 74 | Apr |
| tleseka | 66 | Apr |
Any help would be great!
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY8xDoMwDEXv4pkBEprAyMLAFSIGKJaoiBIU3KWnbzIABrF4eE9fTzYG3tuMYfTB5ZCBVPF0g4M+M2CtD+h+SPQpIq5K5mj+TpPFjbxDkSQfUhS4DJG8OL6UqnRaHJ9KWjB3LynJ5VFSNcOXUp1WzRoef5LM3Uu65PIsqR33fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Username = _t, #"Data usage (GBs)" = _t, Month = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Username", type text}, {"Data usage (GBs)", Int64.Type}, {"Month", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Username"}, {{"Max", each List.Max([#"Data usage (GBs)"]), type nullable number}, {"Min", each List.Min([#"Data usage (GBs)"]), type nullable number},{"Diff", each List.Max([#"Data usage (GBs)"])-List.Min([#"Data usage (GBs)"]), type nullable number}}) in #"Grouped Rows"
3 Replies
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY8xDoMwDEXv4pkBEprAyMLAFSIGKJaoiBIU3KWnbzIABrF4eE9fTzYG3tuMYfTB5ZCBVPF0g4M+M2CtD+h+SPQpIq5K5mj+TpPFjbxDkSQfUhS4DJG8OL6UqnRaHJ9KWjB3LynJ5VFSNcOXUp1WzRoef5LM3Uu65PIsqR33fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Username = _t, #"Data usage (GBs)" = _t, Month = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Username", type text}, {"Data usage (GBs)", Int64.Type}, {"Month", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Username"}, {{"Max", each List.Max([#"Data usage (GBs)"]), type nullable number}, {"Min", each List.Min([#"Data usage (GBs)"]), type nullable number},{"Diff", each List.Max([#"Data usage (GBs)"])-List.Min([#"Data usage (GBs)"]), type nullable number}}) in #"Grouped Rows"I got this to work for the table that I put up there. This is what I want! How would I change the source to the files that I am using?
And would I have to get rid of anything else in the text?Get the data of your source in PQ.
Select entire table and do a changed type to set data type.
Open Adavanced Editor and paste following code after Changed type step (don't forget comma after Changed type step)
#"Grouped Rows" = Table.Group(#"Changed Type", {"Username"}, {{"Max", each List.Max([#"Data usage (GBs)"]), type nullable number}, {"Min", each List.Min([#"Data usage (GBs)"]), type nullable number},{"Diff", each List.Max([#"Data usage (GBs)"])-List.Min([#"Data usage (GBs)"]), type nullable number}}) in #"Grouped Rows"
