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?
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:
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
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?