Convert UTC datetime to local time zones in Power Query

so hello everybody is Monday says time for another parkour video and it is also football fever maybe maybe I don't know if you're following the women's FIFA World Cup but it is started on Friday last week and I have created a report the same a CD for the men's FIFA World Cup and we will talk about more in future videos about on that and one of the things I did wrong with the first time I did that and you don't know the power bi report it was that everything that I downloaded from the website it was obviously on my time zone so when you were on different types and you downloaded the report it showed my tension and not yours so into this video and in the FIFA Women's report I fixed that so that's not an issue anymore and I'm going to show you how I did it because you can use that technique on any other scenario that has with time zones to do okay so we go to the women's FIFA World Cup 2019 report we will I will show you how I did these in future videos so if we go to this report as you can see here it says June 10 6 p.m. well there's the here is that the local time so when I open this power bi report you will see ok go to my computer see you what times are you in and they will change these accordingly I haven't been able to test it in any other time zone of my own so if you download this and it works under it doesn't work just let me know ok you should work Theory key so this is how I did it let's go back into power quick so I have hello power girl where are you it just crashes you know if it is the constant responsive which is tab away or minimize maximize just getaway of power bi comeback and then you have work so let's see here we have the match information and these comes from from directly from the FIFA website and Women's World Cup matches so what we're going to do is we're going to duplicate that I will show you step by step so you can follow along and we're going to kill all the steps up to here delete until and delete so what we have one side of deleted everything is just I've went to the website grab all the data and then don't nothing else so you can see here that column two let's see so here you have column two you contains the information about the time and the date for the matches to happen and then I just deleted some columns got rid of the local time and now I have here a column that has date and time but it's not in daytime format it's just date on time as text this is important so the first thing we need to do is to convert this this is my local time I grab it from the website so this is the time zone Sweden which is two hours away from UTC the universal time zone okay so I have to convert these to UTC to the universal time so nothing ask you to go to the local time whatever that is not mine yours basically so how are we going to do this the first thing we need to do is to convert this into time zone so we're going to go I'd column custom column why is he so slow okay so this is [Music] time soon UTC no yep well let's do it in the step so you can follow along so what we're going to do is going to date time from so we're not going to convert it into date time so we're going to convert it into daytime forests from and then we're going to get the column two which is the one that has that information close the brackets and you'll see you see so now it's a daytime format this is UTC +2 hours so we want to convert it to you to see so then we can convert it to whatever local time it is okay so what we're going to do is we need to back these two hours so UTC hours this will be 7th of June 7 p.m. ok so we need to do now if we go here you can do this in different steps I prefer to do it in one step we're going to write timezone you to see now it's going to be due to C and then you're going to do plus if you've taken my M course you know that you can you know add the direction to time zones and the way to do it is you add the class and then you write duration and then you have days and now we want to go back two hours so minus two and nothing with minutes nothing with seconds so we're going to remove two hours from whatever day time is in here which is Sweden time okay so this will say 7:00 p.m. which it says yay so now we have UTC time but now we need to tell power query and I hey you have to go and check which time zone you are so if somebody from California opens this file and wants to follow FIFA it will show there timezone not mine so how do we do that well see what we're going to do our column you can do it in the same but it will get too messy just don't want to show you step by step so this is what we're going to do first of all we're going to use a function called come here come here baby what with my computer is super slow okay so we're going to use a day time function that is called day time add its own the date time here day time add some so what this does is converts they time to day time zone you need to have that okay so we go back to power bi and we put date/time add zone and then we grab our data our date time date and we're going to say okay convert that into daytime some format okay and now you see the difference this is the time format this is the time is sown format good now that we have that we have we need a function that converts this into local time so we're going to go in there probably let me do it in different steps but then know that you can do a massive one or maybe I'll show you at the end how to do the massive one so we go in here and now we need to have a date time is so the function that converts to local so this is return estate and some values from the local data also this is exactly what we need so we go back to power query this do an add column and then we're going to write date time its own [Music] somewhere here should be to local there we have it and now we're going to grab the first custom that we created and that is going to convert the UTC value into my local date/time which you see goes back to nine o'clock which it should be because it is you know go to my computer say hey what are you and I says I'm in Sweden ah ok is plus two hours boom adds two hours and now we want to go back to our date-time format because you don't want to work with date timezone format so to do that you just go custom column and you grab the reset function that is removed sown in here return us at daytime value so this is all we need so it goes in here and his day time zone remove zone so it will give us a date/time value to that so you go get that one oops ok and that will get us back to where we started but the beautiful this is that it should now take is your daytime instead of mine because close this is crazy download and you let me know if it works I did she'd work so now how do you do all of these in one go well you can just grab go to copy it so we were here we were here grab that one and you put all of them inside but here you put your time zone and it will do all the three steps in one you can also obviously put it in here also so you don't have the to see time to take care of it to remove or whatever so here you see the UTC Taemin see here you see the local time and this one you don't need any more and that way you mean so that's how you convert day times into local day times hopefully yes it's really big it works obviously I cannot take any bullet maybe I could change stick the clock on my computer and should test it on the stuff but how about you taste it and you let me know if it works awesome okay now now that we've done that I am going to maybe it is so that this week is about a week I believe it will be so I don't think I will be able to do any videos about how I created the women's FIFA this report right I but I do want to you know when I did a man's report I actually show you all the steps and these steps are on my website here I will post a link down below in case you want to follow so you follow the same mistakes because it's the same website is the same structure is the same same almost not really the knockout phases are different but most of the things are the same the web connector in power bi has got better so it's easier to do so what I thought is this no idea for me to go through all these because it's going to be basically the same but one of the things I didn't do in this series he was actually to show you how to create a final report how I did how I did these you know the brand in the navigation the tooltip all that stuff you know the dynamic titles so I'm going to do a few series of videos to show you that basically the FIFA Women's FIFA World Cup it is available for download on cobalt chrome download center and in Kerrville membership so if you want to follow the women's FIFA just grab this it will update automatically there is a bat though and it is the knockout faces it was crazy complicated as to how they will qualify so together they qualify him for the first one and the second was no problem at all this there one's for me with Chinese honestly so it might be that when you start refreshing these at third place might not really a hundred percent fit and I didn't take into account the fact that two teams could have a draw so the knockout phase might not work very very well I really want to give you a challenge to fix that and let me know how you did it and I'll you know change the video accordingly but otherwise you know the matches work the players work and all that stuff is working awesome here's wanted to tell you there might be the knockout phase might not be a hundred percent correct this is I look at the instructions and I said there's no way I'm going to figure this out I just there's no time for it so if you figure it out hey give me a shout out and I'll show it okay so yeah a value free download go and download it if you want to refresh automatically you have to get a gateway unfortunately so you get the personal gateway it means I have to have your computer on so this can refresh by itself or you can have a refresh when you know you're working so it will refresh but you need to install the Gateway so that there is a pain anyhow so let's see what happens if it is power weakness this week we will cover that but if it's not I will build this report together with you on the comment videos so I'm talking too much I'm going to shut up now I have a great Monday and I'll see you again on Wednesday si always

Channel: Curbal

Views: 60,934

Keywords: Power bi, powerbi, Curbal, Curbal.com, excel, excel bi, power bi desktop, power bi designer, cubal, power bi video tutorial, power query, Convert UTC datetime to local time zones in Power Query, utc to local time power bi, utc to local time power query

Id: M1zquwmpnZE

Length: 15min 29sec (929 seconds)

Published: Mon Jun 10 2019