Forum Discussion

AlexisCohen88K's avatar
AlexisCohen88K
Frequent Visitor
5 years ago
Solved

Using variables in custom column Power M

Hello,   I'm trying to build a string whose parts will depend on a column value. In order to avoid writing a if statement for each possibility, I tried to write 3 if block that assign the desired ...
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    AlexisCohen88K you can write a function and let it return the concatenated text.

     

    (status_c, acdeal_name, airplane_caracter, is_off_market__c)=>
let
var1 = if status_c="For Sale" or status_c="FS (Unverified)" then  "For sale"
else if status_c="FS (Deal Pending)" or status_c="FS (Pndg - Unv)" then  "Deal Pending"
else if status_c="For Lease" then  "For Lease"
else if status_c="Not for Sale" then  "Not for Sale"
else  "Unknown",

var2 = if [acdeal_name<>null then "88K " & airplane_caracter & " " else null,

var3 = if is_off_market__c=true then "(Off-Market)" else null
in
var1 & var2 & va3

     

    now add a new column in your main table and call this function and pass columns as parameters

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

parry2k's avatar
parry2k
Super User
5 years ago

AlexisCohen88K you can write a function and let it return the concatenated text.

 

(status_c, acdeal_name, airplane_caracter, is_off_market__c)=>
let
var1 = if status_c="For Sale" or status_c="FS (Unverified)" then  "For sale"
else if status_c="FS (Deal Pending)" or status_c="FS (Pndg - Unv)" then  "Deal Pending"
else if status_c="For Lease" then  "For Lease"
else if status_c="Not for Sale" then  "Not for Sale"
else  "Unknown",

var2 = if [acdeal_name<>null then "88K " & airplane_caracter & " " else null,

var3 = if is_off_market__c=true then "(Off-Market)" else null
in
var1 & var2 & va3

 

now add a new column in your main table and call this function and pass columns as parameters

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

AlexisCohen88K's avatar
AlexisCohen88K
Frequent Visitor
5 years ago

Hello parry2k,

 

Thank you for your help.

I just created the following function and invoked in a custom column with variables being my table columns. I have an error on the resulting column :

"Expression.Error: We cannot convert a value of type Table to type Function.
Details:
Value=[Table]
Type=[Type]"

 

Tried several thigs but I could not get any result. Would you have any idea?

 

Here is my function :

= (Status__c, acdeal_name, airplane_caracter, is_off_market__c)=>
let
    var_status = if Status__c="For Sale" or Status__c="FS (Unverified)" then  "For sale"
    else if Status__c="FS (Deal Pending)" or Status__c="FS (Pndg - Unv)" then  "Deal Pending"
    else if Status__c="For Lease" then  "For Lease"
    else if Status__c="Not for Sale" then  "Not for Sale"
    else  "Unknown",

    var_inventory = if acdeal_name<>null then "88K " & airplane_caracter & " " else null,

    var_offmarket = if is_off_market__c=true then "(Off-Market)" else null
in
var_status & var_inventory & var_offmarket

 

Thank you again