Forum Discussion
Helper IIExtract Text Between Two Delimiters Only If The Delimiter Exists
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
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
4 Replies
- judspud
Solution Supplier
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
FlyKickHey 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