Forum Discussion
Custom Column to return value depending on rows value
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, the...
Hi Anonymous
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"
Hi Anonymous
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"