Forum Discussion
CSV formatted data
Hello
First dumb post from me.
We have a script which records out-of-hours logons by staff (e.g when staff log on at the weekend, or at 1.30am, that kind of thing). Each event is recorded as single text file named after that member of staff with the reason the event was logged, in a dated folder.
You'll probably be relieved to hear that these txt files are NOT being used in Power BI! Instead, just after midnight each day, another script runs which amalgamates the previous day's logs in to a single .CSV file which looks like this:
| DATE | TIME | STAFF MEMBER | ACTIVITY | REASON |
| 25/12/2020 | 20:05 | Mark | LOGON | BANK HOLIDAY |
| 25/12/2020 | 20:36 | Mark | LOGOFF | BANK HOLIDAY |
| 26/12/2020 | 08:12 | Mike | LOGON | BANK HOLIDAY |
| 27/12/2020 | 07:18 | Mike | LOGON | OFFICE CLOSED |
| 27/12/2020 | 08:25 | Robert | LOGON | OFFICE CLOSED |
| 28/12/2020 | 12:05 | Albert | LOGON | OFFICE CLOSED |
| 28/12/2020 | 09:18 | James | LOGON | OFFICE CLOSED |
| 28/12/2020 | 17:43 | James | LOGON | OFFICE CLOSED |
| 28/12/2020 | 13:10 | Mark | LOGON | OFFICE CLOSED |
| 28/12/2020 | 10:32 | Rachel | LOGON | OFFICE CLOSED |
| 28/12/2020 | 15:32 | Rachel | LOGON | OFFICE CLOSED |
| 29/12/2020 | 14:18 | Helen | LOGON | OFFICE CLOSED |
| 29/12/2020 | 14:52 | Helen | LOGOFF | OFFICE CLOSED |
| 29/12/2020 | 11:44 | James | LOGON | OFFICE CLOSED |
| 29/12/2020 | 13:01 | James | LOGOFF | OFFICE CLOSED |
| 29/12/2020 | 15:54 | James | LOGON | OFFICE CLOSED |
| 29/12/2020 | 17:24 | James | LOGOFF | OFFICE CLOSED |
| 29/12/2020 | 10:56 | Rachel | LOGON | OFFICE CLOSED |
| 29/12/2020 | 17:40 | Rachel | LOGOFF | OFFICE CLOSED |
| 29/12/2020 | 23:42 | Timothy | LOGON | OFFICE CLOSED |
| 30/12/2020 | 15:37 | Andrea | LOGON | OFFICE CLOSED |
| 30/12/2020 | 17:40 | Andrea | LOGOFF | OFFICE CLOSED |
| 30/12/2020 | 21:52 | Nicola | LOGON | OFFICE CLOSED |
| 30/12/2020 | 22:31 | Nicola | LOGOFF | OFFICE CLOSED |
| 30/12/2020 | 10:35 | Robert | LOGON | OFFICE CLOSED |
| 30/12/2020 | 11:28 | Robert | LOGOFF | OFFICE CLOSED |
| 30/12/2020 | 02:21 | Timothy | LOGOFF | OFFICE CLOSED |
| 30/12/2020 | 20:58 | Timothy | LOGON | OFFICE CLOSED |
| 30/12/2020 | 22:48 | Timothy | LOGOFF | OFFICE CLOSED |
| 31/12/2020 | 11:43 | James | LOGON | OFFICE CLOSED |
| 31/12/2020 | 13:01 | James | LOGOFF | OFFICE CLOSED |
| 31/12/2020 | 16:21 | James | LOGON | OFFICE CLOSED |
| 31/12/2020 | 17:13 | James | LOGOFF | OFFICE CLOSED |
| 31/12/2020 | 16:50 | Kathy | LOGON | OFFICE CLOSED |
| 31/12/2020 | 18:47 | Kathy | LOGOFF | OFFICE CLOSED |
| 31/12/2020 | 08:09 | Mike | LOGON | OFFICE CLOSED |
| 31/12/2020 | 10:31 | Mike | LOGOFF | OFFICE CLOSED |
| 31/12/2020 | 09:15 | Rachel | LOGON | OFFICE CLOSED |
| 31/12/2020 | 13:28 | Rachel | LOGOFF | OFFICE CLOSED |
| 01/01/2021 | 15:46 | Kathy | LOGON | BANK HOLIDAY |
| 01/01/2021 | 18:34 | Kathy | LOGOFF | BANK HOLIDAY |
| 01/01/2021 | 13:21 | Linda | LOGON | BANK HOLIDAY |
| 01/01/2021 | 14:21 | Linda | LOGOFF | BANK HOLIDAY |
| 01/01/2021 | 16:16 | Mortimer | LOGON | BANK HOLIDAY |
| 01/01/2021 | 17:43 | Mortimer | LOGOFF | BANK HOLIDAY |
| 01/01/2021 | 17:50 | Mortimer | LOGON | BANK HOLIDAY |
| 01/01/2021 | 18:31 | Mortimer | LOGOFF | BANK HOLIDAY |
| 01/01/2021 | 12:14 | Rachel | LOGON | BANK HOLIDAY |
| 01/01/2021 | 17:18 | Rachel | LOGOFF | BANK HOLIDAY |
| 01/01/2021 | 15:53 | Toby | LOGON | BANK HOLIDAY |
| 01/01/2021 | 17:57 | Toby | LOGOFF | BANK HOLIDAY |
| 01/01/2021 | 10:58 | Victoria | LOGON | BANK HOLIDAY |
| 01/01/2021 | 11:55 | Victoria | LOGOFF | BANK HOLIDAY |
| 02/01/2021 | 21:20 | Albert | LOGON | WEEKEND |
| 02/01/2021 | 22:24 | Albert | LOGOFF | WEEKEND |
| 02/01/2021 | 15:53 | Elizabeth | LOGON | WEEKEND |
| 02/01/2021 | 17:19 | Elizabeth | LOGOFF | WEEKEND |
| 02/01/2021 | 12:51 | James | LOGON | WEEKEND |
| 02/01/2021 | 14:10 | James | LOGOFF | WEEKEND |
| 02/01/2021 | 17:43 | Kathy | LOGON | WEEKEND |
| 02/01/2021 | 18:14 | Kathy | LOGOFF | WEEKEND |
| 02/01/2021 | 11:16 | Mark | LOGON | WEEKEND |
| 02/01/2021 | 12:43 | Mark | LOGOFF | WEEKEND |
| 02/01/2021 | 10:54 | Rachel | LOGON | WEEKEND |
| 02/01/2021 | 14:43 | Rachel | LOGOFF | WEEKEND |
| 03/01/2021 | 21:23 | Albert | LOGOFF | WEEKEND |
| 03/01/2021 | 22:07 | Albert | LOGON | WEEKEND |
| 03/01/2021 | 22:43 | Albert | LOGOFF | WEEKEND |
| 03/01/2021 | 14:00 | Carl | LOGON | WEEKEND |
| 03/01/2021 | 16:43 | Carl | LOGOFF | WEEKEND |
| 03/01/2021 | 16:36 | Nell | LOGON | WEEKEND |
| 03/01/2021 | 20:02 | Nell | LOGOFF | WEEKEND |
| 03/01/2021 | 22:13 | Nicola | LOGON | WEEKEND |
| 03/01/2021 | 22:48 | Nicola | LOGOFF | WEEKEND |
| 03/01/2021 | 11:15 | Rachel | LOGON | WEEKEND |
| 03/01/2021 | 12:16 | Rachel | LOGOFF | WEEKEND |
| 03/01/2021 | 12:25 | Rachel | LOGON | WEEKEND |
| 03/01/2021 | 15:52 | Rachel | LOGOFF | WEEKEND |
| 03/01/2021 | 22:52 | Rachel | LOGON | WEEKEND |
| 03/01/2021 | 22:19 | Timothy | LOGON | WEEKEND |
| 04/01/2021 | 18:57 | Andrew | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 21:42 | Albert | LOGON | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 22:40 | Albert | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 19:13 | Agatha | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 07:46 | Brian | LOGON | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 20:30 | David | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 07:27 | Devon | LOGON | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 19:18 | Jimmy | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 20:53 | Jimmy | LOGON | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 21:46 | Jimmy | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 18:13 | James | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 18:06 | Kathy | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
| 04/01/2021 | 20:14 | Terrie | LOGOFF | OUTSIDE OF NORMAL WORKING HOURS |
I've changed the names, but the rest of the data is real (yes, someone really did login on Christmas Day!)
Anyway, this data is meant for HR to see if they can identify workload issues, and my idea is to produce an easy to read/use Power BI Dashboard which they can use to click on to see who logged in on a certain date, and/or click on a staff member's name to see how many times that person is logging on outside of normal work hours.
However, I know little-to-nothing about Power BI and so am struggling to understand how to use it to achieve this.
So, my question is, is the data we've got in a suitable format (can this be achieved with a .csv file containing data like the sample above)? I watched a 60 minute long YT video by Avi Singh which seemed really good, but his sample data had what seemed to be a significant advantage in that it was in an Excel format spreadsheet, with separate tables
Yes, your data is set up to get you started on your Report.
Please refer to attached file - I have done a couple of Table Visuals to get you started.
Hope you can take it from here....
Good Luck and Regards,
5 Replies
- jpjefferyNew Member
Here's that sample data again, in what I hope will be an easier format to use/refer to:
DATE TIME STAFF MEMBER ACTIVITY REASON 25/12/2020 20:05 Mark LOGON BANK HOLIDAY 25/12/2020 20:36 Mark LOGOFF BANK HOLIDAY 26/12/2020 08:12 Mike LOGON BANK HOLIDAY 27/12/2020 07:18 Mike LOGON OFFICE CLOSED 27/12/2020 08:25 Robert LOGON OFFICE CLOSED 28/12/2020 12:05 Albert LOGON OFFICE CLOSED 28/12/2020 09:18 James LOGON OFFICE CLOSED 28/12/2020 17:43 James LOGON OFFICE CLOSED 28/12/2020 13:10 Mark LOGON OFFICE CLOSED 28/12/2020 10:32 Rachel LOGON OFFICE CLOSED 28/12/2020 15:32 Rachel LOGON OFFICE CLOSED 29/12/2020 14:18 Helen LOGON OFFICE CLOSED 29/12/2020 14:52 Helen LOGOFF OFFICE CLOSED 29/12/2020 11:44 James LOGON OFFICE CLOSED 29/12/2020 13:01 James LOGOFF OFFICE CLOSED 29/12/2020 15:54 James LOGON OFFICE CLOSED 29/12/2020 17:24 James LOGOFF OFFICE CLOSED 29/12/2020 10:56 Rachel LOGON OFFICE CLOSED 29/12/2020 17:40 Rachel LOGOFF OFFICE CLOSED 29/12/2020 23:42 Timothy LOGON OFFICE CLOSED 30/12/2020 15:37 Andrea LOGON OFFICE CLOSED 30/12/2020 17:40 Andrea LOGOFF OFFICE CLOSED 30/12/2020 21:52 Nicola LOGON OFFICE CLOSED 30/12/2020 22:31 Nicola LOGOFF OFFICE CLOSED 30/12/2020 10:35 Robert LOGON OFFICE CLOSED 30/12/2020 11:28 Robert LOGOFF OFFICE CLOSED 30/12/2020 02:21 Timothy LOGOFF OFFICE CLOSED 30/12/2020 20:58 Timothy LOGON OFFICE CLOSED 30/12/2020 22:48 Timothy LOGOFF OFFICE CLOSED 31/12/2020 11:43 James LOGON OFFICE CLOSED 31/12/2020 13:01 James LOGOFF OFFICE CLOSED 31/12/2020 16:21 James LOGON OFFICE CLOSED 31/12/2020 17:13 James LOGOFF OFFICE CLOSED 31/12/2020 16:50 Kathy LOGON OFFICE CLOSED 31/12/2020 18:47 Kathy LOGOFF OFFICE CLOSED 31/12/2020 08:09 Mike LOGON OFFICE CLOSED 31/12/2020 10:31 Mike LOGOFF OFFICE CLOSED 31/12/2020 09:15 Rachel LOGON OFFICE CLOSED 31/12/2020 13:28 Rachel LOGOFF OFFICE CLOSED 01/01/2021 15:46 Kathy LOGON BANK HOLIDAY 01/01/2021 18:34 Kathy LOGOFF BANK HOLIDAY 01/01/2021 13:21 Linda LOGON BANK HOLIDAY 01/01/2021 14:21 Linda LOGOFF BANK HOLIDAY 01/01/2021 16:16 Mortimer LOGON BANK HOLIDAY 01/01/2021 17:43 Mortimer LOGOFF BANK HOLIDAY 01/01/2021 17:50 Mortimer LOGON BANK HOLIDAY 01/01/2021 18:31 Mortimer LOGOFF BANK HOLIDAY 01/01/2021 12:14 Rachel LOGON BANK HOLIDAY 01/01/2021 17:18 Rachel LOGOFF BANK HOLIDAY 01/01/2021 15:53 Toby LOGON BANK HOLIDAY 01/01/2021 17:57 Toby LOGOFF BANK HOLIDAY 01/01/2021 10:58 Victoria LOGON BANK HOLIDAY 01/01/2021 11:55 Victoria LOGOFF BANK HOLIDAY 02/01/2021 21:20 Albert LOGON WEEKEND 02/01/2021 22:24 Albert LOGOFF WEEKEND 02/01/2021 15:53 Elizabeth LOGON WEEKEND 02/01/2021 17:19 Elizabeth LOGOFF WEEKEND 02/01/2021 12:51 James LOGON WEEKEND 02/01/2021 14:10 James LOGOFF WEEKEND 02/01/2021 17:43 Kathy LOGON WEEKEND 02/01/2021 18:14 Kathy LOGOFF WEEKEND 02/01/2021 11:16 Mark LOGON WEEKEND 02/01/2021 12:43 Mark LOGOFF WEEKEND 02/01/2021 10:54 Rachel LOGON WEEKEND 02/01/2021 14:43 Rachel LOGOFF WEEKEND 03/01/2021 21:23 Albert LOGOFF WEEKEND 03/01/2021 22:07 Albert LOGON WEEKEND 03/01/2021 22:43 Albert LOGOFF WEEKEND 03/01/2021 14:00 Carl LOGON WEEKEND 03/01/2021 16:43 Carl LOGOFF WEEKEND 03/01/2021 16:36 Nell LOGON WEEKEND 03/01/2021 20:02 Nell LOGOFF WEEKEND 03/01/2021 22:13 Nicola LOGON WEEKEND 03/01/2021 22:48 Nicola LOGOFF WEEKEND 04/01/2021 20:30 David LOGOFF OUTSIDE OF NORMAL WORKING HOURS 04/01/2021 07:27 Devon LOGON OUTSIDE OF NORMAL WORKING HOURS 04/01/2021 19:18 Jimmy LOGOFF OUTSIDE OF NORMAL WORKING HOURS 04/01/2021 20:53 Jimmy LOGON OUTSIDE OF NORMAL WORKING HOURS 04/01/2021 21:46 Jimmy LOGOFF OUTSIDE OF NORMAL WORKING HOURS 04/01/2021 18:13 James LOGOFF OUTSIDE OF NORMAL WORKING HOURS 04/01/2021 18:06 Kathy LOGOFF OUTSIDE OF NORMAL WORKING HOURS 04/01/2021 20:14 Terrie LOGOFF OUTSIDE OF NORMAL WORKING HOURS - rsbinCommunity Champion
Yes, your data is set up to get you started on your Report.
Please refer to attached file - I have done a couple of Table Visuals to get you started.
Hope you can take it from here....
Good Luck and Regards,
- jpjefferyNew Member
Great stuff, rsbin. Thank you.
I'm trying to replace the existing dummy data with our real data, following this article: How to replace Data Source for existing report
But when I get to the Data source settings dialogue it's proving somewhat uncooperative...
Is this the right way to replace the data, or is there another?
- jpjefferyNew Member
Using Transform Data as you described threw up some difficulties, BUT, looking carefully at your sample file (it took me a few goes) showed me how you added those COUNT columns, at which point I was on my way!
Look at this thing of beauty...!
So, thanks for your help, rsbin! I really appreciate it.