Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
)
Solved! Go to Solution.
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:
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |