Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Please help me as I am new to Power Query and I dont know how to calculate this following:
I have to calculate the Whole Time Equivalent for 25,000 staff. It is relatively easy when the person has one employment record however I have people with 2 and 3 employment records. and I have calculate the combined total per person. I have attached a sample of what I am trying to achieve i.e. to calcultate the total WTE for each scenario ie a person may have one post (person A below) or multiple posts (persons B and C with 2 and 3 posts respectively) This is most likely very easy but I have tried to do some research myself and can't find an answer. Any help would be greatly appreciated. Many thanks in advance.
Person A has 1 post. WTE for that post is 1.0 making a total of 1.0
Person B has 2 posts. WTE for post 1 is 0.5 and for post 2 0.3 making a combined total of 0.8
Person C has 3 posts. WTE for post 1 is 0.3, for post 2 is 0.5 and for post 3 is 0.2 making a combined total of 1.0
Solved! Go to Solution.
@ciaranmcmullan Doesn't make the least bit of sense. 🙂
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lEyVIrVgQs4AQX0TDFEjJFFnLGLmGKIGCnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, WTE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"WTE", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"WTE", each List.Sum([WTE]), type nullable number}})
in
#"Grouped Rows"
Hi Greg thanks for the super fast response. thank you for the advice, I grouped by person and the summed up the WTE however it gives me the Summed up WTE against each row for those with multiple positions whereas I need only one total. I hope that makes sense?
@ciaranmcmullan Doesn't make the least bit of sense. 🙂
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lEyVIrVgQs4AQX0TDFEjJFFnLGLmGKIGCnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, WTE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"WTE", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"WTE", each List.Sum([WTE]), type nullable number}})
in
#"Grouped Rows"
Sorry if I was a little unclear. In Excel I use the following formula which calculates it for me and essentially I am looking Power Query to replicate it exactly.
=IF(COUNTIF($A$2:A2,A2)=COUNTIF(A:A,A2),SUMIF(A:A,A2,B:B),"")
A represents the column with the person and B is the column with each WTE
The result is that it adds the WTE for each person and gives me one total. EG
0.21 | |
1.00 | 1.21 |
0.33 | |
0.67 | 1.00 |
0.27 | |
0.67 | 0.93 |
@ciaranmcmullan Sounds like you need to do a Group By in Power Query based upon the "Person" column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.