Forum Discussion
taking total from another table by filtering column in second table
Hello,
I have 2 tables loaded in power query. I have to add a column in 1 st table to find utilization % (Actual hour/Target hour).
1st table(need to add column in this table)
| Employee | Target hour |
| A | 55 |
| B | 66 |
| C | 43 |
| D | 43 |
| E | 65 |
| F | 25 |
The second column:(need to take actual hour details from below table by filtering Error "No")
| Employee | Actual hours | Error |
| A | 17 | No |
| A | 8 | No |
| A | 12 | Yes |
| B | 16 | Yes |
| B | 9 | No |
| B | 4 | No |
| C | 10 | No |
| C | 15 | Yes |
| C | 18 | No |
| D | 9 | No |
| D | 4 | Yes |
| D | 26 | Yes |
| E | 20 | No |
| E | 25 | No |
| E | 2 | Yes |
| F | 8 | No |
| F | 12 | Yes |
| F | 13 | No |
Appreciate an early response 🙂
Here is the 1 way to do it.
let Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI1VYrViVZyAjLNzMBMZyDTxBjMdEEwXUEKIGrdgEwjIDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Target hour" = _t]), Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0BxJ++UqxOhC+BSrX0AhIRKYWgwWcQAJmaAKWCA0grgmC6wxSboDGN0XSDhZAstAF1TgXqHEw5SC+EbL1riABJAvAfFM0PpJ6N1T/uaH7DyxgDFUQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Actual hours" = _t, Error = _t]), ChangedType = Table.TransformColumnTypes(Table2,{{"Actual hours", type number}}), GroupedRows = Table.Group(ChangedType, {"Employee"}, {{"Actual hour", each List.Sum(Table.SelectRows(_, each ([Error] = "No"))[Actual hours]), type nullable text}}), #"Merged Queries" = Table.NestedJoin(Table1, {"Employee"}, GroupedRows, {"Employee"}, "Mrg", JoinKind.LeftOuter), #"Expanded Mrg" = Table.ExpandTableColumn(#"Merged Queries", "Mrg", {"Actual hour"}), ChangedType2 = Table.TransformColumnTypes(#"Expanded Mrg",{{"Target hour", type number}, {"Actual hour", type number}}), FINAL = Table.AddColumn(ChangedType2, "Utilization %", each [Actual hour]/[Target hour], Percentage.Type) in FINAL
1 Reply
Here is the 1 way to do it.
let Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI1VYrViVZyAjLNzMBMZyDTxBjMdEEwXUEKIGrdgEwjIDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Target hour" = _t]), Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0BxJ++UqxOhC+BSrX0AhIRKYWgwWcQAJmaAKWCA0grgmC6wxSboDGN0XSDhZAstAF1TgXqHEw5SC+EbL1riABJAvAfFM0PpJ6N1T/uaH7DyxgDFUQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Actual hours" = _t, Error = _t]), ChangedType = Table.TransformColumnTypes(Table2,{{"Actual hours", type number}}), GroupedRows = Table.Group(ChangedType, {"Employee"}, {{"Actual hour", each List.Sum(Table.SelectRows(_, each ([Error] = "No"))[Actual hours]), type nullable text}}), #"Merged Queries" = Table.NestedJoin(Table1, {"Employee"}, GroupedRows, {"Employee"}, "Mrg", JoinKind.LeftOuter), #"Expanded Mrg" = Table.ExpandTableColumn(#"Merged Queries", "Mrg", {"Actual hour"}), ChangedType2 = Table.TransformColumnTypes(#"Expanded Mrg",{{"Target hour", type number}, {"Actual hour", type number}}), FINAL = Table.AddColumn(ChangedType2, "Utilization %", each [Actual hour]/[Target hour], Percentage.Type) in FINAL