Forum Discussion

jtooke1's avatar
jtooke1
Copper Contributor
Feb 01, 2022
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...
developer
excel
Formulas and Functions
power bi
  • Lorenzo's avatar
    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
Lorenzo's avatar
Lorenzo
Silver Contributor
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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 01, 2022

Lorenzo 

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"

Resources