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 value to a variable.

Final stage is to combine variables value to get my final string (like I would do in VBA)

 

I got an error on the second if block (Token Eof expected).

 

Below is the code (written in a custom column dial box):

 

if [Status__c]="For Sale" or [Status__c]="FS (Unverified)" then variable_status="For sale"
else if [Status__c]="FS (Deal Pending)" or [Status__c]="FS (Pndg - Unv)" then variable_status="Deal Pending"
else if [Status__c]="For Lease" then variable_status="For Lease"
else if [Status__c]="Not for Sale" then variable_status="Not for Sale"
else variable_status="Unknown"

if [Ac Deal.Name]<>null then variable_inventory="88K " & [Airplane Caracter] & " " else variable_inventory=null

if [is_off_market__c]=true then variable_market="(Off-Market)" else variable_market=null

variable_inventory & variable_status & variable_market

 

 

The result expected in this last line that concatenate all the variables text into one string.

 

Would you have any idea on how to achieve this without creating 3 different columns or getting things too complex?

I already got +100 columns...

 

Thank you for your time!

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.⚡

4 Replies

  • 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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      Hello

      I have been looking for this solution for a long time, thanks for sharing it, I have managed to load the variables.