How to properly parse this nmap gnmap file?

[JChris]

I have a file like that:

Host: 192.168.0.13 (randomname1.company.test) Status: Up
Host: 192.168.0.13 (randomname2.company.test) Ports: 21/open/tcp//ftp//Super FTP 1.0/, 80/open/tcp//http//thttpd/, 443/open/tcp//ssl|http//thttpd/, 8000/open/tcp//http//thttpd/, 9500/open/tcp//ismserver?/// Ignored State: filtered (986)
Host: 192.168.0.14 (randomname3.company.test) Status: Up
Host: 192.168.0.14 (randomname4.company.test) Ports: 80/open/tcp//http//Microsoft IIS httpd 1.0/, 135/open/tcp//msrpc//Microsoft Windows RPC/, 139/open/tcp//netbios-ssn//Microsoft Windows netbios-ssn/ Ignored State: filtered (989)
Host: 192.168.0.15 (randomname5.company.test) Status: Up
Host: 192.168.0.15 (randomname6.company.test) Ports: 21/open/tcp//ftp//Omega FTP/, 9500/open/tcp//ismserver?/// Ignored State: filtered (989)

I want to parse it in PBI in a way that I will have a table with IP, Hostname, port, status, type, serivce. So, in this example I would have something like:

192.168.0.13, randomname1.company.test, 21, open, ftp, Super FTP 1.0

192.168.0.13, randomname1.company.test, 80, open, http, httpd

192.168.0.14, randomname2.company.test, 80, open, http, Microsoft IIS httpd 1.0

And so on...

Is there a way?

[v-yuta-msft]

Hi JChris,

So your requirement is to clean your data in file, right?

On way is to use Split Column, click Query Editor-> Transform-> Split Column by custom delimiter "\\" then delete useless columns.

The second way is to use R script in Power BI, you can use function like Split() with Regular expression in it to clean your data.

Regards,

Jimmy Tao