Power query create new column X where: IF any value in column A is true, return true for all X

[NNaj]

Hi Everyone,

Column1 is the company name. Each company has many products (not shown). Each product has a rating. I want to create a new column that looks at each company and returns fail, watchlist, or pass based on the ratings on the products.

If any product fails, return fail for every row in this new column

If any product is a watch list, return fail.

If any product is green (with no fails or watchlists) return pass for all rows for that company

I need to do this on power query because I need to use this column in a left outer join with another table. Thank you in advance for all your help.

[AnkitBI]

Hi @NNaj - If you can provide sample data, then I will be able to help.

[NNaj]

Attached is some sample data, cant seem to be able to atach an excel sheet.

The new column is what I need to create on power query

[v-diye-msft · Accepted solution]

Hi @NNaj 

Let me know if you'd like to get below results:

Add below measures:

Fail = CALCULATE(MAX('Table (3)'[Rating]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Company]),[Rating]="Fail"))

No rating = CALCULATE(MAX('Table (3)'[Rating]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Company]),[Rating]="No Rating"))

Pass = CALCULATE(MAX('Table (3)'[Rating]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Company]),[Rating]="Pass"))

Watch list = CALCULATE(MAX('Table (3)'[Rating]),FILTER(ALLEXCEPT('Table (3)','Table (3)'[Company]),[Rating]="Watch list"))

 Final measure:

Measure = SWITCH(TRUE(),[Fail]="Fail","Fail",[Watch list]="Watch list","Fail",[Pass]="Pass","Pass",[No rating]="No Rating","No Rating")

Pbix attached.

[NNaj]

Thank you for this. I was wondering if there is a way to do this in Power Query in the edit queries page. I need to do it like this because I need to merge this table with another using a left outer join and at the moment when I try to create the join The measure is not visible in the table

#

[AnkitBI]

Hi @NNaj  Check below solution for PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg1R0lEyBGK3xMwcpVgdqIgREAckFhcjRIyB2C9fISixJDMvHSFsgiHs6AQUMkXWDxYxw67QHLuwBYawkyNQyBKIwxNLkjMUcjKLSxDihgbY1Rsa4hA3whB3dwGJY/oSIo7pTYi4Kap4LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, Product = _t, Rating = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Product", Int64.Type}, {"Rating", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Company"}, {{"AllRows", each _, type table [Company=text, Product=number, Rating=text]}}),
    Custom1 = Table.AddColumn(#"Grouped Rows","FailPass",each if List.ContainsAny([AllRows][Rating],{
"Fail","Watch list"}) then "Fail" else if List.Contains([AllRows][Rating],"Pass") then "Pass" else "No Rating"),
    #"Expanded AllRows" = Table.ExpandTableColumn(Custom1, "AllRows", {"Product", "Rating"}, {"Product", "Rating"})
in
    #"Expanded AllRows"

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.