Forum Discussion

bharka's avatar
bharka
Regular Visitor
8 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

ricardocamargos's avatar
ricardocamargos
Continued Contributor
8 years ago

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's avatar
bharka
Regular Visitor
8 years ago

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's avatar
    bharka
    Regular Visitor
    8 years ago

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