create a new column with IP addresses, based on column with different values with IP address in them

[WtP]

I have a log overview from a firewall. What I want to achieve is to split the IP addresses from the source column into a seperate column. But here is where it gets tricky. The column source looks like below:

Source

192.168.2.150

Asset A (192.168.6.40)

host-192.168.2.50 (192.168.9.50)

hostb.examplecorp.com (192.168.4.60)

asset-v525-192.168.2.88 (192.168.1.88)

What I would like to have is the following:

Source	Source IP address
192.168.2.150	192.168.2.150
Asset A (192.168.6.40)	192.168.6.40
host-192.168.9.50 (192.168.9.50)	192.168.9.50
hostb.examplecorp.com (192.168.4.60)	192.168.4.60
asset-v525-192.168.1.88 (192.168.1.88)	192.168.1.88

As you would understand the IP addresses are there as an example and do not reflect the actual ranges. The IP addresses do differ.

I mainly was trying this in DAX using the left and right command which gives me different results all not what I need. I also tried my way through this using power query. This also worked kind of. the IP addresses could be split by the "(" but then the cells with only an IP address would stay. Adding an "(" in from of all IP adresses also resulted in a lot of issues like i.e.: host-(192, ((192, asset-v525-(192, etc.

hope you can help me because I have no clue where else to look for answers.

This will be my last ditch effort. If this doesn't work, I'll start splitting data at the source document level. (I am trying to avoid this)

[ryan_mayu · Accepted solution]

@WtP 

Here is a workaround for you.

create a column from example

then you type in the expected output one by one, it will automatically help you extract the data. You need to modify the one that does not display the expected output.

[v-yanjiang-msft · Accepted solution]

Hi @WtP ,

According to your description, here's my solution. Create a custom column.

if Text.Contains([Source],"(") then Text.BetweenDelimiters([Source], "(", ")") else [Source]

Get the result.

Here's the whole M syntax.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ00jM0s9ADkqYGSrE60UqOxcWpJQqOChowKTM9EwNNsFRGfnGJLkKHqQFCkSWQh1CUpJdakZhbkJOanF9UoJecn4tQaKJnBlWYCLJIt8zUyBTJTAsLhFJDIA+oNBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Source],"(") then Text.BetweenDelimiters([Source], "(", ")") else [Source])
in
    #"Added Custom"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WtP]

Hi @v-yanjiang-msft ,

Thank you for your reply. It was very helpfull.

As I am new to the whole PowerBI what would be the difference between the first syntax and the M syntax? and if I have to use the M syntax, where do I have to put it?

Thank you in advance.

[v-yanjiang-msft]

Hi @WtP ,

My pleasure!

Please follow the steps:

1.In Desktop, select Transform data.

2.In Power Query, right-click the blank field under Queries, select New Query>Blank Query.

3.Click Advanced Editor, then copy-paste the whole M syntax I provided in the previous post to see the details.

If you only want to add a custom column, just click Custom Column in Power Query.

Then put in this code:

if Text.Contains([Source],"(") then Text.BetweenDelimiters([Source], "(", ")") else [Source]

Certainly, maybe you should modify the table/column names according to your sample.

Best Regards,
Community Support Team _ kalyj

[ryan_mayu · Accepted solution]

@WtP 

Here is a workaround for you.

create a column from example

then you type in the expected output one by one, it will automatically help you extract the data. You need to modify the one that does not display the expected output.

[WtP]

Hi @ryan_mayu 

The other solution solved it. I just wanted to point out that I also tried your solution and this works realy well also. I am sure I'll be using this option a lot in the future because I think it is very flexible, so thank you for your reply!

Kind regards.