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.
Thanks to both of you and sorry for the delay.
I'm not sure I understand what I need to do and I'm not sure if it can be done. Our logs are pulled from the Azure cloud and our analytics system out there generated the query for Power BI. Here's what it looks like. I only removed our subscription ID in the source URL for privacy reasons.
let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://management.azure.com/subscriptions/...",
[Query=[#"query"="SecurityEvent | where EventID == 4771",#"x-ms-app"="OmsAnalyticsPBI",#"timespan"="PT24H10M",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" },
{
{ "string", Text.Type },
{ "int", Int32.Type },
{ "long", Int64.Type },
{ "real", Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool", Logical.Type },
{ "guid", Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in AnalyticsQuerySo is there anything that could be added or modified in there?
Thanks a lot.
If I am not missing something here you should be able to proceed with the steps I proposed, through the GUI of Power Query. This will modify the script you copied and add additional steps to that script.
I haven't used Azure though, how is the script you copied in your post provided to you, ist that something you manually put in Power BI or is there automation involved here?
