Extract data from CSV / log file into PowerBI

[starpiter]

We receive a CSV file every 24 hours with activity - From this file, we extract the run times and dates.

I am trying to replicate the sheet I have in Excel in PowerBI, I’m a little rusty on PBI and in need of some help.

I have tried to Conditional Columns, unpivoting and pivoting columns and still going blank.

In short I need to.

• Import CSV / Log file 
• Assign each group (Time batch) assign a URN
• Each group on a new row, extracting the details and pupulating this data into a column
• Which will allow complete sum on rows i.e. (=Sum H - G)
• Confirm average time for 24hr period

https://1drv.ms/f/s!Aqw9t5Hm7znwgfxiSrgJ9IsRRYiX1g

1. Full Log (anonomised)
2. Extract log of 1 timestamp
3. Mock report - my lame attempt where i failed

Excel view

Log file with fields mapped to below

Raw log file - 1 x Group (Time batch) - *[204] cannot be used as the URN as represents other data.

14.11.2022 09:22:02 [ 204]
14.11.2022 09:22:02 [ 204] prsim.pl started at Mon Nov 14 09:22:02 2022
14.11.2022 09:22:02 [ 204] Redundancy Group ServerRole = standalone
14.11.2022 09:22:02 [ 204] ### ctrl-PRSIM.pl
14.11.2022 09:22:02 [ 204] ### cyclectrl-host= <Server>
14.11.2022 09:22:02 [ 204] ### controller-id = 101
14.11.2022 09:22:02 [ 204] ### net = -NTS_ONLINE_LIVE_NETWORK
14.11.2022 09:22:02 [ 204] ### cycle_ini = .\cycle
14.11.2022 09:22:02 [ 204] ### cycle_time = 120
14.11.2022 09:22:02 [ 204] ### SIMONE_OFFSET = 240
14.11.2022 09:22:02 [ 204] PRSIM->RDATUM: 14-NOV-22
14.11.2022 09:22:02 [ 204] PRSIM->INITIME: 09:16
14.11.2022 09:22:02 [ 204] PRSIM->TERMTIME: 09:18
14.11.2022 09:22:02 [ 204] Start \\<Server>\SIMROOT\exe\SDSDPP_CC.exe: Mon Nov 14 09:22:02 2022
14.11.2022 09:22:05 [ 204] End \\<Server>\SIMROOT\exe\SDSDPP_CC.exe: Mon Nov 14 09:22:05 2022 code=0
14.11.2022 09:22:06 [ 204] Start rdf2sim: Mon Nov 14 09:22:06 2022
14.11.2022 09:22:07 [ 204] End rdf2sim: Mon Nov 14 09:22:07 2022 code=0
14.11.2022 09:22:07 [ 204] Start Reconstruction: Mon Nov 14 09:22:07 2022
14.11.2022 09:22:18 [ 204] End Reconstruction: Mon Nov 14 09:22:18 2022
14.11.2022 09:22:18 [ 204] ZV_SIMUL terminated: retcode=0, EXSTAT=RUNOK
14.11.2022 09:22:18 [ 204] rdf purgetime -24
14.11.2022 09:22:19 [ 204] Start \\<Server>\SIMROOT\exe\SODThinClient.exe: Mon Nov 14 09:22:19 2022
14.11.2022 09:22:22 [ 204] End \\<Server>\SIMROOT\exe\SODThinClient.exe: Mon Nov 14 09:22:22 2022 code=0
14.11.2022 09:22:22 [ 204] PRSIM return code = 0
14.11.2022 09:22:22 [ 204] PRSIM Exit code = 0
14.11.2022 09:22:22 [ 204] PRSIM runtime = 20 [sec]
14.11.2022 09:22:22 [ 204] ================================================

[lbendlin]

Wow, that is a really bad logfile format. Calling that a CSV is a bit of a stretch.  Can you provide a larger sample (with more than one group)  and indicate the expected outcome?

[starpiter]

@lbendlin Yes - totally agree with the format 'not being the best' 🙂

Ive anonomised the log and uploaded here, also my poor attempt to try and replicate (out of practise with PowerBI since i changed roles)

https://1drv.ms/f/s!Aqw9t5Hm7znwgfxiSrgJ9IsRRYiX1g 

[lbendlin · Accepted solution]

This was an interesting challenge, but it is still a horrible report, and I don't think Power BI is the right tool for that.

[starpiter]

Wow - thank you - i am clearly out of practise, a few basics i missed there and new features i wasnt aware of. Really apprecaite your help. i'll keep you posted how i get on,  you went above and beyond my expectations thank you 🙂 

[lbendlin]

Beware of the events that cross day boundaries - the duration calculation needs to be adjusted.  And find a better way to identify the individual instances - I made a crude assumption that each run would finish inside 60 seconds.