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

developer

excel

Formulas and Functions

power bi

Lorenzo

Feb 01, 2022

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

Forum Discussion

Power Query - Matching code in rows for if then calculation

Resources