taking total from another table by filtering column in second table

[sahadhk]

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 🙂

[Jakinta · Accepted solution]

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