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

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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