Forum Discussion
Solved
pivot table
Hello, I've got a problem with one formula. This is the table I have: One row is one day that the user was present (each user is there multiple times). What I need is a new table that w...
bharka,
Add a blank query in Power BI Desktop, paste the following code to the Advanced Editor of the blank query to test the process.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7BDQAhCATAXnybyALmoBZj/22c5k4e8trHhF3GKGigxgQrtaCr6E6iMus2CwKZr+QgDSJ/+koJYk+NfAzZJFf+a9BDfq+hf8QNnBrtmNyNgjhLa7KfnC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, User_ID = _t, Attendace = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User_ID", Int64.Type}, {"Attendace", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date])), #"Grouped Rows" = Table.Group(#"Added Custom", {"Month", "User_ID"}, {{"sum of attendance", each List.Sum([Attendace]), type number}}) in #"Grouped Rows"
Regards,
Lydia
bharka,
Add a blank query in Power BI Desktop, paste the following code to the Advanced Editor of the blank query to test the process.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7BDQAhCATAXnybyALmoBZj/22c5k4e8trHhF3GKGigxgQrtaCr6E6iMus2CwKZr+QgDSJ/+koJYk+NfAzZJFf+a9BDfq+hf8QNnBrtmNyNgjhLa7KfnC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, User_ID = _t, Attendace = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User_ID", Int64.Type}, {"Attendace", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Month", "User_ID"}, {{"sum of attendance", each List.Sum([Attendace]), type number}})
in
#"Grouped Rows"
Regards,
Lydia
Thank you 😊