Forum Discussion
Helper IIExtract 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
Solution SupplierHi FlyKick
You could try an in statement that has the text.contains as the expression first.
I.e. search within the text for the [ or ] then extract the text between otherwise its just the text.
https://docs.microsoft.com/en-us/powerquery-m/text-contains
Hope this helps
Thanks,
George
Helper IIHey 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"
- judspud
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
- FlyKick
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.....