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