We've captured the moments from FabCon & SQLCon that everyone is talking about, and we are bringing them to the community, live and on-demand. Starts on April 14th. Register now
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!
Solved! Go to Solution.
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"
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"
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
| User | Count |
|---|---|
| 5 | |
| 3 | |
| 3 | |
| 2 | |
| 2 |
| User | Count |
|---|---|
| 11 | |
| 10 | |
| 7 | |
| 6 | |
| 5 |