Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Crow2525
Helper I
Helper I

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):

FruitAnimalVehicle
AppleLionCar
AppleMouseCar
OrangeCatPlane
PearCatTrain

 

Condition Dataset (Table Name: Conditions):

SegmentFruitAnimalVehicle
1 CatPlane
2Pear  
3 CatTrain
4Apple  
5 Mouse 
6OrangeCatPlane

 

Ideal Output:

FruitAnimalVehicleSegment Group
AppleLionCar4
AppleMouseCar4
OrangeCatPlane6
PearCatTrain2

 

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

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

 

View solution in original post

7 REPLIES 7
Crow2525
Helper I
Helper I

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.

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

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

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_TYPSCHEME FLAGANZSIC_SubDiv_DescANZSIC_Group_DescANZSIC_Code_Desc
PIDFALSEBUSINESS SERVICESTECHNICAL SERVICESARCHITECTURAL SERVICES
PIDTRUEBUSINESS SERVICESTECHNICAL SERVICESARCHITECTURAL SERVICES
PIDFALSEBUSINESS SERVICESTECHNICAL SERVICESARCHITECTURAL SERVICES
PIDFALSEBUSINESS SERVICESLEGAL AND ACCOUNTING SERVICESACCOUNTING SERVICES
PIDTRUEBUSINESS SERVICESLEGAL AND ACCOUNTING SERVICESACCOUNTING SERVICES
MNLFALSECONSTRUCTION TRADE SERVICESINSTALLATION TRADE SERVICESPLUMBING SERVICES
DOLFALSECONSTRUCTION TRADE SERVICESINSTALLATION TRADE SERVICESPLUMBING SERVICES
DOLFALSEAGRICULTUREHORTICULTURE AND FRUIT GROWINGGRAPE GROWING
PIDFALSEHEALTH SERVICESVETERINARY SERVICESVETERINARY SERVICES
ICTFALSEBUSINESS SERVICESLEGAL AND ACCOUNTING SERVICESCOMPUTER CONSULTANCY SERVICES
PIDFALSEBUSINESS SERVICESMARKETING AND BUSINESS MANAGEMENT SERVICESBUSINESS MANAGEMENT SERVICES
PIDTRUEBUSINESS SERVICESMARKETING AND BUSINESS MANAGEMENT SERVICESBUSINESS MANAGEMENT SERVICES
MNLFALSEBUSINESS SERVICESMARKETING AND BUSINESS MANAGEMENT SERVICESBUSINESS MANAGEMENT SERVICES
MNLFALSEAGRICULTUREHORTICULTURE AND FRUIT GROWINGFRUIT GROWING N.E.C.
MNLFALSEFORESTRY AND LOGGINGFORESTRY AND LOGGINGLOGGING
DOLFALSEFORESTRY AND LOGGINGFORESTRY AND LOGGINGLOGGING
PIDFALSEPROPERTY SERVICESREAL ESTATE AGENTSREAL ESTATE AGENTS
PIDTRUEPROPERTY SERVICESREAL ESTATE AGENTSREAL ESTATE AGENTS
PIDFALSEPROPERTY SERVICESREAL ESTATE AGENTSREAL ESTATE AGENTS
MNLFALSEPETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT MBASIC CHEMICAL MANUFACTURINGSYNTHETIC RESIN MANUFACTURING
DOLFALSEPETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT MBASIC CHEMICAL MANUFACTURINGSYNTHETIC RESIN MANUFACTURING
DOLFALSEAGRICULTUREGRAIN, SHEEP AND BEEF CATTLE FARMINGBEEF CATTLE FARMING

 

Segments:

