Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

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

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User
Super User

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

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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