I have a file like that:
Host: 192.168.0.13 (randomname1.company.test) Status: UpHost: 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: UpHost: 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: UpHost: 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.
Check out the on demand sessions that are available now!
Check out the Winners!
Features releasing from October 2020 through March 2021