How to create a Time Table to analyze your Power BI or Excel data

just like you need a calendar table to do analysis by year month and quarter you really need a time table to do analysis by minute hour half hour buckets etc now power query can help you build that timetable i'll show you how let's go [Music] so just to start with this is what my data looks like i've got units by date time even down to the second and ultimately what i want to end up with is the ability to do something like this and pick a particular day and see the units broken down in 10 minute buckets or maybe i can drill up to see the data in one hour buckets for that day so the ability to group time and display it in incremental buckets makes sort of analyzing the data a lot easier so we'll build this together i'll show you first of all how to set up your fact table your data in the first place and then how to build your own timetable using power query and ultimately you then hook it up just like you would to the calendar you have a separate lookup table a dimension table for time and away you go okay so let's start with our data we have date and time now you really need to split this apart into a date column and a time column and probably one of the easiest ways to do that is highlight this date and time go add column time time only you've now got a nice time column and i can change this one simply to a date and rename it as date okay nice and simple so the next issue is that my time is at the second level down to the second now i don't want that i don't need it for my analysis to the minute is fine and also it means that when i hook it up to my time table that we're going to create we also don't need that timetable to be at the second level it can be at the minute which is much smaller and simpler okay so there's a couple of ways of rounding this that i know of the way i would probably go about this is to add a column and first of all i need to change this to a decimal number that then gives me this standard option and i can do the modulo and as it says here enter a number from which to find the remainder value so if i i want to do 1 over 1440 okay but i you can't put in 1 over 14 you can't do a divide in here this doesn't let you okay won't let you do a one over so if i'm going to do 1440 click ok and then i simply edit my formula up here to do one divided by 1440 it essentially leaves the remainder so if i then take this column and deduct the remainder it's going to chop basically round everything up to the nearest minute so i go standard i actually can go transform and i can oh you can't go transform in there for two columns okay add column standard subtract this now if i click on my time is it rounded to the nearest minute and if i change this back to time as well okay you'll see that that's the one with the seconds this is the one with the minutes it just chops it down to the nearest minute okay like a round down function now there's probably a simpler way of doing that i don't need this modulo anymore okay i don't even need this time anymore before i get rid of it here's the other way i'd do this um i'm not sure it makes any difference performance wise but you can also do it this way that modular one is a little bit sort of mathematical and potentially confusing so simply right click add column from examples okay this is what i want 5 17 p.m so 5 17 p.m put the capitals in enter and it just turns text from time okay i click ok and then i turn this into a time and it's sorted it for you without using the modulo um let's just double check let's take those two and add another column that subtracts the times from each other and are there any differences no so both methods give me the same answer as far as i'm aware i haven't tested this out completely but seems to work okay let me just go back and take out these take out these and i needed that one okay so i don't need that i don't need that so i can remove those columns and this is now my time to the minute beautiful okay so my data is now sorted right i now need to create a time table which i can use to round up time to minutes to hours to half hours etc i just hook it up then to this time to the minute column for doing my analysis okay so let's go home and uh new source blank query right so i want to create a list or a table containing every minute of the day from midnight to 11 59 pm so what you can do is equals open the curly brackets from 0 to 1440 which is 60 times 24. however when you do this you get an error you need to do one less than that okay so 14 39 so that's the way sort of times are handled so here we have it we have one less row but because it starts at zero there are then all the minutes of the day in here click on the two table button click okay okay and this is my just my minute number so i'm going to go whole number minute number so that's my big list of minutes okay i now need to turn these minute numbers into actual minutes or times now one represents whole day so i need to do one divided by um 1440 okay so i'm just going to go standard divide under the add column tab standard divide okay 1440 okay that's now should be if i turn this into a time that's 12 1201 1202 and so on all the way down to 11 59. so this is my time for the minute okay this is my column that i can hook up to my time based um table my time data okay then what do we want to analyze the data by doing a bucket into five minute intervals ten minute intervals whatever that might be if i do an integer divide so add column standard divide with an integer okay and put a five in there for five minutes i then get these items here okay all roll up into category zero then these next five minutes are under one these next five minutes the second lot of five minutes and so on and so on and if you needed to you could always add a one if you wanted to nudge it up um for each one okay so and rather than rename the column i'm just going to call this five five min buckets okay let's do one for ten minute buckets so click click back on here on minute number standard divide integer this time for 10 okay and this is 10 minute buckets 10 min bucket and last one a 60 minute bucket so back on here the add column tab divide integer okay let's do 60 for the hour and rename this one hour bucket okay well we could just use those if we wanted to but it'd be nice to show the times so what do we do there so i'm going to take my five minute bucket column and i'm going to go to my custom column button and call this my five minute time slot okay i'm going to take my five minute bucket multiply it by five and divide it by the 1440 and now this becomes if i turn it into time this becomes 12 am 1205 12 10 12 15. then i'm just going to repeat that i'll do it for the 10 minute and the one hour slot so there we have our various time slots so this is now my time table awesome okay i've already got a calendar table that i pre-built a little link to my video about the calendar setup will appear now also there'll be links and also a link to download this file will be in the comments okay so go check them out okay home close and apply so now i just go into my model my calendar is already hooked up okay from date to date time to the minute can hook up to time to the minute there we go so that's hooked up beautifully as well and now i've already written a measure for number of units okay so that's already in there and i'm simply going to go into here and say right this is number of units and then go to my calendar and say by date and then in this chart i'm going to say number of units and i could choose from my time table my uh one hour time slot to start with okay and then my 10 minute time slot underneath it and then if i click on an item there i can always then say oh actually i want to see this by the next level down and if i want to see this is all grouped together so you want to sometimes go to the drop down here and say show items with no data so then if i click on this particular time we see in the gaps as well so there we go a time table just like a calendar table built in power query hope you find that useful let me know what you think love getting your comments let people know about this channel and i will catch you later [Music] you

Channel: Access Analytic

Views: 32,569

Keywords:

Id: -q7v56p192M

Length: 12min 32sec (752 seconds)

Published: Sun Oct 17 2021