powerquery group by rows with formula on multiple columns

Question

I am trying to group/merge two rows by dividing the values in each based on another column (Eligible) value.

From the initial raw data, I have reached this level with different steps (by unpivoting etc.) in power query.

Now I need to have a ratio per employee (eligible/not-eligible) for each month.

So for employee A, "Jan-14" will be -10/(-10 + -149) and so on. Any ideas will be appreciated. Thanks

Really appreciate the response. Interestingly, I have used your other answer to reach this stage from the raw data.

Since we are calculating how much time an employee worked on eligible activities each month so We will be grouping on the Employee. Employee name was just for reference which I took out and later will join with employee query to get the names if required. There was a typo in the image, the last row should also be an employee with id 2.

So now when there is a matching row, we use the formula to calculate the percentage of time spent on eligible activities but

If there isn't a matching row with eligible=1, then the outcome should be 0

if there isn't a matching row with eligible-0, then the outcome should be 1 (100%)

horseyride horseyride · Accepted Answer · 2020-10-22T19:16:18

Try this and modify as needed. It assumes you are starting with all Eligible=0 and will only pick up matching Eligible=1. If there is a E=1 without E=0 it is removed. Also assumes we match on both Employee and EmployeeName

~ ~ ~ ~ ~

Click select the first three columns (Employee, EmployeeName, Eligible), right click .... Unpivot other other columns

Add custom column with name "One" and formula =1+[Eligible]

Merge the table onto itself, Home .. Merge Queries... with join kind Left Outer

Click to match Employee, EmployeeName and Attribute columns in the two boxes, and match One column in the top box to the Eligible Column in the bottom box

In the new column, use arrows atop the column to expand, choosing [x] onlt the Value column. Make the name of the column: Custom.Value

Add column .. custom column ... formula = [Custom.Value] / ( [Custom.Value] + [Value])

Filter Eligible to only pick up the zeroes using the arrow atop that column

Remove extra columns

Click select Attribute column, Transform ... pivot ... use custom as the values column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Employee", "EmployeeName", "Eligible"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "One", each 1+[Eligible]),
#"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Employee", "EmployeeName", "Attribute", "One"},#"Added Custom",{"Employee", "EmployeeName", "Attribute", "Eligible"},"Added Custom",JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Value"}, {"Custom.Value"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Added Custom", "Custom", each [Custom.Value]/([Custom.Value]+[Value])),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Eligible] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Eligible", "Value", "One", "Custom.Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom", List.Sum)
in #"Pivoted Column"

powerquery group by rows with formula on multiple columns

1 Answers