Custom Column to return value depending on rows value

[dannyboc]

Hi Everyone,

I have a table of many SKU's with a status column, and its value. I want to create a custom column that evaluates the value of all rows per SKU. When at least one status is TRUE, then the value of Check column is TRUE, if all the values are FALSE, then false, and if all the values are Uknown, then uknown. How can I create that column using Power query? 

Thanks!

SKU	Status	Value	Check
1001	A	TRUE	TRUE
1001	B	TRUE	TRUE
1001	C	TRUE	TRUE
1001	D	FALSE	TRUE
1001	E	TRUE	TRUE
1001	F	TRUE	TRUE
1001	G	UNKNOWN	TRUE
1002	A	FALSE	FALSE
1002	B	FALSE	FALSE
1003	A	UNKNOWN	TRUE
1003	B	UNKNOWN	TRUE
1003	C	TRUE	TRUE
1004	A	UNKNOWN	UNKNOWN
1004	B	UNKNOWN	UNKNOWN

[Vera_33 · Accepted solution]

Hi @dannyboc 

One way to do it, group by SKU and check the value, then join. Do you have cases that not all are FALSE or UNKNOWN, say 1 FALSE + 1 UNKNOWN?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUXIE4pCgUFelWB24mBMWMWcsYi5A7OboE4wi6IpFoRsWMXcgDvXz9vMP94MJG0Hdg2KmEdRBKILGUJVoBhhD1WIRRne/CXYTTNBNiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, Status = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", Int64.Type}, {"Status", type text}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, {{"Check", each if List.Contains( [Value], "TRUE") then "TRUE" 
else List.Distinct([Value]){0}}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"SKU"}, #"Grouped Rows", {"SKU"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Check"}, {"Check"})
in
    #"Expanded Grouped Rows"