Forum Discussion
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 for this but it doesnt seem to work:
Sort Year =
RIGHT (
LEFT (
'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],
SEARCH ( "_", 'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],1, LEN ( 'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary]) - 1 ) - 1
),
LEN (
LEFT (
'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],
SEARCH ( "_", 'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],1, LEN ( 'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary]) - 1 ) - 1
)
) - 7
)
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?
6 Replies
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?
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):
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
I think you have 2 different forms of similar question floating around.
Also I can't access the app.powerbi link provided (which is quite right)
Could you paste the data sample here? Here's how