Extract Dates From Text Strings in Power Query

this video focuses on extracting dates from text values and this can have different challenges in power query so let's see what kind of details i can provide you with to make your life easier [Music] so it may be that you're working with a data set that actually has your date values in a very funny format and power query sometimes recognizes that like when you change the data type but more often than not actually there might be some difficulties so let's delve into how we can work with those dates when they're hidden in text values and how you can extract them so you can better work with them in your data set so the first dates i want to show you are in set one and they look as follows so for example the first line shows the year and then the month and then the days and the other versions we have sometimes have a dash there sometimes they have a dot sometimes the order is just slightly different and let's see what power query does with this by default so if we want these to be like dates you could for example say that you want to change the data type here and it recognizes them all but for our example let's add a custom column we're going to call this one date and this video will focus on the the function called date from text and the date from text function uh requires the single uh the first argument for sure it's mandatory and then it optionally has a second argument which we will look at in a little bit so provide it with the text value first and what it will do is it will try to convert the text value to a date and in this case when we look at it all of the transformations here were successful so as you can see a lot of dates are actually recognized but even though it looks like there's a lot of variations let's have a look at set number two so set number two looks similar at first sight so also with days months and years it's just in a different order or with a different separator now let's see what happens when we do the same thing here so we can again do date from text and we will reference the the first text column and then again we will write this should be a date now in this case all of the transformations that valkyrie tried by default have failed now your result may look different because this is always done by default with the culture in your system so if it's configured for english it will try to do it based on english culture and if you're in a different country it might just do it in a different way so results might be slightly different but for the example i just want to show you that sometimes the transformations don't work however we can start making the transformation more successful by using the optional second parameter so let's move this to a new line what can we do to make this work so the function allows you to have a second argument which is the options parameter and it allows you to give a format of your date even though it's in a text now so to do that you can open a record with a square bracket and then you write format equals and you open some quotations and now you can input the form that the the format that your text value is in so it's the format of the the date that you need so if we look at the first line it shows us that it starts with the day so we can write two times the d that represents the day then we have two uh two digits for the month now the months are represented by capital m because the the lowercase m is actually for minutes and lastly we need the years which is the lower letter y now if i press enter you're going to find that the first one actually works now the formatting only worked for the first one because each formatting on these cells is different and you can just make some slight adjustments to make it work for others so for the next line for example it starts with a month and we can move the day here now we go to the third one it actually starts with the year and from the year it goes to the day and then it goes to the month so you see the third one works now you can do the same for month month day day year year year and then we remove the rest and now we move to let's see ah i made a mistake of the lowercase amps here so change that to the capital lamb and then actually the fourth one also works and you can change that around it even works with custom characters like on line seven on line seven we have a separator of a dot and a star and we have another dot and star and it's about days and then come the months and we only have two digits for the year in this case and if you change that you see the power query actually recognizes that as well so it's very flexible in that sense now of course in this data set we have so many different ways but i'm gonna just show you a formula that i cooked up that will transform each one of them just trying to apply the different formats i will just copy paste it on the screen so the formula that's on the screen now tries to apply the the transformation of the the text to a date with the different formatting and if it fills it tries another one and it does that with the try otherwise construct so it first tries the first function in the top and if that returns an error it tries the next line and the next line until it reaches the bottom and if none of those transformations work and still return an error it will actually give you a null value and to give you a quick view of that the advanced editor shows you that this is the the formula that i wrote now this is not per se best practice because it's pretty tricky to find your dates like this like if you apply logic like this it might be that you sometimes change around the day by the month after all the fifth of january might also be the first of may with this logic but this is just to show that you can apply the different formats now that's data set two we still have some other challenges that you might face let's have a look at data set three so the third data set instead of having just a day number and a month number together with the year this one actually has the description of the of the month now the description of your month might be different depending on the country you are in so the first line it says the 30th of march 2022 and that is because mrt is the short way to show your month in the dutch language and going to the next line mart is the long way to show your month in the dutch language for march and the third one shows one that is for for french now if power query tries to transform this i will show you we can create a custom column and again we will write date from text then of course we take the date the text value that we have and now let's start out by the format that we need so our format equals so we have the day day now we have the capital m three times for a short month and we have the year four digits now if power query recognized all of this then we should be fine right now but as you can see there is an error and that already says that power query doesn't know how to transform this to a date it has tried that automatically with the culture of my system so if you want to force another culture for the comparison you can actually adjust the function a bit more so let's move this line to a clean one and what you do is within the record that we created in the second argument you can write a comma and then you can write culture equals and then you can write the the short version of your the culture of your of the country you want to compare so for the netherlands that's quotations and you write an lnl and now the top one actually works and if you want to try that one for the next line it actually shows the month in the long version you can write another capital m and that also works and for the last line i put them on june there for the french culture so you can change this to french french and you'll find that the last line also works so what have you learned here first of all that the date from text function transforms your text values on it just tries it best based on the culture of your system and if that works you're done second of all if that doesn't work you can indicate a formatting string using the the y m and d now of course most of your situations will then be solved but last of all if you don't want to depend on the the culture of the system you're performing things on you can even provide the date from text function with the culture and this will make your query more robust and make sure that it works on any different system that you work with and with that i actually think you can tackle most of the problems you have extracting dates from your text values i hope that worked let me know if there's any other issues you have with extracting date values from text perhaps i missed something perhaps you know a better way let me know down in the comments and if you have any suggestions for other challenges that you want to have solved also just drop it in the comments and i'll see if i can respond see you on the next one [Music]

Channel: BI Gorilla

Views: 12,057

Keywords: Power query, Power query tutorial, date.fromtext, extract date from text, transform text to date, power query date from text, power query text to date, power query text to date error, power query text to date yyyymmdd, format text value in date

Id: JJFiPtmqPAg

Length: 9min 33sec (573 seconds)

Published: Wed Jul 06 2022