CODEWITHSUNDEEP
powerbipowerquery
Josh Lambert
Josh Lambert

Reputation: 47

Create a Conditional Column in Power Query based on a range in another column

I'm looking to create a conditional column that is based on a different column within a range of cells.

If the "Status" column contains the word "Finalized" for any of the cells in a range that all have the same value in a separate column (Number), then in the "Desired Output" column, return "Finalized", if not "Current".

Here is an example of what I mean:

enter image description here

So, for Number 123456, there is one cell in the "Status" column that says "Finalized". Since that's the case, the new column would say "Finalized" for each of those cells in the row with the same Number. If the Status column doesn't say "Finalized", then it would say "Current".

Thank you!

Upvotes: 0

Views: 593

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

Here's a different way of checking if there is a Finalized in that column:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Stage", type text}, {"Status", type text}}),
   
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Number"}, {
        {"Desired Output", (t)=>Table.AddColumn(t,"Desired Output", 
                each if List.Contains(t[Status], "Finalized") then "Finalized" else [Status])}}),
   
    #"Expanded Desired Output" = Table.ExpandTableColumn(#"Grouped Rows", "Desired Output", {"Stage", "Status", "Desired Output"}),
   
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Desired Output",{
        {"Number", Int64.Type}, 
        {"Stage", type text}, 
        {"Status", type text}, 
        {"Desired Output", type text}})
in
    #"Changed Type1"

enter image description here

Upvotes: 0

horseyride
horseyride

Reputation: 21393

One way

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source,"Desired",(x)=>if Table.RowCount(Table.SelectRows(Source, each [Number]=x[Number] and [Status]="Finalized"))=1 then "Finalized" else x[Status])
in #"Added Custom"

or, sometimes faster, grouping

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Number"}, {{"data", each 
        let a=Table.RowCount(Table.SelectRows(_, each [Status] = "Finalized"))
        in Table.AddColumn(_,"Desired", each if a=1 then "Finalized" else [Status])
, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Stage", "Status", "Desired"}, {"Stage", "Status", "Desired"})
in  #"Expanded data"

enter image description here

Upvotes: 0

Related Questions