Forum Discussion

jpjeffery's avatar
jpjeffery
New Member
3 years ago
Solved

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:

 

DATETIMESTAFF MEMBERACTIVITYREASON
25/12/202020:05MarkLOGONBANK HOLIDAY
25/12/202020:36MarkLOGOFFBANK HOLIDAY
26/12/202008:12MikeLOGONBANK HOLIDAY
27/12/202007:18MikeLOGONOFFICE CLOSED
27/12/202008:25RobertLOGONOFFICE CLOSED
28/12/202012:05AlbertLOGONOFFICE CLOSED
28/12/202009:18JamesLOGONOFFICE CLOSED
28/12/202017:43JamesLOGONOFFICE CLOSED
28/12/202013:10MarkLOGONOFFICE CLOSED
28/12/202010:32RachelLOGONOFFICE CLOSED
28/12/202015:32RachelLOGONOFFICE CLOSED
29/12/202014:18HelenLOGONOFFICE CLOSED
29/12/202014:52HelenLOGOFFOFFICE CLOSED
29/12/202011:44JamesLOGONOFFICE CLOSED
29/12/202013:01JamesLOGOFFOFFICE CLOSED
29/12/202015:54JamesLOGONOFFICE CLOSED
29/12/202017:24JamesLOGOFFOFFICE CLOSED
29/12/202010:56RachelLOGONOFFICE CLOSED
29/12/202017:40RachelLOGOFFOFFICE CLOSED
29/12/202023:42TimothyLOGONOFFICE CLOSED
30/12/202015:37AndreaLOGONOFFICE CLOSED
30/12/202017:40AndreaLOGOFFOFFICE CLOSED
30/12/202021:52NicolaLOGONOFFICE CLOSED
30/12/202022:31NicolaLOGOFFOFFICE CLOSED
30/12/202010:35RobertLOGONOFFICE CLOSED
30/12/202011:28RobertLOGOFFOFFICE CLOSED
30/12/202002:21TimothyLOGOFFOFFICE CLOSED
30/12/202020:58TimothyLOGONOFFICE CLOSED
30/12/202022:48TimothyLOGOFFOFFICE CLOSED
31/12/202011:43JamesLOGONOFFICE CLOSED
31/12/202013:01JamesLOGOFFOFFICE CLOSED
31/12/202016:21JamesLOGONOFFICE CLOSED
31/12/202017:13JamesLOGOFFOFFICE CLOSED
31/12/202016:50KathyLOGONOFFICE CLOSED
31/12/202018:47KathyLOGOFFOFFICE CLOSED
31/12/202008:09MikeLOGONOFFICE CLOSED
31/12/202010:31MikeLOGOFFOFFICE CLOSED
31/12/202009:15RachelLOGONOFFICE CLOSED
31/12/202013:28RachelLOGOFFOFFICE CLOSED
01/01/202115:46KathyLOGONBANK HOLIDAY
01/01/202118:34KathyLOGOFFBANK HOLIDAY
01/01/202113:21LindaLOGONBANK HOLIDAY
01/01/202114:21LindaLOGOFFBANK HOLIDAY
01/01/202116:16MortimerLOGONBANK HOLIDAY
01/01/202117:43MortimerLOGOFFBANK HOLIDAY
01/01/202117:50MortimerLOGONBANK HOLIDAY
01/01/202118:31MortimerLOGOFFBANK HOLIDAY
01/01/202112:14RachelLOGONBANK HOLIDAY
01/01/202117:18RachelLOGOFFBANK HOLIDAY
01/01/202115:53TobyLOGONBANK HOLIDAY
01/01/202117:57TobyLOGOFFBANK HOLIDAY
01/01/202110:58VictoriaLOGONBANK HOLIDAY
01/01/202111:55VictoriaLOGOFFBANK HOLIDAY
02/01/202121:20AlbertLOGONWEEKEND
02/01/202122:24AlbertLOGOFFWEEKEND
02/01/202115:53ElizabethLOGONWEEKEND
02/01/202117:19ElizabethLOGOFFWEEKEND
02/01/202112:51JamesLOGONWEEKEND
02/01/202114:10JamesLOGOFFWEEKEND
02/01/202117:43KathyLOGONWEEKEND
02/01/202118:14KathyLOGOFFWEEKEND
02/01/202111:16MarkLOGONWEEKEND
02/01/202112:43MarkLOGOFFWEEKEND
02/01/202110:54RachelLOGONWEEKEND
02/01/202114:43RachelLOGOFFWEEKEND
03/01/202121:23AlbertLOGOFFWEEKEND
03/01/202122:07AlbertLOGONWEEKEND
03/01/202122:43AlbertLOGOFFWEEKEND
03/01/202114:00CarlLOGONWEEKEND
03/01/202116:43CarlLOGOFFWEEKEND
03/01/202116:36NellLOGONWEEKEND
03/01/202120:02NellLOGOFFWEEKEND
03/01/202122:13NicolaLOGONWEEKEND
03/01/202122:48NicolaLOGOFFWEEKEND
03/01/202111:15RachelLOGONWEEKEND
03/01/202112:16RachelLOGOFFWEEKEND
03/01/202112:25RachelLOGONWEEKEND
03/01/202115:52RachelLOGOFFWEEKEND
03/01/202122:52RachelLOGONWEEKEND
03/01/202122:19TimothyLOGONWEEKEND
04/01/202118:57AndrewLOGOFFOUTSIDE OF NORMAL WORKING HOURS
04/01/202121:42AlbertLOGONOUTSIDE OF NORMAL WORKING HOURS
04/01/202122:40AlbertLOGOFFOUTSIDE OF NORMAL WORKING HOURS
04/01/202119:13AgathaLOGOFFOUTSIDE OF NORMAL WORKING HOURS
04/01/202107:46BrianLOGONOUTSIDE OF NORMAL WORKING HOURS
04/01/202120:30DavidLOGOFFOUTSIDE OF NORMAL WORKING HOURS
04/01/202107:27DevonLOGONOUTSIDE OF NORMAL WORKING HOURS
04/01/202119:18JimmyLOGOFFOUTSIDE OF NORMAL WORKING HOURS
04/01/202120:53JimmyLOGONOUTSIDE OF NORMAL WORKING HOURS
04/01/202121:46JimmyLOGOFFOUTSIDE OF NORMAL WORKING HOURS
04/01/202118:13JamesLOGOFFOUTSIDE OF NORMAL WORKING HOURS
04/01/202118:06KathyLOGOFFOUTSIDE OF NORMAL WORKING HOURS
04/01/202120:14TerrieLOGOFFOUTSIDE 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

  • rsbin's avatar
    rsbin
    3 years ago

    jpjeffery ,

    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

  • Here's that sample data again, in what I hope will be an easier format to use/refer to:

     

    DATETIMESTAFF MEMBERACTIVITYREASON
    25/12/202020:05MarkLOGONBANK HOLIDAY
    25/12/202020:36MarkLOGOFFBANK HOLIDAY
    26/12/202008:12MikeLOGONBANK HOLIDAY
    27/12/202007:18MikeLOGONOFFICE CLOSED
    27/12/202008:25RobertLOGONOFFICE CLOSED
    28/12/202012:05AlbertLOGONOFFICE CLOSED
    28/12/202009:18JamesLOGONOFFICE CLOSED
    28/12/202017:43JamesLOGONOFFICE CLOSED
    28/12/202013:10MarkLOGONOFFICE CLOSED
    28/12/202010:32RachelLOGONOFFICE CLOSED
    28/12/202015:32RachelLOGONOFFICE CLOSED
    29/12/202014:18HelenLOGONOFFICE CLOSED
    29/12/202014:52HelenLOGOFFOFFICE CLOSED
    29/12/202011:44JamesLOGONOFFICE CLOSED
    29/12/202013:01JamesLOGOFFOFFICE CLOSED
    29/12/202015:54JamesLOGONOFFICE CLOSED
    29/12/202017:24JamesLOGOFFOFFICE CLOSED
    29/12/202010:56RachelLOGONOFFICE CLOSED
    29/12/202017:40RachelLOGOFFOFFICE CLOSED
    29/12/202023:42TimothyLOGONOFFICE CLOSED
    30/12/202015:37AndreaLOGONOFFICE CLOSED
    30/12/202017:40AndreaLOGOFFOFFICE CLOSED
    30/12/202021:52NicolaLOGONOFFICE CLOSED
    30/12/202022:31NicolaLOGOFFOFFICE CLOSED
    30/12/202010:35RobertLOGONOFFICE CLOSED
    30/12/202011:28RobertLOGOFFOFFICE CLOSED
    30/12/202002:21TimothyLOGOFFOFFICE CLOSED
    30/12/202020:58TimothyLOGONOFFICE CLOSED
    30/12/202022:48TimothyLOGOFFOFFICE CLOSED
    31/12/202011:43JamesLOGONOFFICE CLOSED
    31/12/202013:01JamesLOGOFFOFFICE CLOSED
    31/12/202016:21JamesLOGONOFFICE CLOSED
    31/12/202017:13JamesLOGOFFOFFICE CLOSED
    31/12/202016:50KathyLOGONOFFICE CLOSED
    31/12/202018:47KathyLOGOFFOFFICE CLOSED
    31/12/202008:09MikeLOGONOFFICE CLOSED
    31/12/202010:31MikeLOGOFFOFFICE CLOSED
    31/12/202009:15RachelLOGONOFFICE CLOSED
    31/12/202013:28RachelLOGOFFOFFICE CLOSED
    01/01/202115:46KathyLOGONBANK HOLIDAY
    01/01/202118:34KathyLOGOFFBANK HOLIDAY
    01/01/202113:21LindaLOGONBANK HOLIDAY
    01/01/202114:21LindaLOGOFFBANK HOLIDAY
    01/01/202116:16MortimerLOGONBANK HOLIDAY
    01/01/202117:43MortimerLOGOFFBANK HOLIDAY
    01/01/202117:50MortimerLOGONBANK HOLIDAY
    01/01/202118:31MortimerLOGOFFBANK HOLIDAY
    01/01/202112:14RachelLOGONBANK HOLIDAY
    01/01/202117:18RachelLOGOFFBANK HOLIDAY
    01/01/202115:53TobyLOGONBANK HOLIDAY
    01/01/202117:57TobyLOGOFFBANK HOLIDAY
    01/01/202110:58VictoriaLOGONBANK HOLIDAY
    01/01/202111:55VictoriaLOGOFFBANK HOLIDAY
    02/01/202121:20AlbertLOGONWEEKEND
    02/01/202122:24AlbertLOGOFFWEEKEND
    02/01/202115:53ElizabethLOGONWEEKEND
    02/01/202117:19ElizabethLOGOFFWEEKEND
    02/01/202112:51JamesLOGONWEEKEND
    02/01/202114:10JamesLOGOFFWEEKEND
    02/01/202117:43KathyLOGONWEEKEND
    02/01/202118:14KathyLOGOFFWEEKEND
    02/01/202111:16MarkLOGONWEEKEND
    02/01/202112:43MarkLOGOFFWEEKEND
    02/01/202110:54RachelLOGONWEEKEND
    02/01/202114:43RachelLOGOFFWEEKEND
    03/01/202121:23AlbertLOGOFFWEEKEND
    03/01/202122:07AlbertLOGONWEEKEND
    03/01/202122:43AlbertLOGOFFWEEKEND
    03/01/202114:00CarlLOGONWEEKEND
    03/01/202116:43CarlLOGOFFWEEKEND
    03/01/202116:36NellLOGONWEEKEND
    03/01/202120:02NellLOGOFFWEEKEND
    03/01/202122:13NicolaLOGONWEEKEND
    03/01/202122:48NicolaLOGOFFWEEKEND
    04/01/202120:30DavidLOGOFFOUTSIDE OF NORMAL WORKING HOURS
    04/01/202107:27DevonLOGONOUTSIDE OF NORMAL WORKING HOURS
    04/01/202119:18JimmyLOGOFFOUTSIDE OF NORMAL WORKING HOURS
    04/01/202120:53JimmyLOGONOUTSIDE OF NORMAL WORKING HOURS
    04/01/202121:46JimmyLOGOFFOUTSIDE OF NORMAL WORKING HOURS
    04/01/202118:13JamesLOGOFFOUTSIDE OF NORMAL WORKING HOURS
    04/01/202118:06KathyLOGOFFOUTSIDE OF NORMAL WORKING HOURS
    04/01/202120:14TerrieLOGOFFOUTSIDE OF NORMAL WORKING HOURS

     

     

    • rsbin's avatar
      rsbin
      Community Champion

      jpjeffery ,

      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,

       

      • jpjeffery's avatar
        jpjeffery
        New 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?

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