Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@AlexisCohen88K can you share sample pbix file and I will get it done.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello
I have been looking for this solution for a long time, thanks for sharing it, I have managed to load the variables.
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
User | Count |
---|---|
117 | |
75 | |
62 | |
50 | |
44 |
User | Count |
---|---|
174 | |
125 | |
60 | |
60 | |
57 |