Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create new column based on text from a messy text blob

So, I have a column with data that looks like this: #Research; #ourResearch-Cat1-Subcat1; #Benchmark; 2018; Benchmark #Research; #OurResearch-Cat1-Subcat2; #Benchmark; Benchmark #OURRES...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    7 years ago

    Hi Anonymous

     

    Please try these two calculated columns as an alternative.  These are in DAX and again, I have attached a PBIX file that you can download to see/tweak etc.

     

    Category = 
VAR LengthOfText = LEN('Table1'[Column1])
VAR StartingPointOR = SEARCH("#OurResearch",'Table1'[Column1],1,0)+1
VAR RestOfText1 =  MID('Table1'[Column1],StartingPointOR+12,LengthOfText-StartingPointOR+1) & ";"
VAR StartingPointSC = SEARCH(";",RestOfText1,1,0)-1
VAR RestOfText2 = LEFT(RestOfText1,StartingPointSC)
VAR StartingPointDash = SEARCH("-",RestOfText2,1,0)

RETURN 
    IF(
        StartingPointDash>0,
        LEFT(RestOfText2,StartingPointDash-1),
        RestOfText2
        )

    and

     

    Sub Category = 
VAR LengthOfText = LEN('Table1'[Column1])
VAR StartingPointOR = SEARCH("#OurResearch",'Table1'[Column1],1,0)+1
VAR RestOfText1 =  MID('Table1'[Column1],StartingPointOR+12,LengthOfText-StartingPointOR+1) & ";"
VAR StartingPointSC = SEARCH(";",RestOfText1,1,0)-1
VAR RestOfText2 = LEFT(RestOfText1,StartingPointSC)
VAR StartingPointDash = SEARCH("-",RestOfText2,1,0)

RETURN 
    IF(
        StartingPointDash>0,
        MID(RestOfText2,StartingPointDash+1,99)
        )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

In case anybody wants to see the full text of v-jiascu-msft's code without having to download the file, here it is:

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.BeforeDelimiter(Text.BetweenDelimiters([Column1], "-", ";"), "-")),
 #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.AfterDelimiter(Text.BetweenDelimiters([Column1], "-", ";"), "-"))


The 'each' in each statement seems like a particularly important detail there.

Also, that last example does not actually have a semicolon for the Text.BetweenDelimiters to key off of. Yet it seems to work. The the documentation of the function does not describe how the function behaves if the second delimeter doesn't exist, how did you figure out that solution?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    I'm glad it can work. Firstly, we need to find the pattern that the computer can recognize. Secondly, find a function here that can do the job. 

    The functions will iterate every character of the parameter till the second delimiter or the end. So it won't be a problem if the second delimiter doesn't exist.

     

    Best Regards,
    Dale