Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Take text after a delimiter, DirectQuery

Hello,   I have a DirectQuery that pulls a column that has values like below. Normally if this was a data import, I would simply extract the text after the delimiter using PowerQuery M. This is not...
Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey,

     

    maybe you can add a calculated column to your table like so:

    Column = 
var theText = 'Table1'[aname]
var thePosition = FIND("\",theText,1,0)
return
MID(theText,thePosition + 1,LEN(theText)-thePosition)

    then you can use the new column also as a slicer.
    The DAX for a measure will look like this. it's a little bit more verbose to make sure that a single value of the column with the name is present:

    Measure = 
IF(HASONEVALUE(Table1[aname])
    ,var theText = FIRSTNONBLANK('Table1'[aname],0)
    var thePosition = FIND("\",theText,1,0)
    return
    MID(theText,thePosition + 1,LEN(theText)-thePosition)
)

    This is my testdata :-)

     

    Regards,

    Tom

     

TomMartens's avatar
TomMartens
Super User
7 years ago

Hey,

 

maybe you can add a calculated column to your table like so:

Column = 
var theText = 'Table1'[aname]
var thePosition = FIND("\",theText,1,0)
return
MID(theText,thePosition + 1,LEN(theText)-thePosition)

then you can use the new column also as a slicer.
The DAX for a measure will look like this. it's a little bit more verbose to make sure that a single value of the column with the name is present:

Measure = 
IF(HASONEVALUE(Table1[aname])
    ,var theText = FIRSTNONBLANK('Table1'[aname],0)
    var thePosition = FIND("\",theText,1,0)
    return
    MID(theText,thePosition + 1,LEN(theText)-thePosition)
)

This is my testdata :-)

 

Regards,

Tom