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: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
jb_graphic
Frequent Visitor

Help with setting up my data so I can get the visuals I want.

‎09-18-2023 10:11 AM

Let me start by saying I am new to Power Bi and Power query. I am trying to learn and increase my knowledge.

Let me try and explain this as best I can so I can hopefully get some help.

We send some jobs to a couple remote servers (Sim_1 & Sim_2) to process.

If the server is processing a job it sits in a queue and waits for the next available server and then moves to that server for processing.

I am trying to work out the utilization rate for these servers individually and combined.

Also, at any given time how many jobs are waiting in queue.

I have a couple issues I am having trouble solving.

#1 how to add the dates to the table when there is no activity so I can log as resources are 100% available. Or I have read that I can add a date table, I am not 100% sure how to do that and the advantages it has. A link to a good video on the use would be valuable.

#2 how to calculate and display the jobs in queue data. I want to show zeros in this chart so I can see trends if possible.

Would someone be able to help me walk through the process of building a report?

Now I do realize that the data I have is most likely limited in what I can extract from it.

Any help would be greatly appreciated.

I have included a sample of the data I am using.

 

ServerJobName (including User Name)CreateDateCreateTimeStartDateStartTimeEndDateEndTimeQueueTimeRunTimeStatusUser NameJob Details
DR-SIM-1user1_MY26test_FEA_0609135148.batFriday, June 9, 20231:51:59 PMFriday, June 9, 20231:55:21 PMFriday, June 9, 20232:05:41 PM310Successuser1MY26test_FEA_0609135148
DR-SIM-1user1_MY26test_FEA_0610055531.batSaturday, June 10, 20235:55:38 AMSaturday, June 10, 20235:58:07 AMSaturday, June 10, 20236:45:28 AM247Successuser1MY26test_FEA_0610055531
DR-SIM-1user2_Analysis6_BSH_36_OTA_LC_F4_PROD_0628075304.batWednesday, June 28, 20237:53:15 AMWednesday, June 28, 20239:00:50 AMWednesday, June 28, 202310:01:34 AM6760Successuser2Analysis6_BSH_36_OTA_LC_F4_PROD_0628075304
DR-SIM-1user2_Analysis6_BSH_36_BTA_SC_0630_2_0630035935.batFriday, June 30, 20233:59:51 AMFriday, June 30, 20234:48:14 AMFriday, June 30, 20236:17:27 AM4889Successuser2Analysis6_BSH_36_BTA_SC_0630_2_0630035935
DR-SIM-1user3_Derailment_Sim_0630233440.batFriday, June 30, 202311:36:05 PMFriday, June 30, 202311:37:19 PMFriday, June 30, 202311:38:11 PM10Failuser3Derailment_Sim_0630233440
DR-SIM-1user3_Derailment_Sim_0630234224.batFriday, June 30, 202311:43:35 PMFriday, June 30, 202311:44:44 PMSaturday, July 1, 202312:18:13 AM133Successuser3Derailment_Sim_0630234224
DR-SIM-1user3_ccouterrrr_0705185557.batWednesday, July 5, 20236:57:48 PMFriday, July 7, 20232:40:10 PMFriday, July 7, 20233:04:03 PM262223Successuser3ccouterrrr_0705185557
DR-SIM-1user4_test_LOWER_LOAD_7_5_0705190615.batWednesday, July 5, 20237:06:25 PMFriday, July 7, 20233:04:18 PMFriday, July 7, 20233:06:02 PM26371Successuser4test_LOWER_LOAD_7_5_0705190615
DR-SIM-1user4_test_UPPER_LOAD_7_5_0705190827.batWednesday, July 5, 20237:08:38 PMFriday, July 7, 20233:06:14 PMFriday, July 7, 20233:07:59 PM26371Successuser4test_UPPER_LOAD_7_5_0705190827
DR-SIM-1user4_test_LOWER_LOAD_7_5_0705190952.batWednesday, July 5, 20237:10:07 PMFriday, July 7, 20233:08:11 PMFriday, July 7, 20233:09:56 PM26381Successuser4test_LOWER_LOAD_7_5_0705190952
DR-SIM-1user3_ccinnnerrrrr_0705191621.batWednesday, July 5, 20237:17:57 PMFriday, July 7, 20233:10:09 PMFriday, July 7, 20233:39:47 PM263229Successuser3ccinnnerrrrr_0705191621
DR-SIM-1user3_Copy_of_Derailment_Sim_0705192240.batWednesday, July 5, 20237:23:28 PMFriday, July 7, 20233:40:01 PMFriday, July 7, 20234:22:46 PM265642Failuser3Copy_of_Derailment_Sim_0705192240
DR-SIM-1user3_lowloadderail_0707164851.batFriday, July 7, 20234:49:26 PMFriday, July 7, 20234:50:32 PMFriday, July 7, 20235:26:12 PM135Successuser3lowloadderail_0707164851
DR-SIM-1user3_fatigue_0708000715.batSaturday, July 8, 202312:12:27 AMSaturday, July 8, 202312:14:29 AMSaturday, July 8, 202312:35:51 AM221Successuser3fatigue_0708000715
DR-SIM-1user4_test_UPPER_LOAD_7_11_0711141344.batTuesday, July 11, 20232:13:52 PMTuesday, July 11, 20232:29:00 PMTuesday, July 11, 20232:55:34 PM1526Successuser4test_UPPER_LOAD_7_11_0711141344
DR-SIM-1user3_corner_mill_fatigue2_0712200942.batWednesday, July 12, 20238:13:41 PMWednesday, July 12, 20238:15:09 PMWednesday, July 12, 20238:37:51 PM122Successuser3corner_mill_fatigue2_0712200942
DR-SIM-1user4_test_UPPER_LOAD_7_17_0717170019.batMonday, July 17, 20235:00:35 PMMonday, July 17, 20235:48:02 PMMonday, July 17, 20235:50:18 PM472Successuser4test_UPPER_LOAD_7_17_0717170019
DR-SIM-1user3_derail_third_window_0717194202.batMonday, July 17, 20237:42:36 PMMonday, July 17, 20239:55:33 PMMonday, July 17, 20239:55:53 PM1320Successuser3derail_third_window_0717194202
DR-SIM-1user5_Nonlinear_HBL_0717205109.batMonday, July 17, 20238:51:14 PMMonday, July 17, 202311:14:56 PMMonday, July 17, 202311:16:18 PM1431Successuser5Nonlinear_HBL_0717205109
DR-SIM-1user3_Derailment_Sim_0718030428.batTuesday, July 18, 20233:07:15 AMTuesday, July 18, 20239:44:05 PMTuesday, July 18, 20239:44:25 PM11160Successuser3Derailment_Sim_0718030428
DR-SIM-1user5_Nonlinear_HBL_0719162555.batWednesday, July 19, 20234:26:03 PMThursday, July 20, 20238:55:44 PMThursday, July 20, 20238:57:02 PM17091Successuser5Nonlinear_HBL_0719162555
DR-SIM-1user2_Analysis3_YMH_34_OTA_FT_0705_0720030428.batThursday, July 20, 20233:06:38 AMFriday, July 21, 20233:44:27 PMFriday, July 21, 20235:40:08 PM2197115Successuser2Analysis3_YMH_34_OTA_FT_0705_0720030428
DR-SIM-1user3_test_fatigue_0728163943.batFriday, July 28, 20234:40:39 PMSunday, July 30, 20235:35:18 AMSunday, July 30, 20236:00:03 AM221424Successuser3test_fatigue_0728163943
DR-SIM-1user3_Derailment_Sim_0728164158.batFriday, July 28, 20234:42:30 PMSunday, July 30, 20236:00:17 AMSunday, July 30, 20236:38:07 AM223737Successuser3Derailment_Sim_0728164158
DR-SIM-2user10_edr_slbracket_test_spa_0405150944.batTuesday, April 5, 20223:10:21 PMWednesday, April 6, 20224:13:52 PMWednesday, April 6, 20224:14:22 PM15030Failuser10edr_slbracket_test_spa_0405150944
DR-SIM-2user3_ccouter3_0630234853.batFriday, June 30, 202311:53:40 PMFriday, June 30, 202311:54:46 PMSaturday, July 1, 202312:18:25 AM123Successuser3ccouter3_0630234853
DR-SIM-2user2_Analysis3_YMH_34_OTA_SC_0705_0705102854.batWednesday, July 5, 202310:29:00 AMThursday, July 6, 20234:36:21 PMThursday, July 6, 20236:42:38 PM1807126Successuser2Analysis3_YMH_34_OTA_SC_0705_0705102854
DR-SIM-2user3_fatigueee_0705190249.batWednesday, July 5, 20237:05:38 PMFriday, July 7, 20232:42:32 PMFriday, July 7, 20233:20:58 PM261638Successuser3fatigueee_0705190249
DR-SIM-2user3_Derailment_Sim_0705191902.batWednesday, July 5, 20237:19:51 PMFriday, July 7, 20233:21:12 PMFriday, July 7, 20233:53:42 PM264132Successuser3Derailment_Sim_0705191902
DR-SIM-2user4_test_LOWER_LOAD_7_5_0706121221.batThursday, July 6, 202312:12:32 PMFriday, July 7, 20233:56:22 PMFriday, July 7, 20234:18:07 PM166321Successuser4test_LOWER_LOAD_7_5_0706121221
DR-SIM-2user1_MY26test_CSU_FEA_0707032018.batFriday, July 7, 20233:20:25 AMFriday, July 7, 20234:18:18 PMFriday, July 7, 20235:07:24 PM77749Successuser1MY26test_CSU_FEA_0707032018
DR-SIM-2user4_test_LOWER_LOAD_7_11_0711141541.batTuesday, July 11, 20232:15:59 PMTuesday, July 11, 20232:31:48 PMTuesday, July 11, 20232:53:39 PM1521Successuser4test_LOWER_LOAD_7_11_0711141541
DR-SIM-2user3_derailment_splitloload_0714211533.batFriday, July 14, 20239:17:20 PMFriday, July 14, 20239:18:23 PMFriday, July 14, 20239:24:55 PM16Successuser3derailment_splitloload_0714211533
DR-SIM-2user3_derail_stefko_0715002030.batSaturday, July 15, 202312:22:29 AMSaturday, July 15, 202312:24:38 AMSaturday, July 15, 202312:35:51 AM211Successuser3derail_stefko_0715002030
DR-SIM-2user4_test_LOWER_LOAD_7_17_0717170104.batMonday, July 17, 20235:01:18 PMMonday, July 17, 20235:31:33 PMMonday, July 17, 20235:35:50 PM304Successuser4test_LOWER_LOAD_7_17_0717170104
DR-SIM-2user4_test_LOWER_LOAD_7_17_0717204714.batMonday, July 17, 20238:47:23 PMMonday, July 17, 20239:09:15 PMMonday, July 17, 20239:26:46 PM2117Successuser4test_LOWER_LOAD_7_17_0717204714
DR-SIM-2user5_Nonlinear_HBL_0717231203.batMonday, July 17, 202311:12:11 PMTuesday, July 18, 20233:33:00 AMTuesday, July 18, 20233:33:46 AM2600Successuser5Nonlinear_HBL_0717231203
DR-SIM-2user5_Nonlinear_HBL_0718174433.batTuesday, July 18, 20235:44:41 PMTuesday, July 18, 202310:01:23 PMTuesday, July 18, 202310:01:44 PM2560Successuser5Nonlinear_HBL_0718174433
DR-SIM-2user4_test_LOWER_LOAD_7_19_0719163615.batWednesday, July 19, 20234:36:29 PMWednesday, July 19, 202311:09:58 PMWednesday, July 19, 202311:10:55 PM3930Successuser4test_LOWER_LOAD_7_19_0719163615
DR-SIM-2user4_test_LOWER_LOAD_7_19_0719163712.batWednesday, July 19, 20234:37:19 PMWednesday, July 19, 202311:11:07 PMWednesday, July 19, 202311:12:08 PM3931Successuser4test_LOWER_LOAD_7_19_0719163712
DR-SIM-2user2_Analysis3_YMH_34_BTA_FT_0705_0720031127.batThursday, July 20, 20233:11:39 AMFriday, July 21, 20233:49:00 PMFriday, July 21, 20235:48:17 PM2197119Successuser2Analysis3_YMH_34_BTA_FT_0705_0720031127
DR-SIM-2user4_test_LOWER_LOAD_7_24_0724180254.batMonday, July 24, 20236:03:03 PMMonday, July 24, 20236:37:35 PMMonday, July 24, 20238:14:00 PM3496Successuser4test_LOWER_LOAD_7_24_0724180254
DR-SIM-2user5_Nonlinear_Side_Load_0724180514.batMonday, July 24, 20236:05:18 PMMonday, July 24, 20238:14:12 PMFriday, July 28, 20237:58:43 AM1285024Successuser5Nonlinear_Side_Load_0724180514
DR-SIM-2user4_test_LOWER_LOAD_BAR_7_24_2_0724202957.batMonday, July 24, 20238:30:05 PMFriday, July 28, 20237:59:37 AMFriday, July 28, 20238:35:00 AM500935Successuser4test_LOWER_LOAD_BAR_7_24_2_0724202957
DR-SIM-2user4_test_LOWER_LOAD_BAR_7_24_3_0724204836.batMonday, July 24, 20238:48:44 PMFriday, July 28, 20238:35:12 AMFriday, August 4, 20233:25:46 PM502610490Failuser4test_LOWER_LOAD_BAR_7_24_3_0724204836

 

