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.
You can merge the actual table into the projected table in Power Query using the Employee and Project columns as the link. You would then expand the actual hours column from the merged table.
Thanks for the help!
To clarify, I am supposed to use a full outer join for this merge, correct? When I do this, I get null values for employee and project name. I could bring in the second table columns for these names and then do a calculated column to basically say if one is blank, grab the name from the other and then remove the old columns once the new calculated one is there. But this seems like a lot of steps. is there not a way where it can fill the null values in without additional steps / calculated columns?