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

[erajka]

I have two tables, one (ID-Table) with IDs, Team Name, Area Name and another (ComplianceData) with duplicates of IDs with their different compliance states/count of compliant items. I want to merge these two together to the outcome below.

ID-Table:

ID	Team Name	Area Name
1	A	AA
2	B	BB
3	C	CC

ComplianceData:

ID	Compliance State	Number of Items
1	Noncompliant	22
1	Compliant	300
1	Noncompliant	23
2	Compliant	499
2	Noncompliant	9
2	Noncompliant	1
3	Compliant	70
3	Noncompliant	30

Final Table Wanted:

ID	TeamName	AreaName	Compliance State
1	A	AA	Compliant	300
1	A	AA	Noncompliant	45
2	B	BB	Compliant	499
2	B	BB	Noncompliant	10
3	C	CC	Compliant	70
3	C	CC	Noncompliant	30

I have tried merging queries together, but then I am down to just one row for each ID...Is it possible to have 2 rows per ID, one for Compliant and one for Noncompliant? 

[dax · Accepted solution]

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.

[Anonymous]

Select ComplianceData

Go to Home 

Merge Queries 

Select ID on both ComplianceData: and ID-Table then hit enter. 

Then expand out ID-table tab 

Result 

Reorder if needed