Forum Discussion

MeganMarkey's avatar
MeganMarkey
Regular Visitor
1 year ago
Solved

Concatenate EXCEPT where blank

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 a...
Need Help
  • collinsg's avatar
    collinsg
    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

     

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
1 year ago

Hi MeganMarkey ,

 

Try the following instead:

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

 

Pete

MeganMarkey's avatar
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's avatar
    BA_Pete
    Icon for Super User rankSuper 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