Merging Rows from many to 2 per ID for a specific value

Hi erajka , 

You could use M code or DAX to achieve this goal.

DAX: create relationship between two table, then show them in table.

M code: use merge based on ID , then use groupby to get sum value.

let
    Source = Table.NestedJoin(#"ID-Table", {"ID"}, ComplianceData, {"ID"}, "ComplianceData", JoinKind.LeftOuter),
    #"Expanded ComplianceData" = Table.ExpandTableColumn(Source, "ComplianceData", {"Compliance State", "Number of Items"}, {"Compliance State", "Number of Items"}),
    #"Grouped Rows" = Table.Group(#"Expanded ComplianceData", {"ID", "Team Name", "Area Name", "Compliance State"}, {{"sum", each List.Sum([Number of Items]), type number}})
in
    #"Grouped Rows"

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.