Replacing text

[IanDavies]

I have some exported data that is imported to PowerQuery as a TAB separated file and one of the columns has the data in this format:

{'Group_tags': ['GMGB'], 'Host_tags': []}

I'd like to be able to extract just the TAG (GMGB) without any of the surrounding key names or punctuation. A simple text replace doesn't work, I assume because there are special or control characters in the text.

There is only ever one TAG and always in Group_tags

Is there a way to get to the desired output?

Ian

[IanDavies]

Split Column worked for my purposes. I did two splits, the first one on one position and the second one on the lefy most occurence of a delimiter.

It feels like a fudge but gives me what I need.

[IanDavies]

UPDATE:

I was able to do this by splitting on the apostrophes and removing all unwanted columns

[Greg_Deckler · Accepted solution]

@IanDavies So you want the GMGB? You could use split column potentially. You could also use RegEx expressions in an R or Python script step.

[IanDavies]

AFAIK I cant use DAX. The recipients of the final output will only have Excel hence the question in a Power Query forum.

Not familiar with R ... could do python but not sure I want to (REGEX yuck). I will research how to do this. If you've got pointers to get me started I'd appreciate it.

I'll try the split column route and see what happens