Forum Discussion

Crow2525's avatar
Crow2525
Icon for Helper I rankHelper 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's avatar
    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's avatar
Crow2525
Icon for Helper I rankHelper 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's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost 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's avatar
    Crow2525
    Icon for Helper I rankHelper 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_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
    • Crow2525's avatar
      Crow2525
      Icon for Helper I rankHelper I
      4 years ago

      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