Forum Discussion

Jbrunson09's avatar
Jbrunson09
Frequent Visitor
2 years ago
Solved

Combine 2 data tables on multiple criteria

All,   I have 2 data tables.  A table of employee names and the projects they are assigned along with how many hours they project to bill that project in a week. The second table to of employee ...
  • jgeddes's avatar
    2 years ago

    I created some sample data and was able to get your desired result using appending queries and table grouping. 

    Projected Table

    Actual Table

    Result

     

    Code

    let
        projectedSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXIEYiOlWB24gBMQGyMLOKOrcAFiU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Project = _t, #"Projected Hours" = _t]),
        changeProjectedTypes = Table.TransformColumnTypes(projectedSource,{{"Employee", type text}, {"Project", type text}, {"Projected Hours", Int64.Type}}),
        actualSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXIEYmOlWB24gAsQmyILuAGxIbKAK0RLLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Project = _t, #"Actual Hours" = _t]),
        changeActualTypes = Table.TransformColumnTypes(actualSource,{{"Employee", type text}, {"Project", type text}, {"Actual Hours", Int64.Type}}),
        #"Appended Query" = Table.Combine({changeProjectedTypes, changeActualTypes}),
        #"Grouped Rows" = Table.Group(#"Appended Query", {"Employee", "Project"}, {{"Projected Hours", each List.Sum([Projected Hours]), type nullable number}, {"Actual Hours", each List.Sum([Actual Hours]), type nullable number}})
    in
        #"Grouped Rows"

     

    Appending the queries together and then grouping by Employee and Project while taking the sum of both projected and actual hours is what makes this work.

    Hope this helps.