SegmentationPOLICY_TYPSCHEME_FLAGANZSIC_INDUSTRYANZSIC_DIV
Accountants SchemePIDTRUEACCOUNTING SERVICES 
ManagementMNLFALSE  
Accountants Non-Scheme FALSEACCOUNTING SERVICES 
EngineersPIDTRUECONSULTING ENGINEERING SERVICES 
MedicalMDIFALSE  
Business Management Services - Non Scheme FALSEBUSINESS MANAGEMENT SERVICES 
Real Estate Agents - RestPIDFALSE PROPERTY SERVICES

The output is bringing up incorrect matches:

 

Note 2 - management for Product_typ = "DOL"

POLICY_TYPSCHEME FLAGANZSIC_SubDiv_DescANZSIC_Group_DescANZSIC_Code_DescCustom1
PIDFALSEBUSINESS SERVICESTECHNICAL SERVICESARCHITECTURAL SERVICES2 - Management
PIDTRUEBUSINESS SERVICESTECHNICAL SERVICESARCHITECTURAL SERVICES1 - Accountants Scheme
PIDFALSEBUSINESS SERVICESTECHNICAL SERVICESARCHITECTURAL SERVICES2 - Management
PIDFALSEBUSINESS SERVICESLEGAL AND ACCOUNTING SERVICESACCOUNTING SERVICES1 - Accountants Scheme
PIDTRUEBUSINESS SERVICESLEGAL AND ACCOUNTING SERVICESACCOUNTING SERVICES1 - Accountants Scheme
MNLFALSECONSTRUCTION TRADE SERVICESINSTALLATION TRADE SERVICESPLUMBING SERVICES2 - Management
DOLFALSECONSTRUCTION TRADE SERVICESINSTALLATION TRADE SERVICESPLUMBING SERVICES2 - Management
DOLFALSEAGRICULTUREHORTICULTURE AND FRUIT GROWINGGRAPE GROWING2 - Management
PIDFALSEHEALTH SERVICESVETERINARY SERVICESVETERINARY SERVICES2 - Management
ICTFALSEBUSINESS SERVICESLEGAL AND ACCOUNTING SERVICESCOMPUTER CONSULTANCY SERVICES2 - Management
PIDFALSEBUSINESS SERVICESMARKETING AND BUSINESS MANAGEMENT SERVICESBUSINESS MANAGEMENT SERVICES6 - Business Management Services - Non Scheme
PIDTRUEBUSINESS SERVICESMARKETING AND BUSINESS MANAGEMENT SERVICESBUSINESS MANAGEMENT SERVICES6 - Business Management Services - Non Scheme
MNLFALSEBUSINESS SERVICESMARKETING AND BUSINESS MANAGEMENT SERVICESBUSINESS MANAGEMENT SERVICES6 - Business Management Services - Non Scheme
MNLFALSEAGRICULTUREHORTICULTURE AND FRUIT GROWINGFRUIT GROWING N.E.C.2 - Management
MNLFALSEFORESTRY AND LOGGINGFORESTRY AND LOGGINGLOGGING2 - Management
DOLFALSEFORESTRY AND LOGGINGFORESTRY AND LOGGINGLOGGING2 - Management
PIDFALSEPROPERTY SERVICESREAL ESTATE AGENTSREAL ESTATE AGENTS7 - Real Estate Agents - Rest
PIDTRUEPROPERTY SERVICESREAL ESTATE AGENTSREAL ESTATE AGENTS7 - Real Estate Agents - Rest
PIDFALSEPROPERTY SERVICESREAL ESTATE AGENTSREAL ESTATE AGENTS7 - Real Estate Agents - Rest
MNLFALSEPETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT MBASIC CHEMICAL MANUFACTURINGSYNTHETIC RESIN MANUFACTURING2 - Management
DOLFALSEPETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT MBASIC CHEMICAL MANUFACTURINGSYNTHETIC RESIN MANUFACTURING2 - Management
DOLFALSEAGRICULTUREGRAIN, SHEEP AND BEEF CATTLE FARMINGBEEF CATTLE FARMING2 - Management
Vijay_A_Verma
Super User
Super User

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"

 

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.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors