cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Neill_
Helper III
Helper III

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

)

 

1 ACCEPTED 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?

View solution in original post

6 REPLIES 6
HotChilli
Super User
Super User

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

 

https://app.powerbi.com/groups/me/reports/7f8586b7-1789-48d2-9866-d9434547dd26?ctid=0727f0b0-9d54-42...

 

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

 

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

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 10.58%
2017/18 - Period 100.42%
2017/18 - Period 20.57%
2017/18 - Period 30.54%
2017/18 - Period 40.20%
2017/18 - Period 50.15%
2017/18 - Period 60.19%
2017/18 - Period 70.13%
2017/18 - Period 80.28%
2017/18 - Period 90.22%
Period 1 - 2018/190.59%
Period 1 - 2019/201.77%
Period 1 - 2020/210.70%
Period 10 - 2018/190.76%
Period 10 - 2019/201.11%
Period 10 - 2020/210.66%
Period 11 - 2018/191.52%
Period 11 - 2019/200.91%
Period 11 - 2020/211.32%
Period 12 - 2018/190.99%
Period 12 - 2019/200.50%
Period 12 - 2020/211.85%
Period 2 - 2018/190.50%
Period 2 - 2019/200.52%
Period 2 - 2020/211.66%
Period 3 - 2018/190.71%
Period 3 - 2019/201.34%
Period 3 - 2020/210.52%
Period 4 - 2018/191.40%
Period 4 - 2019/200.33%
Period 4 - 2020/210.86%
Period 5 - 2018/190.60%
Period 5 - 2019/200.84%
Period 5 - 2020/210.71%
Period 6 - 2018/190.34%
Period 6 - 2019/201.01%
Period 6 - 2020/210.87%
Period 7 - 2018/190.86%
Period 7 - 2019/200.55%
Period 7 - 2020/211.17%
Period 8 - 2018/190.98%
Period 8 - 2019/200.77%
Period 8 - 2020/211.21%
Period 9 - 2018/190.46%
Period 9 - 2019/200.83%
Period 9 - 2020/211.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

@HotChilli 

 

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/199.71%
2019/2010.48%
2020/2112.55%
eriod 100.42%
Period 10.58%
Period 20.57%
Period 30.54%
Period 40.20%
Period 50.15%
Period 60.19%
Period 70.13%
Period 80.28%
Period 90.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?

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors