cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sltraffic
Helper III
Helper III

2 Part Question Using Time

Hello,

 

I have a 2 part question manipulating my Time values.

 

First question; I'd like to sum my Event entries I have for each hour and plot them on line graph.

 

I have created a custom column in my Power Queury Editor that separates the time from the date but I'm not seeing an option to sum the entries on a per hour basis when I set the column as the Axis.

sltraffic_3-1609367444618.png

 

I am able to create another column that separates the Hour and plot these values but my x axis is not what I'm looking for.

sltraffic_2-1609366849431.png

 

Second question; what is the most effficent way to create another column that will assign either the Time or Hour column according to these ranges;

12:00:00 AM to 5:59:00 AM = "ON"

6:00:00 AM to 1:59:00 PM = "AM"

2:00:00 PM to 10:00:00 PM = "PM"

10:01:00 PM to 11:59:00 PM = "ON"

 

Here is a sample of the data in my Queury Editor.

 

sltraffic_1-1609366817532.png

 

Thanks in advance for your help!

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User III
Super User III

@sltraffic  Try adding a new column for 'start of hour' to get the x-axis you're looking for in your first question: 

AllisonKennedy_0-1609369332435.png

 

For your second question you can add a new Conditional Column in Power Query:

 

 

AllisonKennedy_3-1609369568312.png

 

 



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User III
Super User III

@sltraffic  Try adding a new column for 'start of hour' to get the x-axis you're looking for in your first question: 

AllisonKennedy_0-1609369332435.png

 

For your second question you can add a new Conditional Column in Power Query:

 

 

AllisonKennedy_3-1609369568312.png

 

 



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

Thank you so much! This worked exactly as I was hoping...and more straight forward than I was visualizing.

aj1973
Community Champion
Community Champion

Hi @sltraffic 

To answer your second question: Add a conditional column in power query

your first question is not clear and espacially the first Graph Y Axis = Count of Time and X Axis = Time !!

Can you share a dummy Pbix?

 

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors