Conditional column based on lookup table of conditions

[Crow2525]

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

[Crow2525]

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]

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

[Vijay_A_Verma]

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

[Crow2525]

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

[Crow2525]

The output is bringing up incorrect matches:

Note 2 - management for Product_typ = "DOL"

POLICY_TYP	SCHEME FLAG	ANZSIC_SubDiv_Desc	ANZSIC_Group_Desc	ANZSIC_Code_Desc	Custom1
PID	FALSE	BUSINESS SERVICES	TECHNICAL SERVICES	ARCHITECTURAL SERVICES	2 - Management
PID	TRUE	BUSINESS SERVICES	TECHNICAL SERVICES	ARCHITECTURAL SERVICES	1 - Accountants Scheme
PID	FALSE	BUSINESS SERVICES	TECHNICAL SERVICES	ARCHITECTURAL SERVICES	2 - Management
PID	FALSE	BUSINESS SERVICES	LEGAL AND ACCOUNTING SERVICES	ACCOUNTING SERVICES	1 - Accountants Scheme
PID	TRUE	BUSINESS SERVICES	LEGAL AND ACCOUNTING SERVICES	ACCOUNTING SERVICES	1 - Accountants Scheme
MNL	FALSE	CONSTRUCTION TRADE SERVICES	INSTALLATION TRADE SERVICES	PLUMBING SERVICES	2 - Management
DOL	FALSE	CONSTRUCTION TRADE SERVICES	INSTALLATION TRADE SERVICES	PLUMBING SERVICES	2 - Management
DOL	FALSE	AGRICULTURE	HORTICULTURE AND FRUIT GROWING	GRAPE GROWING	2 - Management
PID	FALSE	HEALTH SERVICES	VETERINARY SERVICES	VETERINARY SERVICES	2 - Management
ICT	FALSE	BUSINESS SERVICES	LEGAL AND ACCOUNTING SERVICES	COMPUTER CONSULTANCY SERVICES	2 - Management
PID	FALSE	BUSINESS SERVICES	MARKETING AND BUSINESS MANAGEMENT SERVICES	BUSINESS MANAGEMENT SERVICES	6 - Business Management Services - Non Scheme
PID	TRUE	BUSINESS SERVICES	MARKETING AND BUSINESS MANAGEMENT SERVICES	BUSINESS MANAGEMENT SERVICES	6 - Business Management Services - Non Scheme
MNL	FALSE	BUSINESS SERVICES	MARKETING AND BUSINESS MANAGEMENT SERVICES	BUSINESS MANAGEMENT SERVICES	6 - Business Management Services - Non Scheme
MNL	FALSE	AGRICULTURE	HORTICULTURE AND FRUIT GROWING	FRUIT GROWING N.E.C.	2 - Management
MNL	FALSE	FORESTRY AND LOGGING	FORESTRY AND LOGGING	LOGGING	2 - Management
DOL	FALSE	FORESTRY AND LOGGING	FORESTRY AND LOGGING	LOGGING	2 - Management
PID	FALSE	PROPERTY SERVICES	REAL ESTATE AGENTS	REAL ESTATE AGENTS	7 - Real Estate Agents - Rest
PID	TRUE	PROPERTY SERVICES	REAL ESTATE AGENTS	REAL ESTATE AGENTS	7 - Real Estate Agents - Rest
PID	FALSE	PROPERTY SERVICES	REAL ESTATE AGENTS	REAL ESTATE AGENTS	7 - Real Estate Agents - Rest
MNL	FALSE	PETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT M	BASIC CHEMICAL MANUFACTURING	SYNTHETIC RESIN MANUFACTURING	2 - Management
DOL	FALSE	PETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT M	BASIC CHEMICAL MANUFACTURING	SYNTHETIC RESIN MANUFACTURING	2 - Management
DOL	FALSE	AGRICULTURE	GRAIN, SHEEP AND BEEF CATTLE FARMING	BEEF CATTLE FARMING	2 - Management

[Vijay_A_Verma · Accepted solution]

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]

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.