Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jpjeffery
New Member

CSV formatted data

‎09-26-2022 09:06 AM

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

Labels:
Message 1 of 6
314 Views
0
1 ACCEPTED SOLUTION
rsbin
Super User
Super User
In response to jpjeffery

‎09-26-2022 10:14 AM

@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,

 

View solution in original post

jpjeffery.pbix
Message 3 of 6
264 Views
5 REPLIES 5
jpjeffery
New Member

‎09-28-2022 05:45 AM

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

 

PBI - OoH.jpg

 

So, thanks for your help, rsbin! I really appreciate it.

Message 6 of 6
235 Views
0
jpjeffery
New Member

‎09-26-2022 09:09 AM

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

 

 

Message 2 of 6
305 Views
0
rsbin
Super User
Super User
In response to jpjeffery

‎09-26-2022 10:14 AM

@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.pbix
Message 3 of 6
265 Views
jpjeffery
New Member
In response to rsbin

‎09-27-2022 06:58 AM

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

PBI - Data Source Settings (current file).jpgPBI - Data Source Settings (global permissions).jpg

Is this the right way to replace the data, or is there another?

Message 4 of 6
255 Views
0
rsbin
Super User
Super User
In response to jpjeffery

‎09-27-2022 07:03 AM

@jpjeffery ,

In my sample, I used the Enter Data option.

You simply have to connect to your source using Transform Data -> New Source, then select Text/CSV.  

rsbin_0-1664287374157.png

 

Then select the path to where your CSV file resides.

You can then delete the table I manually created.

Regards,

Message 5 of 6
253 Views

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All