Forum Discussion

Arman_And's avatar
Arman_And
Regular Visitor
3 years ago
Solved

Split column based on another column

Hi Everyone

I want to split one column based on another column. In other words, I want to subtract the string of another column from the current column.
I'm looking for a DAX or PQ formula to reach to Result column by splitting the Name column based on Subcategory Column.


Please check the sample dataset below:
The current stat: 

SubcategoryName
 CourtsSCHOOL PARK COURT
PlaygroundsMEMORIAL ELEMENTARY PLAYGROUNDS


The Result:

SubcategoryNameResult
 CourtsSCHOOL PARK COURT SCHOOL PARK
PlaygroundsMEMORIAL ELEMENTARY PLAYGROUNDSMEMORIAL ELEMENTARY
Data Protection
General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    Hi Arman_And ,

     

    You could try this:-

    Column = 
VAR _found =
    FIND ( UPPER ( 'Table'[Subcategory] ), UPPER ( 'Table'[Name] ), 1, 0 )
RETURN
    TRIM (
        REPLACE ( UPPER ( 'Table'[Name] ), _found, LEN ( 'Table'[Subcategory] ), "" )
    )

    Output;-

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi Arman_And ,

     

    You could try this:-

    Column = 
VAR _found =
    FIND ( UPPER ( 'Table'[Subcategory] ), UPPER ( 'Table'[Name] ), 1, 0 )
RETURN
    TRIM (
        REPLACE ( UPPER ( 'Table'[Name] ), _found, LEN ( 'Table'[Subcategory] ), "" )
    )

    Output;-