Forum Discussion
Crow2525
Helper I
4 years agoConditional column based on lookup table of conditions
Task: I need to assign a group to the main dataset depending on a table of conditions. If a condition is satified at the top, then it will not be allocated into a future group. Main Dataset (Tab...
- 4 years ago
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
CODE FOR SOURCE
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lHyyczPA1LOiUVKsToIYd/80uJUJHH/osS8dIhACZAMyEnMSwVLBKQCVcCEQ4oSM/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Animal = _t, Vehicle = _t]), ListValue = List.Buffer(Conditions[Value]), ListSegment = List.Buffer(Conditions[Segment]), #"Added Custom" = Table.AddColumn(Source, "Custom", each try ListSegment{List.PositionOf(ListValue,[Fruit])} otherwise try ListSegment{List.PositionOf(ListValue,[Animal])} otherwise try ListSegment{List.PositionOf(ListValue,[Vehicle])} otherwise null) in #"Added Custom"CODE FOR CONDITIONS
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIi58QSIBmQk5iXqhSrE61kBOKlJhZBZMFCxsgqQ4oSM/PAwiZAnmNBQU4qklJTCNs3v7Q4FSZmBmT4FyXmpaeiWxcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Fruit = _t, Animal = _t, Vehicle = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Segment", Int64.Type}, {"Fruit", type text}, {"Animal", type text}, {"Vehicle", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Segment"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> null)) in #"Filtered Rows"
Vijay_A_Verma
Most Valuable Professional
4 years agoSee the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
CODE FOR SOURCE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lHyyczPA1LOiUVKsToIYd/80uJUJHH/osS8dIhACZAMyEnMSwVLBKQCVcCEQ4oSM/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Animal = _t, Vehicle = _t]),
ListValue = List.Buffer(Conditions[Value]),
ListSegment = List.Buffer(Conditions[Segment]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each try ListSegment{List.PositionOf(ListValue,[Fruit])} otherwise try ListSegment{List.PositionOf(ListValue,[Animal])} otherwise try ListSegment{List.PositionOf(ListValue,[Vehicle])} otherwise null)
in
#"Added Custom"CODE FOR CONDITIONS
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIi58QSIBmQk5iXqhSrE61kBOKlJhZBZMFCxsgqQ4oSM/PAwiZAnmNBQU4qklJTCNs3v7Q4FSZmBmT4FyXmpaeiWxcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Fruit = _t, Animal = _t, Vehicle = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Segment", Int64.Type}, {"Fruit", type text}, {"Animal", type text}, {"Vehicle", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Segment"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> null))
in
#"Filtered Rows"
- Crow25254 years ago
Helper I
Wow...
Give me an hour to figure out what you've done! I'll return and flag it as a solution, once I've verified it.