Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
starpiter
Regular Visitor

Extract data from CSV / log file into PowerBI

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

starpiter_0-1678977723098.png

 

Log file with fields mapped to below

 

starpiter_0-1679316175515.png

 

 

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] ================================================
1 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.

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

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?

@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 

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.

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 🙂 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.