Extract a Location Name from ugly data

[MusterBuster]

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:

Date	Content	Rating	Type	Reference ID
28/02/2018	gfagfegfei	5	Invited	Brand_Name_StoreType_Walsall_Service
28/02/2018	ieohgeoighoie	3	Invited	Brand_Name_StoreType_Ashton
29/02/2018	jgpoieqjgij	2	Organic	Brand_Name_StoreType_St_Albans
29/02/2018	gmjiejhgoij	5	Invited	Brand_Name_StoreType_Kilbride_Service
29/02/2018	jegopijopg	1	Invited	Brand_Name_StoreType_Edinburgh_(VM)
29/02/2018	egjgpoj	2	Invited	Brand_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 · Accepted solution]

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]

---

@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]

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

[MFelix]

Hi @MusterBuster,

Is the Brand_Name also variable or is it the same?

Regards,

MFelix

[MusterBuster]

---

@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