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
Hi ricardocamargos,
the date format is right, the problem is that I have no idea how to create the table I described. I need to use the sum of Attendance for each user in each month as a numerator in another calculations.
Thanks for the advice, though :)
The reason is that I don't have powerBI in English and the format just looks different but it's the same.