Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Direct Query: Remove characters from beginning Dax

I'm trying to remove the first 7 characters from a period list to leave the remaing 6 behind e.g. "Period 1 - 2018/19" to just 2018/19.   I'm working with direct query and have created a new column...
Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    Ah, you've lost me there. I don't understand what you're telling me.

     

    This is the simplest I could come up with (not tested on Direct Query)

    ColumnText2 = IF (LEFT(TableX[theColumn],1) IN {"1", "2"} ,
   LEFT ( TableX[theColumn], 7)
,
   RIGHT ( TableX[theColumn], 7))

    May I also ask what the source system is because Power Query can do this if the source is SQL Server or a few others?

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
6 years ago

It throws an error? Or does nothing? Or returns an incorrect result?

Can you clear that up and also provide a representative data sample.   What's the reason for not using a simpler formula to extract the 7 chars on the right?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

It throws an error: Function: 'ISERROR' is not allowed as part of a calculated column DAX expressions on DirectQuery Models

 

Here's a sample dataset of the data I'm working with (Note is has some periods in reverse too):

 

https://app.powerbi.com/groups/me/reports/7f8586b7-1789-48d2-9866-d9434547dd26?ctid=0727f0b0-9d54-4244-a73c-2debe1367db8

 

From other responses it seems to be a limitation of Direct Query:

https://community.powerbi.com/t5/Desktop/Trim-the-first-7-characters-from-value/m-p/1133084#M515998