Forum Discussion
Extract Text Between Two Delimiters Only If The Delimiter Exists
Hi FlyKick
Please try the following formula as a custom column within Query Editor. You will need to change [IP] to your column name.
if Text.Contains([IP],"[") then Text.BetweenDelimiters([IP], "[", "]") as text else [IP]Please mark this as a solution if it provides the data you require.
Thanks,
George
Hey George,
Thanks for the tip. I'm pretty new to this whole Power BI thing. Just to clarify I need to go into the advanced query editor to complete this correct? I just tried adding the following line.
- Text.Contains(#"Expanded AuditData.ClientIP", "[")
It erorrs with Token Eof expected? I have pasted what is currently displayed in the advanced query editor below in case its of assistance?
let
Source = Csv.Document(File.Contents("C:\tempp\AuditLog.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CreationDate", type datetime}, {"UserIds", type text}, {"Operations", type text}, {"AuditData", type text}}),
#"Parsed JSON" = Table.TransformColumns(#"Changed Type",{{"AuditData", Json.Document}}),
#"Expanded AuditData" = Table.ExpandRecordColumn(#"Parsed JSON", "AuditData", {"CreationTime", "Id", "Operation", "OrganizationId", "RecordType", "ResultStatus", "UserKey", "UserType", "Version", "Workload", "ClientIP", "UserId", "ClientIPAddress", "ClientInfoString", "ClientProcessName", "ClientVersion", "ExternalAccess", "InternalLogonType", "LogonType", "LogonUserSid", "MailboxGuid", "MailboxOwnerSid", "MailboxOwnerUPN", "OrganizationName", "OriginatingServer", "SessionId", "AffectedItems", "CrossMailboxOperation", "DestFolder", "Folder", "ObjectId", "CorrelationId", "EventSource", "ItemType", "ListId", "ListItemUniqueId", "Site", "UserAgent", "WebId", "SourceFileExtension", "SiteUrl", "SourceFileName", "SourceRelativeUrl", "OperationProperties", "Item", "ActivityParameters", "FormsUserType", "SourceApp", "AzureActiveDirectoryEventType", "ExtendedProperties", "ModifiedProperties", "Actor", "ActorContextId", "ActorIpAddress", "InterSystemsId", "IntraSystemId", "SupportTicketId", "Target", "TargetContextId", "ApplicationId", "FormId", "FormName", "LogonError", "MachineDomainInfo", "MachineId", "FileSyncBytesCommitted", "ImplicitShare", "ApplicationDisplayName", "DestinationFileExtension", "DestinationFileName", "DestinationRelativeUrl", "ExchangeDetails", "DoNotDistributeEvent"}, {"AuditData.CreationTime", "AuditData.Id", "AuditData.Operation", "AuditData.OrganizationId", "AuditData.RecordType", "AuditData.ResultStatus", "AuditData.UserKey", "AuditData.UserType", "AuditData.Version", "AuditData.Workload", "AuditData.ClientIP", "AuditData.UserId", "AuditData.ClientIPAddress", "AuditData.ClientInfoString", "AuditData.ClientProcessName", "AuditData.ClientVersion", "AuditData.ExternalAccess", "AuditData.InternalLogonType", "AuditData.LogonType", "AuditData.LogonUserSid", "AuditData.MailboxGuid", "AuditData.MailboxOwnerSid", "AuditData.MailboxOwnerUPN", "AuditData.OrganizationName", "AuditData.OriginatingServer", "AuditData.SessionId", "AuditData.AffectedItems", "AuditData.CrossMailboxOperation", "AuditData.DestFolder", "AuditData.Folder", "AuditData.ObjectId", "AuditData.CorrelationId", "AuditData.EventSource", "AuditData.ItemType", "AuditData.ListId", "AuditData.ListItemUniqueId", "AuditData.Site", "AuditData.UserAgent", "AuditData.WebId", "AuditData.SourceFileExtension", "AuditData.SiteUrl", "AuditData.SourceFileName", "AuditData.SourceRelativeUrl", "AuditData.OperationProperties", "AuditData.Item", "AuditData.ActivityParameters", "AuditData.FormsUserType", "AuditData.SourceApp", "AuditData.AzureActiveDirectoryEventType", "AuditData.ExtendedProperties", "AuditData.ModifiedProperties", "AuditData.Actor", "AuditData.ActorContextId", "AuditData.ActorIpAddress", "AuditData.InterSystemsId", "AuditData.IntraSystemId", "AuditData.SupportTicketId", "AuditData.Target", "AuditData.TargetContextId", "AuditData.ApplicationId", "AuditData.FormId", "AuditData.FormName", "AuditData.LogonError", "AuditData.MachineDomainInfo", "AuditData.MachineId", "AuditData.FileSyncBytesCommitted", "AuditData.ImplicitShare", "AuditData.ApplicationDisplayName", "AuditData.DestinationFileExtension", "AuditData.DestinationFileName", "AuditData.DestinationRelativeUrl", "AuditData.ExchangeDetails", "AuditData.DoNotDistributeEvent"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded AuditData",{{"AuditData.CreationTime", "AuditData.CreationTimeUTC"}, {"CreationDate", "CreationDate(Local)"}}),
#"Expanded AuditData.DestFolder" = Table.ExpandRecordColumn(#"Renamed Columns", "AuditData.DestFolder", {"Id", "Path"}, {"AuditData.DestFolder.Id", "AuditData.DestFolder.Path"}),
#"Expanded AuditData.Folder" = Table.ExpandRecordColumn(#"Expanded AuditData.DestFolder", "AuditData.Folder", {"Id", "Path"}, {"AuditData.Folder.Id", "AuditData.Folder.Path"}),
#"Expanded AuditData.AffectedItems" = Table.ExpandListColumn(#"Expanded AuditData.Folder", "AuditData.AffectedItems"),
#"Expanded AuditData.AffectedItems1" = Table.ExpandRecordColumn(#"Expanded AuditData.AffectedItems", "AuditData.AffectedItems", {"Attachments", "Id", "InternetMessageId", "ParentFolder", "Subject"}, {"AuditData.AffectedItems.Attachments", "AuditData.AffectedItems.Id", "AuditData.AffectedItems.InternetMessageId", "AuditData.AffectedItems.ParentFolder", "AuditData.AffectedItems.Subject"}),
#"Expanded AuditData.Item" = Table.ExpandRecordColumn(#"Expanded AuditData.AffectedItems1", "AuditData.Item", {"Id", "ParentFolder"}, {"AuditData.Item.Id", "AuditData.Item.ParentFolder"}),
#"Expanded AuditData.Item.ParentFolder" = Table.ExpandRecordColumn(#"Expanded AuditData.Item", "AuditData.Item.ParentFolder", {"Id", "Name", "Path"}, {"AuditData.Item.ParentFolder.Id", "AuditData.Item.ParentFolder.Name", "AuditData.Item.ParentFolder.Path"}),
#"Expanded AuditData.OperationProperties" = Table.ExpandListColumn(#"Expanded AuditData.Item.ParentFolder", "AuditData.OperationProperties"),
#"Expanded AuditData.OperationProperties1" = Table.ExpandRecordColumn(#"Expanded AuditData.OperationProperties", "AuditData.OperationProperties", {"Name", "Value"}, {"AuditData.OperationProperties.Name", "AuditData.OperationProperties.Value"}),
#"Expanded AuditData.ExtendedProperties" = Table.ExpandListColumn(#"Expanded AuditData.OperationProperties1", "AuditData.ExtendedProperties"),
#"Expanded AuditData.ExtendedProperties1" = Table.ExpandRecordColumn(#"Expanded AuditData.ExtendedProperties", "AuditData.ExtendedProperties", {"Name", "Value"}, {"AuditData.ExtendedProperties.Name", "AuditData.ExtendedProperties.Value"}),
#"Expanded AuditData.ModifiedProperties" = Table.ExpandListColumn(#"Expanded AuditData.ExtendedProperties1", "AuditData.ModifiedProperties"),
#"Expanded AuditData.AffectedItems.ParentFolder" = Table.ExpandRecordColumn(#"Expanded AuditData.ModifiedProperties", "AuditData.AffectedItems.ParentFolder", {"Id", "Path"}, {"AuditData.AffectedItems.ParentFolder.Id", "AuditData.AffectedItems.ParentFolder.Path"})
in
#"Expanded AuditData.AffectedItems.ParentFolder"
Hi FlyKick
Please try the following formula as a custom column within Query Editor. You will need to change [IP] to your column name.
if Text.Contains([IP],"[") then Text.BetweenDelimiters([IP], "[", "]") as text else [IP]
Please mark this as a solution if it provides the data you require.
Thanks,
George
Hey George,
That has done the trick! Looks like I was barking up the wrong tree the whole time 😉 Truly appreciate your help and super bonus points for helping late on a Friday night! Thank You.....