Forum Discussion
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 Trustpil...
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,"_"," ")
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,"_"," ")
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
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).