Forum Discussion

MusterBuster's avatar
MusterBuster
Regular Visitor
8 years ago
Solved

Extract a Location Name from ugly data

Hi everyone,

 

I have some nasty, ugly data courtesy of a Trustpilot review export. I need to find a way to identify which specific store a customer review refers to, however the data from Trustpilot looks like this:

 

DateContentRatingTypeReference ID
28/02/2018gfagfegfei5InvitedBrand_Name_StoreType_Walsall_Service
28/02/2018ieohgeoighoie3InvitedBrand_Name_StoreType_Ashton
29/02/2018jgpoieqjgij2OrganicBrand_Name_StoreType_St_Albans
29/02/2018gmjiejhgoij5InvitedBrand_Name_StoreType_Kilbride_Service
29/02/2018jegopijopg1InvitedBrand_Name_StoreType_Edinburgh_(VM)
29/02/2018egjgpoj2InvitedBrand_Name_StoreType_Gainsborough

 

The only data I care about here is the UK town or city that's within the Reference ID values. Everything else can go. The problems I have are:

 

  • I don't think I can use delimiters as some locations have underscores in their names (eg - St. Albans is "St_Albans" as above)
  • I can't use a range as StoreType always has a different number of character (eg it could be a "Digital", "Lite", "Connect" store for example)

The only solution I can think of is setting up a crazy Replace Values - though it would have to be around 240 lines long to account for all of our locations. 

 

Any other magic tricks out there?

  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    This is very brute force:

     

    Column = 
VAR underscore1 = FIND("_",[Reference ID],1,0)
VAR string1 = MID([Reference ID],underscore1+1,LEN([Reference ID])-underscore1)
VAR underscore2 = FIND("_",string1,1,0)
VAR string2 = MID(string1,underscore2+1,LEN(string1)-underscore2)
VAR underscore3 = FIND("_",string2,1,0)
VAR string3 = MID(string2,underscore3+1,LEN(string2)-underscore3)
RETURN SUBSTITUTE(string3,"_"," ")

5 Replies

    • MusterBuster's avatar
      MusterBuster
      Regular Visitor
      8 years ago
      MFelixwrote:

      Hi MusterBuster,

       

      Is the Brand_Name also variable or is it the same?

       

      Regards,

      MFelix

      Hi MFelix!

       

      Thanks for responding. There's 2 potential brands:

       

      Brand_Name 

      BrandName

       

      MB

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    This is very brute force:

     

    Column = 
VAR underscore1 = FIND("_",[Reference ID],1,0)
VAR string1 = MID([Reference ID],underscore1+1,LEN([Reference ID])-underscore1)
VAR underscore2 = FIND("_",string1,1,0)
VAR string2 = MID(string1,underscore2+1,LEN(string1)-underscore2)
VAR underscore3 = FIND("_",string2,1,0)
VAR string3 = MID(string2,underscore3+1,LEN(string2)-underscore3)
RETURN SUBSTITUTE(string3,"_"," ")
    • MusterBuster's avatar
      MusterBuster
      Regular Visitor
      8 years ago
      Greg_Decklerwrote:

      This is very brute force:

       

      Column = 
VAR underscore1 = FIND("_",[Reference ID],1,0)
VAR string1 = MID([Reference ID],underscore1+1,LEN([Reference ID])-underscore1)
VAR underscore2 = FIND("_",string1,1,0)
VAR string2 = MID(string1,underscore2+1,LEN(string1)-underscore2)
VAR underscore3 = FIND("_",string2,1,0)
VAR string3 = MID(string2,underscore3+1,LEN(string2)-underscore3)
RETURN SUBSTITUTE(string3,"_"," ")

      Thanks Greg!

       

      I may be doing something daft, but I'm getting a Token Eof expected error on the first variable (underscore1)

       

      MB

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        8 years ago

        I'm in the US so perhaps it's an issue where you need to replace the commas with semicolons? Also, this is DAX so it is in the Desktop, not the Query Editor (M).