Forum Discussion
Direct Query: Remove characters from beginning Dax
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?
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
Sounds about right, just stuck for time on this one unfortunately so I cast the net out wide.
Here's the sample data:
Questionnaire_Title_Secondary % of Total Head Delivered 2017/18 - Period 1 0.58% 2017/18 - Period 10 0.42% 2017/18 - Period 2 0.57% 2017/18 - Period 3 0.54% 2017/18 - Period 4 0.20% 2017/18 - Period 5 0.15% 2017/18 - Period 6 0.19% 2017/18 - Period 7 0.13% 2017/18 - Period 8 0.28% 2017/18 - Period 9 0.22% Period 1 - 2018/19 0.59% Period 1 - 2019/20 1.77% Period 1 - 2020/21 0.70% Period 10 - 2018/19 0.76% Period 10 - 2019/20 1.11% Period 10 - 2020/21 0.66% Period 11 - 2018/19 1.52% Period 11 - 2019/20 0.91% Period 11 - 2020/21 1.32% Period 12 - 2018/19 0.99% Period 12 - 2019/20 0.50% Period 12 - 2020/21 1.85% Period 2 - 2018/19 0.50% Period 2 - 2019/20 0.52% Period 2 - 2020/21 1.66% Period 3 - 2018/19 0.71% Period 3 - 2019/20 1.34% Period 3 - 2020/21 0.52% Period 4 - 2018/19 1.40% Period 4 - 2019/20 0.33% Period 4 - 2020/21 0.86% Period 5 - 2018/19 0.60% Period 5 - 2019/20 0.84% Period 5 - 2020/21 0.71% Period 6 - 2018/19 0.34% Period 6 - 2019/20 1.01% Period 6 - 2020/21 0.87% Period 7 - 2018/19 0.86% Period 7 - 2019/20 0.55% Period 7 - 2020/21 1.17% Period 8 - 2018/19 0.98% Period 8 - 2019/20 0.77% Period 8 - 2020/21 1.21% Period 9 - 2018/19 0.46% Period 9 - 2019/20 0.83% Period 9 - 2020/21 1.02% I'm just trying to extract the years in 2018/19 format. There are other values in this column but I only need the ones which contain "/" i.e. Period 9 - 2020/21
I have this substitute query that seems to work but the ordering is the issue now i.e. Period 9 - 2020/21 vs. 2017/18 - Period 5
Query:
Sort Year = LEFT(RIGHT('looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],8),8)I have this table:
2018/19 9.71% 2019/20 10.48% 2020/21 12.55% eriod 10 0.42% Period 1 0.58% Period 2 0.57% Period 3 0.54% Period 4 0.20% Period 5 0.15% Period 6 0.19% Period 7 0.13% Period 8 0.28% Period 9 0.22% Results are from the sample data I previously sent