Extract Text Between Two Delimiters Only If The Delimiter Exists

[FlyKick]

Gday All,

I have a column with IP addresses in varying formats. Some are correct but some exist between two [ ] brackets. E.g.

• 111.111.111.111
• 1111:1111:1111:1111:1111:1111:1111:1111
• [111.111.111.111]:10828
• [1111:1111:1111:1111:1111:1111:1111:1111]:19044

I have tried using extract text between two delimiters in the Power Query Editor and this works but it nukes all the correctly formatted entries that don't have the delimiters. I.e. these entries get wiped.

• 111.111.111.111
• 1111:1111:1111:1111:1111:1111:1111:1111

Is their a way to run the extract and just ignore cells that don't contain the delimiter character? 

Thanks in advance for your help.... 

Cheers,

Fly Kick

[judspud]

Hi @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

[FlyKick]

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"

[judspud · Accepted solution]

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.....