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...
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?
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
HotChilli
Community Champion
Community ChampionAh, 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?