Forum Discussion
Merging Rows from many to 2 per ID for a specific value
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?
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
