Forum Discussion
Combine 2 data tables on multiple criteria
- 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.
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.
I got this to work, Thank you! One thing for future users to know that was tripping me up at first was that the column names that you are trying to connect on need to be the same.
i.e The project table and actual table both need these columns to work "Employee" and "Project"