Split columns

[Anonymous]

Hi All,

I have table named Main Databse1 with column Name WF and data type =text.

I want split by ( and  - from right to get document type

Subcontractor Submission to AC (Procedure)

Subcontractor Submission to AC - Method Statement for Installation & Testing of Above/Below Ground Drainage

SUB(WF) -Subcontractor Submission to AC - (Method Statement)

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce the scenario.

You may a measure and a calculated column as follows.

Measure: bracket = UNICHAR(40)

Calculated column: document type =
var t = ADDCOLUMNS(GENERATESERIES(1,LEN([WF]),1),"Char",LEFT(RIGHT([WF],[Value]),1))
var minIndex = MINX(FILTER(t,[Char]= UNICHAR(40) || [Char]="-"),[Value])
 
return RIGHT([WF],minIndex)

Result:

As a workaround, you may click ‘Edit Query’, go to Query Editor, click ‘Add Column’ ribbon, choose ‘Custom Column’, input the following formula ‘Text.End([WF],Text.PositionOfAny(Text.Reverse([WF]),{"-","("})+1)’

Result:

If I misunderstand your thought, please show me your expected output. Do mask sensitive data before uploading. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Not Very clea. Bur refer 

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

https://community.powerbi.com/t5/Desktop/Conditional-Split-Column-by-delimiter-based-on-value/td-p/7...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601