Forum Discussion

andyly85's avatar
andyly85
Frequent Visitor
1 year ago
Solved

Help with coding conditional functions in power query

Hello, 

 

How do I code this in Power Query please?

 

If column 'Investment Vehicle Type' is Frontier Markets AND column Entity Relationship Name is 'TRP Fund of Funds', then Entity Relationship Name is 'Retirement Plan'.  Thanks in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi andyly85 ,

    I can give you two ways:
    First solution:

    #"Replaced Value" = Table.ReplaceValue(
        #"Changed Type", 
        each if [Investment Vehicle Type] = "Frontier Markets" and [Entity Relationship Name] = "TRP Fund of Funds" then [Entity Relationship Name] else false,
        "Retirement Plan",
        Replacer.ReplaceValue,
        {"Entity Relationship Name"}
    )

    Origin:

    Output:


    Second solution:

    Custom1 = Table.FromRecords(Table.TransformRows(#"Changed Type",(x) => Record.TransformFields(x,{"Entity Relationship Name", each if x[Investment Vehicle Type] = "Frontier Markets" and x[Entity Relationship Name] = "TRP Fund of Funds" then "Retirement Plan" else _})))

    Origin:

    Output:


    Here are the two whole M codes in the Advanced Editor:
    First one:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcivKzyvJTC1S8E0syk4tKVbSUQoJClBwK81LUchPA9PFSrE6WBXml2SkFoElISwsWg1x6cVuCcwcqMmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Investment Vehicle Type" = _t, #"Entity Relationship Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Investment Vehicle Type", type text}, {"Entity Relationship Name", type text}}),
    #"Replaced Value" = Table.ReplaceValue(
        #"Changed Type", 
        each if [Investment Vehicle Type] = "Frontier Markets" and [Entity Relationship Name] = "TRP Fund of Funds" then [Entity Relationship Name] else false,
        "Retirement Plan",
        Replacer.ReplaceValue,
        {"Entity Relationship Name"}
    )
in
    #"Replaced Value"

    Second one:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcivKzyvJTC1S8E0syk4tKVbSUQoJClBwK81LUchPA9PFSrE6WBXml2SkFoElISwStGJRGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Investment Vehicle Type" = _t, #"Entity Relationship Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Investment Vehicle Type", type text}, {"Entity Relationship Name", type text}}),
    Custom1 = Table.FromRecords(Table.TransformRows(#"Changed Type",(x) => Record.TransformFields(x,{"Entity Relationship Name", each if x[Investment Vehicle Type] = "Frontier Markets" and x[Entity Relationship Name] = "TRP Fund of Funds" then "Retirement Plan" else _})))
in
    Custom1


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    1 year ago

    Hi! Here is what I used in order to achieve the below:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Entity Relationship NEW", each if [Invest Veh Type] = "Frontier Markets" and [Entity Relationship Name] = "TRP Fund of Funds" then "Retirement Plan" else [Entity Relationship Name])

     

    Here is the entire M-Code I have:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcivKzyvJTC1S8E0syk4tKVbSUQoJClBwK81LUchPA9PFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invest Veh Type" = _t, #"Entity Relationship Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invest Veh Type", type text}, {"Entity Relationship Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Entity Relationship NEW", each if [Invest Veh Type] = "Frontier Markets" and [Entity Relationship Name] = "TRP Fund of Funds" then "Retirement Plan" else [Entity Relationship Name])
    in
    #"Added Custom"

    • andyly85's avatar
      andyly85
      Frequent Visitor
      1 year ago

      Hi!  Is it possible to code it without adding a new column?  Thanks!

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    Use this formula


    = Table.ReplaceValue(Source, each [Investment Vehicle Type]="Frontier Markets " and  [Entity Relationship Name]="TRP Fund of Funds" ,"",(a,b,c)=> if b then "Retirement Plan" else a ,{"Entity Relationship"})

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi andyly85 ,

    I can give you two ways:
    First solution:

    #"Replaced Value" = Table.ReplaceValue(
        #"Changed Type", 
        each if [Investment Vehicle Type] = "Frontier Markets" and [Entity Relationship Name] = "TRP Fund of Funds" then [Entity Relationship Name] else false,
        "Retirement Plan",
        Replacer.ReplaceValue,
        {"Entity Relationship Name"}
    )

    Origin:

    Output:


    Second solution:

    Custom1 = Table.FromRecords(Table.TransformRows(#"Changed Type",(x) => Record.TransformFields(x,{"Entity Relationship Name", each if x[Investment Vehicle Type] = "Frontier Markets" and x[Entity Relationship Name] = "TRP Fund of Funds" then "Retirement Plan" else _})))

    Origin:

    Output:


    Here are the two whole M codes in the Advanced Editor:
    First one:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcivKzyvJTC1S8E0syk4tKVbSUQoJClBwK81LUchPA9PFSrE6WBXml2SkFoElISwsWg1x6cVuCcwcqMmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Investment Vehicle Type" = _t, #"Entity Relationship Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Investment Vehicle Type", type text}, {"Entity Relationship Name", type text}}),
    #"Replaced Value" = Table.ReplaceValue(
        #"Changed Type", 
        each if [Investment Vehicle Type] = "Frontier Markets" and [Entity Relationship Name] = "TRP Fund of Funds" then [Entity Relationship Name] else false,
        "Retirement Plan",
        Replacer.ReplaceValue,
        {"Entity Relationship Name"}
    )
in
    #"Replaced Value"

    Second one:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcivKzyvJTC1S8E0syk4tKVbSUQoJClBwK81LUchPA9PFSrE6WBXml2SkFoElISwStGJRGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Investment Vehicle Type" = _t, #"Entity Relationship Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Investment Vehicle Type", type text}, {"Entity Relationship Name", type text}}),
    Custom1 = Table.FromRecords(Table.TransformRows(#"Changed Type",(x) => Record.TransformFields(x,{"Entity Relationship Name", each if x[Investment Vehicle Type] = "Frontier Markets" and x[Entity Relationship Name] = "TRP Fund of Funds" then "Retirement Plan" else _})))
in
    Custom1


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.