Generate Dates between Start and End Date in Power Query

hi you're what sandeep goodly once again and in this video i'm gonna talk about the solution to the power query problem that i gave you last week which is where you had to create a row for every single customer for every single month's investment that he or she makes now if you haven't really taken a look at that problem i suggest that you take a look at the problem first and then come back and take a look at this solution video which is where i will help you step by step to build the solution right from scratch no further ado let's begin all right i'm in power query and that's where i have already loaded the data and i will just take one minute to explain you what the problem was and then we'll start to build out the solution right from scratch now you can see that we have four columnar data we have customer id monthly investment start date and the end date and my objective is to create a row for every single investment for the month for the customer so let's just say that there are two years of investment right from the start date till the end date and i'd like to create a row for every single month in case the start date is not starting off the first of the month the first row of the data should be the start date not really the first of the month that's my objective and you can see that one of the rows is not the first month it's actually the 12th of the month and my first row of that particular month would not be the start of the month but should be the 12th of the month and following that every other month is going to be the first of the month all right that was what the problem was let's just see that from this data if we have to kind of expand the data and create rows from all the dates for every single month between the start date and the end date how do we do that right so the first thing that i'm going to do is you can see that power query has loaded the data from my excel file it has also applied a step which is called changed type you can see that our change type step has been applied and the data type has been changed for the date column as date time now i don't really want date time i would just convert that to a date because the functions that i'll be writing to solve this particular problem are expecting the data types to be date and not date time right so i'm just going to delete that date time from here delete that date time from here and i'm sort of good to go so i'm just going to close this commit to this and this is actually now a date the first part that i'm trying to build is that let's not focus on creating a date for every single month but let's just focus on creating a date for all the dates that are in between the start date and the end date so let's just say that the date is starting on the 1st of jan and the date is ending on the 31st of december i would like to have all the dates i'm not really worried about the first of the month let's just see how can we do that i'm gonna go to the add columns tab and make a custom column right here and let's just start to write my formula over there let's just call these as dates and i will start to write a very very simple list creator function now whenever you're trying to work with the list remember that you have to work with curly brackets and since i'm trying to create a list obviously i'll have to do something with the curly bracket so i'm just going to start writing curly brackets and up in the curly brackets i'll just say 1.5 now 1.5 simply means that i'd like to have my start number as 1 and n number is 5 which has got no meaning with the start date and the end date but all that i'm trying to see is that am i going to be able to create a list or not so let's just see that if i just click on ok every single row i get a list if i just take a look at the preview of the list you will find that i do get one two three four five but this is not supposed to be five rows this is supposed to be all the rows that belong to the start date and the end date the good thing is that i have figured out a way to create a list and i can modify this 1.5 instead of one i can just write the start date and instead of five i can write the end date and in between that i'm just gonna get all the dates so what do i do i go back to my function and instead of writing one here i am going to write start date but start date is actually a date and one is a number so i need to create a number before i can start to work with the date as a number so i'm just going to write a function called number.from that means that i have a date please extract the date as a numeric input so number.from is my start date i select that close the bracket and then i'm just going to say instead of five again i'm gonna write like number dot from and here i'm just going to say this is going to be your end date right i close the bracket you can see that number dot from got its own bracket but the entire list creator function has got two dots in between and the start and the end curly bracket all right good to go say okay and we again get a list if i take a look at the preview of the list i do get numbers these numbers are nothing but dates right from the first date which is the first date right here till the end date which is after two years by the end of december 2022. now let's just try to build it further all right the next thing that i'm gonna do is i don't really need all the dates right you know i don't really need all the dates i just need the start of the month so if there are like two years of time frame between the start and the end i just need 24 days because there are going to be 24 months between the start date and the end date so how do i modify my function to actually accommodate for that i'm again going to open up my custom formula box right here and start to modify this little step that i have created at this particular time what i would need is a bit of variables because i'll be doing a lot of modifications and maneuvers with my data so let's just kind of convert this into a variable to be able to do that i need to write the let let statement right here and let's just say that this thing or the list that i have created is all days so i'm just going to maybe uh go over to the next row for some reason i'm not able to do that let's just write it again let and okay and i'm just going to say all dates and this is nothing but my first variable which is giving me all the dates between the start date and the end date now what i want to do is i want to restrict these to the start of the months only right so i'm going to maybe declare another variable and i'm going to say that these are my start uh of month dates right and i'm going to maybe say that i would like to transform the list that i have already created which is in the previous step and convert that list to just the start of the months so i'm just going to use a function called list.transform right here and let's just delete that little thing that came at the start and also put a comma because the step has ended and the new step has started so i'm going to say list dot transform it says that hey which list are you trying to transform let me just format that real quick so in the next row move the next row next row okay so i'm going to say that hey which list are you trying to transform the list that i'm trying to transform is the list that i already created which is giving me all the dates which is nothing but all dates that's the list that i'm trying to transform now it says hey how would you like to transform that what i'd like to do is i like to pick up every single number and push it to the start of the month that's what i will do so i'm just going to use the each keyword because i need to go and pick up every single individual number and then push it to the start of the month so i'm just going to use the each keyword and i'm going to first convert the number back to a date because i got numbers and to be able to work with dates you need a date so i'm going to use something like date.from and i am going to use the underscore underscore means the current rows value right so go in each means the current row like go in every single row and underscore means every single rows item is the underscore all right so i'm gonna go here and close that bracket and this is as of now uh just converting the number into a date but apart from converting the number into a date i also want to move it to the start of the month so i'm just going to use the function called date dot start of month i hope that is correct and o is definitely upper case i do that i start the bracket and this is i believe good to go and i have to finally close my uh loop of the variables and i have the in statement and i will definitely say that in of start of the month dates i think this should be good enough let's just see what is the output that we get i'm just going to click on ok and if i now take a look at the list that we have created we definitely get the start of the month dates you can see that now the dates are being repeated over and over again because it's just converting all the dates in between the two dates and pushing them to the start of the month i don't really want the dates to be repeated because i just want unique dates and we have a function to be able to modify that i'm just going to go back to my step once again and say that these start of the month dates are just not simply the conversion of the dates to the start of the month but also just give me the unique in that list now i'm just gonna maybe use another function called list dot distinct right and i'm gonna say that hey here is the list that you would want to just find the uniques in let's just format this a bit all right let's just kind of commit to this and say okay and what do we get is again a list but let's just take a look at the preview of that list this actually is giving me the start of the month and you can see that one january is no more repeated once again we have one january 1st fair first march 1st april so on and so forth this seems to be working fine let's just build this further all right one of the problems with the solution that we are building is as of now our dates mandatorily start from the first of the month we don't necessarily need that take a look at this particular customer customer number 125 the first investment was made in 12th of may not really the first of may so if i take a look at the list that we have generated for that the dates actually start from the 1st of may not really the 12th of may this is incorrect because i definitely want the first date to be not the 1st of the may but definitely the 12th of the may so how am i going to solve this particular problem what i will do is i'll do a little fix in the list that i have created in this particular list that i have created the first item i will manually insert the first item to be the exact first date that we have so i'm just going to take this particular date which is the 12th of the day whatever may june whatever that is and i'm going to take this particular value and insert that as the first item of this particular list so in case my data is not really starting the on the first of the month this fix that i'm building as of now will insert the actual date as the first of the month right be good enough so let's just see how can we do that i'm gonna go back to my step right here and before i find the uniques in the entire list let me just create that fix where i insert the start date as the first part of the list so i'm just going to come right here and after the list is list or distinct i will start to insert another function i'm going to say list dot let's just write list dot insert range and you can see that list dot insert range asks you for a list that's fine we have been working with lists and that's going to be totally okay so i'm just going to say that hey this is my first part which is where i get a list and this is actually giving me a list this is still okay now as the second part of this particular function is going to say that hey on what position do you want the insertion to be done and you can see that it's asking you for the index number now in power query the counting actually starts with zero so i'm going to say that my position at which you want to insert an item is going to be the zeroth item which is nothing but the first item cool then it says hey what would you like to insert and you can see that over here if i just write maybe start date this is not going to work well because start date is actually a single value and what it's asking you it says that if you have multiple values or a single value doesn't matter but provide me those values as a list so i just can't feed the start date as a column i have to convert the start date from a column to a list nothing that complicated all that i will do is i will still reference the start date column but i will reference that in of the curly bracket and this will actually convert the start date into a list that's all about it i'm just going to close the bracket and adhere to my formatting and let's just see does it actually insert the 12th of the may right here or not i'm just going to say okay and let's just see what we get on the customer number 125 and you can see that we do definitely get the 12 of the may of that entire period this is actually good all right so far so good but we still have one small problem remaining in this particular thing to be able to fully solve it take a look at that customer number 127 who declared to invest 1 000 bucks every single month but he's just investing that for just a month so he starts his investment on the 5th of january 2022 and he ends it on 31st of january 2022 that means if i go grab the list right here i should just see one value and that value is supposed to be 5th of jan 2022 that's what it's supposed to mean so if i just go ahead and take a look at that list in that list i'm going to see two values one value that i manually inserted which is the start date which is right here this is good enough and the other value is definitely the first of the jan month which is what we are trying to convert all the lists into because this value was inserted manually it stays and this is the value that we converted all the dates into is again staying and because they are unique both will actually stay but i don't really want the first of jan to stay in here i just want the fifth of this to be inserted now i'm gonna write a very simple condition the condition that i'm gonna write is that hey power query why don't you actually pick up this entire list and start scanning every single item of the list every single item or every single date of this particular list should be definitely greater than or equal to the start date when the investment is beginning so take a look at your start date your your start date is nothing but the fifth and if you compare this condition with this particular date 5th of jan is greater or equal to 5th of jan the answer is two the first item stays take a look at the next item the first of jan is greater or equal to the fifth of jan that's not a true that's a false so the second item is going to be removed the question is how do i write that condition to be able to go in every single row of this list and compare that with the start date right here how do i do that so i'm just going to go ahead to my formula right here in the formula what i'm going to do is i'm going to maybe create a little condition here to be able to select the values in the list that are going to stay that meet the condition i'm going to use the function called list dot select right and list dot select the first part of that is hey which list are you trying to work with i'm just trying to work with this particular list and this particular list is generated by this massive formula that i've been writing for the few minutes now and i'm just gonna kind of uh format this a bit and we are kind of good to go and the second part of this list dot select function is what is your selection condition how would you test an item staying or not staying in the list so i'm going to say that each item which is again which is where i will start to write the each keyword because i have to go row by row each item which is right here which is as of now a date first of all has to be converted to a number because we have to do a mathematical comparison so i'm going to say number dot from number dot from of the current item current item means in underscore the first item then goes to the next row then the second item every single item is an underscore so um take a look at the current item and that current item should be greater than or equal to my start date right let's just close the bracket and let's just kind of say okay and let's see what we get now as of now on this particular row which is row number 127 here the customer id 127 i should just get one value if i maybe click here i am getting an error now why do i get an error let's just go back and let's just start to investigate so when you were trying to maybe have a list right here which is when we did not have the error and we had two items here you were trying to go in every single row of the first item and every single row of the second item once power query goes in the first item it kind of forgets the reference to what exactly was the start date as a column so what i have to do is i have to sort of capture the start date before before i start going in every single row and because the start date has already been captured it's going to remember what the start date was when you start going in the first row because in the first row it's in the list and it doesn't really kind of see or remember what the start date is because the start date has been left outside in the outside table right here right so what do i do i kind of capture that and then i feed that inside of the list how do i do that looks may be complicated it's the same concept as context transition inside of tax it's nearly the same let's just take a look at how do we solve this so at the start when i was declaring variables i'm going to declare another variable and let's just call this as start dt and i'm going to reference that with the start date right now that item has been captured and power query will tend to remember that this is a variable that i'm creating when i'm working with this particular list right here all of this code that we are writing will is is going to belong to this particular list that we are trying to create right so i have created a little start date right here and i'm going to use this particular variable instead of comparing that with the hard coded column which has now forgotten so i'm going to say that number dot from and i'm going to say that this is going to be start date so start dt i think that's the one yeah that's the one and i'm going to say okay and let's just see do we get the correct output or not so i am maybe coming here if i click on the side of the list i now only get to see one date which is the correct date which is when the investment actually started all right now that we are done with all the hard work and we have got all the dates just the month at the month level only we can expand this particular column so i'm just going to go ahead on this particular column click on expand extract the values to new rows and we have all the values that we wanted let's just go here start date and end it we don't really need these columns anymore convert these dates into a data type of date and that is nothing but my output all right that was all about the power query challenge i hope you like this one and if you have any questions around this please feel free to put them down in the comments and i'll be glad to reply i also suggest you to check out all the people who have commented on the blog or on the youtube channel to take a look at their solutions you will find them pretty interesting and of course just like i promised a big shout out to everybody who participated thanks so much for your time as well in the end a quick shout about my tax and my power query courses in case you're starting out with power bi and you would like to start right from scratch learn the fundamentals first and then start to proceed on to solving more sophisticated more challenging problems of your own data i'd highly recommend that you take a look at my courses it's going to be highly beneficial thanks so much for spending time with me and i will catch you guys in the next one [Music] you

Channel: Goodly

Views: 55,756

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: tnjzIq7jinQ

Length: 18min 50sec (1130 seconds)

Published: Fri Jul 02 2021