Labels:
Message 1 of 6
486 Views
0
1 ACCEPTED SOLUTION
foodd
Super User
Super User

‎09-18-2023 01:28 PM

Hello @jb_graphic, and thank you for starting a conversation in the Desktop Forum.

 

Thank you for your useful description.  Starting with Power Query and Power BI is a 

useful and beneficial journey.

 

Beginning, I would suggest the Enterprise DNA Beginners Guide to Power BI Playlist for

a good grounding.

 

Enterprise DNA Youtube Channel:

Ultimate Beginners Guide to Power BI (Playlist) - updated 5 days ago

Ultimate Beginners Guide to DAX (Playlist) - updated 5 days ago

 

For the Date Table, there are many examples.  Reza Rad and SQLBI are awesome.   

As well, Melissa de Korte who is one of the best Power Query M Practitioners

in the world, and an EnterpriseDNA Expert has created the Extended

Date Table Power Query M Function that is a must-have, and

very easy to update based on your needs. 

Start with this URL:  Extended Date Table Power Query M Function (enterprisedna.co) 

where she explains it in depth.  Melissa is very active on LinkedIn, where 

she publishes Power Query and Power BI content daily.

View solution in original post

Message 4 of 6
448 Views
5 REPLIES 5
foodd
Super User
Super User

