SOLVED

Power Query - Matching code in rows for if then calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-3099939%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Matching%20code%20in%20rows%20for%20if%20then%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3099939%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20use%20Power%20Query%20to%20create%20a%20custom%20column%20based%20on%20a%20column%20value%20across%20matching%20codes%20in%20separate%20rows.%20For%20example%2C%20whenever%20rows%20have%20the%20same%20%22Code%22%2C%20I%20want%20to%20evaluate%20all%20%22Compliance%22%20values%20across%20the%20rows%20to%20determine%20an%20%22Overall%20Compliance%22%20column.%20I%20have%20included%20some%20sample%20data%20below%2C%20where%20I%20am%20trying%20to%20achieve%20Overall%20Compliance.%20Overall%20Compliance%20should%20only%20be%20%22Yes%22%20when%20all%20values%20for%20Compliance%20across%20each%20Code%20are%20Yes.%20Overall%20Compliance%20should%20be%20No%20if%20any%20value%20across%20the%20Code%20is%20No.%3C%2FP%3E%3CTABLE%20width%3D%22274%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3ECode%3C%2FTD%3E%3CTD%20width%3D%2280%22%3ECompliance%3C%2FTD%3E%3CTD%20width%3D%22130%22%3EOverall%20Compliance%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E123%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E123%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E123%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E456%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E456%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E456%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E789%3C%2FTD%3E%3CTD%3EYes%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E789%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E789%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3CTD%3ENo%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20do%20this%20with%20Power%20Query%3F%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3099939%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3100035%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Matching%20code%20in%20rows%20for%20if%20then%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3100035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1293677%22%20target%3D%22_blank%22%3E%40jtooke1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Grouped%20Rows%22%20%3D%20Table.Group(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%7B%22Code%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Count%22%2C%20each%20_%2C%20type%20table%20%5BCode%3Dnumber%2C%20Compliance%3Dtext%5D%7D%7D)%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Grouped%20Rows%22%2C%0A%20%20%20%20%20%20%20%20%22Overall%20Compliance%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20List.AllTrue(%20List.Transform(%20%5BCount%5D%5BCompliance%5D%2C%20each%20_%20%3D%20%22Yes%22%20))%0A%20%20%20%20%20%20%20%20%20%20%20%20then%20%22Yes%22%0A%20%20%20%20%20%20%20%20%20%20%20%20else%20%22No%22%20)%2C%0A%20%20%20%20%23%22Expanded%20Count%22%20%3D%20Table.ExpandTableColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%22Count%22%2C%0A%20%20%20%20%20%20%20%20%7B%22Compliance%22%7D%2C%20%7B%22Compliance%22%7D)%0Ain%0A%20%20%20%20%23%22Expanded%20Count%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

CodeComplianceOverall Compliance
123YesYes
123YesYes
123YesYes
456YesNo
456NoNo
456YesNo
789YesNo
789NoNo
789NoNo

 

Is there a way to do this with Power Query?

Thanks!

 

12 Replies

@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"
best response confirmed by jtooke1 (New Contributor)
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

@Sergei Baklan this works great, thank you!

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

@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"

@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.

@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.

odeComplianceOverall Compliance
123YesYes
123YesYes
123YesYes
456YesPartial
456NoPartial
456YesPartial
789YesPartial
789NoPartial
789NoPartial
1234N/AN/A
1234N/AN/A
1234N/AN/A
856NoNo
856NoNo
856NoNo

@jtooke1 

What's expected for [Code] 9999?

Screenshot.png

@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"
This works perfectly, thanks again!