Power Query - Matching code in rows for if then calculation

[jtooke1]

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!

[#2]

@jtooke1 

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"

[#3 · Accepted solution]

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

[#4]

@Sergei Baklan this works great, thank you!

[#5]

You seem to have implemented @Sergei Baklan proposal but marked mine as Best response...

[#6]

@L z. 

Yes, 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"

[#7]

@jtooke1 , you are welcome

[#8]

@L z. 

---

@L z. wrote:
You seem to have implemented @Sergei Baklan proposal but marked mine as Best response...

---

No problem, doesn't matter. They are all workable.

[#9]

@Sergei Baklan 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

[#10]

@jtooke1 

What's expected for [Code] 9999?

[#11]

@jtooke1 

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"

[#12]

This works perfectly, thanks again!

[#13]

@jtooke1 , glad to help