‎09-18-2023 01:28 PM

Hello @jb_graphic, and thank you for starting a conversation in the Desktop Forum.

 

Thank you for your useful description.  Starting with Power Query and Power BI is a 

useful and beneficial journey.

 

Beginning, I would suggest the Enterprise DNA Beginners Guide to Power BI Playlist for

a good grounding.

 

Enterprise DNA Youtube Channel:

Ultimate Beginners Guide to Power BI (Playlist) - updated 5 days ago

Ultimate Beginners Guide to DAX (Playlist) - updated 5 days ago

 

For the Date Table, there are many examples.  Reza Rad and SQLBI are awesome.   

As well, Melissa de Korte who is one of the best Power Query M Practitioners

in the world, and an EnterpriseDNA Expert has created the Extended

Date Table Power Query M Function that is a must-have, and

very easy to update based on your needs. 

Start with this URL:  Extended Date Table Power Query M Function (enterprisedna.co) 

where she explains it in depth.  Melissa is very active on LinkedIn, where 

she publishes Power Query and Power BI content daily.

Message 4 of 6
449 Views
jb_graphic
Frequent Visitor
In response to foodd

‎09-18-2023 06:11 PM

Thank you for the information, I will look at this and update my data.This may help get me closer to my answer.

Message 5 of 6
433 Views
foodd
Super User
Super User
In response to jb_graphic

‎09-18-2023 06:17 PM

Great!   You can join the EnterpriseDNA Forum which is very active, for Free, and if there is a budget, a paid subscription is worth it.   The Forum is much like the Microsoft Community Forums, though very focused, and the members are very friendly.   Many MVP familiar faces participate as well.

Message 6 of 6
432 Views
KEAlexander
Helper I
Helper I

‎09-18-2023 11:36 AM

There are multiple ways to add a date table. Reza Rad has a nice article on the options. https://radacad.com/power-bi-date-or-calendar-table-best-method-dax-or-power-query

Message 2 of 6
457 Views
jb_graphic
Frequent Visitor
In response to KEAlexander

‎09-18-2023 06:10 PM

Thanks I will look at the link you provided

Message 3 of 6
435 Views

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All
Top Solution Authors
View All