Forum Discussion
Solved
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 eval...
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
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"- This works perfectly, thanks again!
jtooke1 , glad to help