Forum Discussion
Power Query - Matching code in rows for if then calculation
Hi Everyone,
I am looking to use Power Query to create a custom column based on a column value across matching codes in separate rows. For example, whenever rows have the same "Code", I want to evaluate all "Compliance" values across the rows to determine an "Overall Compliance" column. I have included some sample data below, where I am trying to achieve Overall Compliance. Overall Compliance should only be "Yes" when all values for Compliance across each Code are Yes. Overall Compliance should be No if any value across the Code is No.
| Code | Compliance | Overall Compliance |
| 123 | Yes | Yes |
| 123 | Yes | Yes |
| 123 | Yes | Yes |
| 456 | Yes | No |
| 456 | No | No |
| 456 | Yes | No |
| 789 | Yes | No |
| 789 | No | No |
| 789 | No | No |
Is there a way to do this with Power Query?
Thanks!
Hi jtooke1
Another approach assumind data in Excel Table1
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ChangedTypes = Table.TransformColumnTypes(Source, {{"Code", Int64.Type}, {"Compliance", type text}} ), GroupedRows = Table.Group(ChangedTypes, {"Code"}, {"CodeTable", each let AnyNo = Table.SelectRows(_, each Text.Lower([Compliance]) = "no") in Table.AddColumn(_, "Overall Compliance", each if Table.IsEmpty(AnyNo) then "Yes" else "No", type text ), type text } ), CombinedTables = Table.Combine(GroupedRows[CodeTable]) in CombinedTables
12 Replies
Hi jtooke1
Another approach assumind data in Excel Table1
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ChangedTypes = Table.TransformColumnTypes(Source, {{"Code", Int64.Type}, {"Compliance", type text}} ), GroupedRows = Table.Group(ChangedTypes, {"Code"}, {"CodeTable", each let AnyNo = Table.SelectRows(_, each Text.Lower([Compliance]) = "no") in Table.AddColumn(_, "Overall Compliance", each if Table.IsEmpty(AnyNo) then "Yes" else "No", type text ), type text } ), CombinedTables = Table.Combine(GroupedRows[CodeTable]) in CombinedTablesYes, at least from performance point of view better to work with tables, not lists. One more variant
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group( Source, {"Code"}, {{"Count", each _, type table [Code=number, Compliance=text]}}), #"Add Overall" = Table.AddColumn( #"Grouped Rows", "Overall Compliance", each if Table.MatchesAllRows( [Count], each [Compliance] = "Yes" ) then "Yes" else "No", type text ), #"Expand it" = Table.ExpandTableColumn( #"Add Overall", "Count", {"Compliance"}, {"Compliance"}) in #"Expand it"
As variant
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group( Source, {"Code"}, {{"Count", each _, type table [Code=number, Compliance=text]}}), #"Added Custom" = Table.AddColumn( #"Grouped Rows", "Overall Compliance", each if List.AllTrue( List.Transform( [Count][Compliance], each _ = "Yes" )) then "Yes" else "No" ), #"Expanded Count" = Table.ExpandTableColumn( #"Added Custom", "Count", {"Compliance"}, {"Compliance"}) in #"Expanded Count"SergeiBaklan I have a follow up question around this, if I am looking to adjust the code to produce the below results, is that possible also? Essentially, I am looking to add a level where if any Compliance for each Code = No, then Overall Compliance will equal "Partial", and where Compliance all is equal to one value then Overall Compliance will equal that value, no matter what it may be.
ode Compliance Overall Compliance 123 Yes Yes 123 Yes Yes 123 Yes Yes 456 Yes Partial 456 No Partial 456 Yes Partial 789 Yes Partial 789 No Partial 789 No Partial 1234 N/A N/A 1234 N/A N/A 1234 N/A N/A 856 No No 856 No No 856 No No That's practically the same
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group( Source, {"Code"}, {{"Count", each _, type table [Code=number, Compliance=text]}}), #"Add Overall" = Table.AddColumn( #"Grouped Rows", "Overall Compliance", each if Table.MatchesAllRows( [Count], each [Compliance] = "Yes" ) then "Yes" else if Table.MatchesAllRows( [Count], each [Compliance] = "No" ) then "No" else if Table.MatchesAllRows( [Count], each [Compliance] = "N/A" ) then "N/A" else "Partial", type text ), #"Expand it" = Table.ExpandTableColumn( #"Add Overall", "Count", {"Compliance"}, {"Compliance"}) in #"Expand it"
SergeiBaklan this works great, thank you!
jtooke1 , you are welcome
