Help with coding conditional functions in power query

[andyly85]

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!

[v-junyant-msft · Accepted solution]

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.

[Omid_Motamedise]

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"})

[audreygerred]

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]

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