Forum Discussion

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 (Tab...

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"

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

Crow2525

Helper I

4 years ago

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

Forum Discussion

Conditional column based on lookup table of conditions

Related content

Conditional Column with Multiple Conditions

Rank with conditions

Conditional Formatting

Conditional column

Conditional Formatting