Hey everyone! I have 2 columns which I'm trying to concatenate with a "/" - However, in some instances, the 'Tr.prt' column is blank. Where this occurs, I don't want the concatenation to happen at all. Basically, in the 'Relationship' column I only want to see "xxxx/xxxx" or a blank/nothing. Can anyone help? 🙂

Good day MeganMarkey, It may be that the solutions given do not work for as they are DAX solutions and you may be working in Power Query. As you posted in the Power Query forum I'll give you a Power Query solution. Add a custom column using the following code, = if [Tr.prt] = "" then "" else [CoCd] & "/" & [Tr.prt] Hope this helps

Concatenate EXCEPT where blank | Microsoft Fabric Community

13 Replies

BA_Pete
Super User
1 year ago
Hi MeganMarkey ,

Try the following instead:

Relationship = IF( NOT ISBLANK(GIT[Tr.prt]), GIT[CoCd] & "/" & GIT[Tr.prt] )

Pete
- MeganMarkey
  Regular Visitor
  1 year ago
  Hey BA_Pete ,
  
  Unfortunately that hasn't worked 😞 Although when I read your response I was convinced it would! Just to check, am I missing something in making PBI recognise the cells as 'blank'? Or is being empty enough?
  
  Thanks!
  - BA_Pete
    Super User
    1 year ago
    
    In DAX terms, empty is BLANK().
    
    It sounds like you may have space (" ") or other special characters in your cells rather than them being truly empty/BLANK().
    
    Some upstream data cleaning may be in order here.
    
    Pete
mdaatifraza5556
Super User
1 year ago
Hi MeganMarkey

Can you please try the below dax.

Relationship =
IF (
'GIT'[Tr.prt] = BLANK(),
BLANK(),
'GIT'[CoCd] & "/" & 'GIT'[Tr.prt]
)

IF this answers your questions, kindly accept it as a solution and give kudos.
- BA_Pete
  Super User
  1 year ago
  
  Why would this work when we already know the below doesn't?
  
  Relationship = IF( NOT ISBLANK(GIT[Tr.prt]), GIT[CoCd] & "/" & GIT[Tr.prt] )
  
  Pete
  - mdaatifraza5556
    Super User
    1 year ago
    Value that appears to be blank (e.g., an empty string) is not treated as BLANK( ).
    
    If it shows Empty String, ISBLANK( ) will return FALSE.
    
    To treat both ""(empty string) and BLANK( ) as blank ----> use
    check = GIT[Tr.prt] = BLANK()
    
    or
    
    Check = TRIM(GIT[Tr.prt]) <> ""
    
    Below are the snapshot of each calculated column using the dax and what it's returning
    
    If this answers your questions, kindly accept it as a solution and give kudos.
collinsg
Solution Sage
1 year ago
Good day MeganMarkey,

It may be that the solutions given do not work for as they are DAX solutions and you may be working in Power Query. As you posted in the Power Query forum I'll give you a Power Query solution. Add a custom column using the following code,

= if [Tr.prt] = "" then "" else [CoCd] & "/" & [Tr.prt]

Hope this helps
v-veshwara-msft
Community Support
1 year ago
Hi MeganMarkey ,

Thanks for posting in Microsoft Fabric Community.

As suggested by mdaatifraza5556 and BA_Petethe DAX based approach can help achieve your requirement effectively.

If you prefer to use Power Query, as recommended by collinsg , you can try the following expression:

= if [Tr.prt] = "" then "" else [CoCd] & "/" & [Tr.prt]

This works well if Tr.prt column contains only empty strings ("") and no null values.

To make it more robust, especially in cases where Tr.prt might contain spaces or nulls (common when data is from Excel or other sources), you can use this version:

= if Text.Trim([Tr.prt]) = "" or [Tr.prt] = null then "" else [CoCd] & "/" & [Tr.prt]

This ensures that even spaces or nulls are treated as blank, and Relationship column will only show values like CoCd/Tr.prt when both parts are present.

Hope this helps. Please reach out for further assistance.

Thanks again to BA_Pete , mdaatifraza5556 and collinsg for your valuable guidance.

Attached is the .pbix file for reference.
Thank you.
Concatenate EXCEPT where blank.pbix65 KB
Riny_vE
Helper I
1 year ago
Wouldn't this work?
Relationship = IF(LEN(TRIM(GIT[Tr.Prt]))=0,BLANK(),GIT[CoCd]&"/"&GIT[Tr.Prt])
- v-veshwara-msft
  Community Support
  1 year ago
  Hi Riny_vE ,
  
  Thanks for the suggestion.
  
  Yes, the expression you provided works well as a calculated column when used within the same table:
  
  Relationship = IF(LEN(TRIM([Tr.prt])) = 0, BLANK(), [CoCd] & "/" & [Tr.prt])
  
  I tested this in the sample file and it gives the expected output, handling both blank and whitespace-only values in Tr.prt.
  
  Appreciate your input.
  
  Concatenate EXCEPT where blank.pbix67 KB
v-veshwara-msft
Community Support
1 year ago
Hi MeganMarkey ,
Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.
v-veshwara-msft
Community Support
1 year ago
Hi MeganMarkey ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.