Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bharka
Regular Visitor

pivot table

Hello,

 

I've got a problem with one formula. This is the table I have:

 

pbi.jpg

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. 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

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

2.JPG1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

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

2.JPG1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you 😊
ricardocamargos
Continued Contributor
Continued Contributor

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.