cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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)

EmployeeTarget hour
A55
B66
C43
D43
E65
F25

The second column:(need to take actual hour details from below table by filtering Error "No")

EmployeeActual hoursError
A17No
A8No
A12Yes
B16Yes
B9No
B4No
C10No
C15Yes
C18No
D9No
D4Yes
D26Yes
E20No
E25No
E2Yes
F8No
F12Yes
F13No

 

Appreciate an early response 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

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

View solution in original post

1 REPLY 1
Syndicate_Admin
Administrator
Administrator

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

View solution in original post

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Users online (2,255)