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 (Table Name: Source):FruitAnimalVehicleAppleLionCarAppleMouseCarOrangeCatPlanePearCatTrain Condition Dataset (Table Name: Conditions):SegmentFruitAnimalVehicle1 CatPlane2Pear 3 CatTrain4Apple 5 Mouse 6OrangeCatPlane Ideal Output:FruitAnimalVehicleSegment GroupAppleLionCar4AppleMouseCar4OrangeCatPlane6PearCatTrain2 I'm struggling to write this and I don't want to resort to hard-coding a long conditional entry with 35 groups that are likely to change / be adjusted. Pseudo code is:Table.AddColumn(Source, "Segment Group", For each row where y = current row numberIf Source[Fruit](y) = Conditions[Fruit](y) AND Source[Animal](y) = Conditions[Animal](y) AND Source[Vehicle](y) = Conditions[Vehicle](y) then Conditions[Segment](y)Next y

Helper I

4 years ago

Solved

Conditional 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 (Table Name: Source):

Fruit	Animal	Vehicle
Apple	Lion	Car
Apple	Mouse	Car
Orange	Cat	Plane
Pear	Cat	Train

Condition Dataset (Table Name: Conditions):

Segment	Fruit	Animal	Vehicle
1		Cat	Plane
2	Pear
3		Cat	Train
4	Apple
5		Mouse
6	Orange	Cat	Plane

Ideal Output:

Fruit	Animal	Vehicle	Segment Group
Apple	Lion	Car	4
Apple	Mouse	Car	4
Orange	Cat	Plane	6
Pear	Cat	Train	2

I'm struggling to write this and I don't want to resort to hard-coding a long conditional entry with 35 groups that are likely to change / be adjusted.

Pseudo code is:

Table.AddColumn(Source, "Segment Group", 
For each row where y = current row number
If Source[Fruit](y) = Conditions[Fruit](y) AND Source[Animal](y) = Conditions[Animal](y) AND Source[Vehicle](y) = Conditions[Vehicle](y) then Conditions[Segment](y)
Next y

Need Help

Vijay_A_Verma

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"

7 Replies

Vijay_A_Verma

Most Valuable Professional

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"

Crow2525
Helper I
4 years ago
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.

Crow2525

Helper I

4 years ago

Hi Vijay,

The List.Position seems to works on a column basis rather than row basis. Is that correct?

#"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)

It doesn't work how I'd need it to. Where in effect I have an AND statement between each column and an OR statement between each row.

Vijay_A_Verma

Most Valuable Professional

4 years ago

With my code what output you get and what is the output needed, then accordingly I can suggest you the solution.

Crow2525

Helper I

4 years ago

Hi Vijay,

Hoping you can help me find out where I'm getting this wrong.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POLICY_TYP", type text}, {"SCHEME FLAG", type text}, {"ANZSIC_SubDiv_Desc", type text}, {"ANZSIC_Group_Desc", type text}, {"ANZSIC_Code_Desc", type text}}),
    ListValue = List.Buffer(SegmentationConditions[Value]),
    ListSegment = List.Buffer(SegmentationConditions[Segmentation.1]),
    Custom1 = Table.AddColumn(#"Changed Type", "Custom1", 
    each try ListSegment{List.PositionOf(ListValue,[ANZSIC_SubDiv_Desc])} 
    otherwise try ListSegment{List.PositionOf(ListValue,[ANZSIC_Code_Desc])} 
    otherwise try ListSegment{List.PositionOf(ListValue,[ANZSIC_Group_Desc])}
    otherwise  try ListSegment{List.PositionOf(ListValue,[SCHEME FLAG])} 
    otherwise  try ListSegment{List.PositionOf(ListValue,[POLICY_TYP])} 
    otherwise  try ListSegment{List.PositionOf(ListValue,[SCHEME NAME])}     
    otherwise null, Text.Type)
in
    Custom1

let
    Source = Excel.CurrentWorkbook(){[Name="SegmentationConditions"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-AU"),{"Index", "Segmentation"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Segmentation.1"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns", {"Segmentation.1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Value", Text.Upper, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Uppercased Text","*","",Replacer.ReplaceValue,{"Value"})
in
    #"Replaced Value"

Data:

POLICY_TYP	SCHEME FLAG	ANZSIC_SubDiv_Desc	ANZSIC_Group_Desc	ANZSIC_Code_Desc
PID	FALSE	BUSINESS SERVICES	TECHNICAL SERVICES	ARCHITECTURAL SERVICES
PID	TRUE	BUSINESS SERVICES	TECHNICAL SERVICES	ARCHITECTURAL SERVICES
PID	FALSE	BUSINESS SERVICES	TECHNICAL SERVICES	ARCHITECTURAL SERVICES
PID	FALSE	BUSINESS SERVICES	LEGAL AND ACCOUNTING SERVICES	ACCOUNTING SERVICES
PID	TRUE	BUSINESS SERVICES	LEGAL AND ACCOUNTING SERVICES	ACCOUNTING SERVICES
MNL	FALSE	CONSTRUCTION TRADE SERVICES	INSTALLATION TRADE SERVICES	PLUMBING SERVICES
DOL	FALSE	CONSTRUCTION TRADE SERVICES	INSTALLATION TRADE SERVICES	PLUMBING SERVICES
DOL	FALSE	AGRICULTURE	HORTICULTURE AND FRUIT GROWING	GRAPE GROWING
PID	FALSE	HEALTH SERVICES	VETERINARY SERVICES	VETERINARY SERVICES
ICT	FALSE	BUSINESS SERVICES	LEGAL AND ACCOUNTING SERVICES	COMPUTER CONSULTANCY SERVICES
PID	FALSE	BUSINESS SERVICES	MARKETING AND BUSINESS MANAGEMENT SERVICES	BUSINESS MANAGEMENT SERVICES
PID	TRUE	BUSINESS SERVICES	MARKETING AND BUSINESS MANAGEMENT SERVICES	BUSINESS MANAGEMENT SERVICES
MNL	FALSE	BUSINESS SERVICES	MARKETING AND BUSINESS MANAGEMENT SERVICES	BUSINESS MANAGEMENT SERVICES
MNL	FALSE	AGRICULTURE	HORTICULTURE AND FRUIT GROWING	FRUIT GROWING N.E.C.
MNL	FALSE	FORESTRY AND LOGGING	FORESTRY AND LOGGING	LOGGING
DOL	FALSE	FORESTRY AND LOGGING	FORESTRY AND LOGGING	LOGGING
PID	FALSE	PROPERTY SERVICES	REAL ESTATE AGENTS	REAL ESTATE AGENTS
PID	TRUE	PROPERTY SERVICES	REAL ESTATE AGENTS	REAL ESTATE AGENTS
PID	FALSE	PROPERTY SERVICES	REAL ESTATE AGENTS	REAL ESTATE AGENTS
MNL	FALSE	PETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT M	BASIC CHEMICAL MANUFACTURING	SYNTHETIC RESIN MANUFACTURING
DOL	FALSE	PETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT M	BASIC CHEMICAL MANUFACTURING	SYNTHETIC RESIN MANUFACTURING
DOL	FALSE	AGRICULTURE	GRAIN, SHEEP AND BEEF CATTLE FARMING	BEEF CATTLE FARMING

Segments:

Segmentation	POLICY_TYP	SCHEME_FLAG	ANZSIC_INDUSTRY	ANZSIC_DIV
Accountants Scheme	PID	TRUE	ACCOUNTING SERVICES
Management	MNL	FALSE
Accountants Non-Scheme		FALSE	ACCOUNTING SERVICES
Engineers	PID	TRUE	CONSULTING ENGINEERING SERVICES
Medical	MDI	FALSE
Business Management Services - Non Scheme		FALSE	BUSINESS MANAGEMENT SERVICES
Real Estate Agents - Rest	PID	FALSE		PROPERTY SERVICES

Vijay_A_Verma
Most Valuable Professional
4 years ago
With my code what output you get and what is the output needed, then accordingly I can suggest you the solution.

Forum Discussion

Conditional column based on lookup table of conditions

7 Replies

Related content

Conditional Column with Multiple Conditions

Rank with conditions

Conditional Formatting using DAX

Conditional Formatting

Conditional Formatting