Custom Column to return value depending on rows value

Question

Hi Everyone,&nbsp;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?&nbsp;&nbsp;Thanks!&nbsp;SKUStatusValueCheck1001ATRUETRUE1001BTRUETRUE1001CTRUETRUE1001DFALSETRUE1001ETRUETRUE1001FTRUETRUE1001GUNKNOWNTRUE1002AFALSEFALSE1002BFALSEFALSE1003AUNKNOWNTRUE&nbsp;1003BUNKNOWNTRUE&nbsp;1003CTRUETRUE&nbsp;1004AUNKNOWNUNKNOWN1004BUNKNOWNUNKNOWN

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
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?

&nbsp;
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"
&nbsp;

Forum Discussion

Custom Column to return value depending on rows value

1 Reply

Related content

Circular dependency

Custom Column

Remove circular dependency from calculated column

Circular dependency calculated column

Dax query dependency

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