Using variables in custom column Power M

[AlexisCohen88K]

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!

[parry2k]

@AlexisCohen88K can you share sample pbix file and I will get it done. 

[parry2k · Accepted solution]

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

[Syndicate_Admin]

Hello

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

[AlexisCohen88K]

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