Combine 2 data tables on multiple criteria

[Jbrunson09]

All,

I have 2 data tables. 

1. 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.
2. The second table to of employee names and how many hours they ACTUALLY billed to projects in the week.

What I want is to combine these tables into a single table where I can see ALL of the projects that were PROJECTED & ACTUALLY billed. I cant figure out if I need to merge as a new table and do a full outer join or something else completly. Any help would be appreicated.

Example of what this could look like (the real data would have multiple employee names).

Thank you,

[jgeddes · Accepted solution]

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.

[Jbrunson09]

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"

[jgeddes]

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.

[Jbrunson09]

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?