Concatenate EXCEPT where blank

[MeganMarkey]

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? 🙂

[v-veshwara-msft]

Hi @MeganMarkey ,
Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.

[Riny_vE]

Wouldn't this work?

Relationship = IF(LEN(TRIM(GIT[Tr.Prt]))=0,BLANK(),GIT[CoCd]&"/"&GIT[Tr.Prt])

[v-veshwara-msft]

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.

[v-veshwara-msft]

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.

[collinsg]

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

[mdaatifraza5556]

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]

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]

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.

[BA_Pete]

Hey @mdaatifraza5556 ,

Thanks for the great explanation and examples. I've learned something new here!

Based on the above change, you could simplify your code a little bit as follows, but yours is the correct solution 👍

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

Pete

[BA_Pete]

Hi @MeganMarkey ,

Try the following instead:

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

Pete

[MeganMarkey]

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]

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