pivot table

[bharka]

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 will contain User_ID, month and sum of Attendance for each user in the particular month. I need the table for further calculations but I wasn't able to figure it out. 

Thanks for any help. 

[Anonymous · Accepted solution]

@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]

Thank you 😊

[ricardocamargos]

Hi @bharka,

I think once u have the data imported, the Date column will be recognized as text.

If you take a look to the Applied Steps, the second step changes the data types, the date column now is decimal.

To use this column as date, you just need to delete this second step and click on Date column and convert it to Date type. Now you are able to do some calculations by date.

I hope it helps you,

Ricardo

[bharka]

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 🙂

[bharka]

The reason is that I don't have powerBI in English and the format just looks different but it's the same.