Forum Discussion
Filter within a field
Great. We will start by extracting your first data point, the one that follow TargetUserName. First, make a copy of the Event Data column. You do this by first selecting the query in the Query Pane on the left hand side of your screen. Then click on the Add Column menu selection at the top of the Power Query window. Select the Event Data column in your table by clicking on the header. Then click Duplicate Column. You should now get a new column called "Event Data - Copy".
Select this new column. Then click Extract in the menu and click Text Between Delimiters in the resulting drop-down menu. In the resulting dialog box, enter the text
TargetUserName">
as Start delimiter and
</Data>
as the End delimiter. Click OK. The column should now only contain the text you wanted for the first data point as per the pic you attached to your original question. If not, please carefully examine the delimiters to make sure that they match the start and end point of the text you want extracted. Once satisfied, rename this new column to something that makes sense to you (perhaps TargetUserName in this example) by editing the name in the column header.
To complete the remaining data points you just have to repeat these steps for each one, only changing the delimiters accordingly. Hopefully this works, and if not please let me know what problems you ran into. Good luck!
MarcelBeug is right, this is best (and easily) done in Power Query. Here is how:
Enter the query editor and select your data source. Using your example, start by creating two extra custom columns that are just a copy of your EventData column. Then select your first EventData column and use the Extract - Text Between Deliminators menu choice under the Transform menu. Specify
TargetUserName">
as the starting deliminator and
</Data>
as the ending deliminator. You now have the first column done. Repeat for the other columns. Rename columns as appropriate.
For completeness I should mention that instead of duplicating the columns, you could start by splitting column EventData by deliminator </Data>, remove the resulting columns that contain unwanted data and perform the Extract step on the remaining columns. Not sure which version I prefer frankly but if the data file is huge, this method may intuitively have better performance . I am never quite sure about that though, I've been surprised before.