Events in Progress - displaying items present between two dates in Power BI / Excel with Power Query

this is also known as the events in progress problem and i solved this using dax in an earlier video i'll put a link in the notes below this is how you do it with power query let's go [Music] this is my data let me zoom in control shift plus and we can see we've got check-in dates check-out dates a customer id a room category and then the bill and how many people stayed in that room the issue is i want to be able to do a chart showing me how many people were present each day so i can easily do a chart showing me how many people arrived each day or how many people left each day but showing how many were there each day is a problem and i've sold this before using dax and this is the power query method and you may find that one performs better than the other right so i'm starting off i'm just gonna i don't care about the time so i'm just gonna turn these two columns into date columns which brings its own little quirk check this out if i click on this column hold shift i keep my finger on shift and i change this to a date and i say replace current okay it freaks out okay it doesn't like it and if i change it back to a date time replace current okay it's fine so what you have to do again i'm holding shift this is just to do it in one hit you don't have to hold shift you can do one column at a time i'm going to go date and you've got to go add new step okay my data source here is csvs and this issue seems to arrive with csvs so here's two dates anyway on to the main show what i am gonna do is split this row where the person was there for four nights i'm gonna split it into four rows and then this one where the person was there for five nights i'm gonna split it into five rows and that gives me a beautiful date column with the days they were there and the five different dates and i can hook that up to my calendar table and i can slice and dice and do a lovely chart showing number of people present each day doing that though splitting this into five rows is gonna give me a problem that it's gonna replicate the bill five times and if i tried adding up that column obviously they're not paying 518 a night that's for the whole stay so i'm actually going to split this out i'm going to reference this bookings data and there'll be different ways of doing this so right click reference and i'm going to call this my occupancy and all i really care about is the check in the checkout date if i was going to be linking this later on i might keep the customer id and then the number of occupants all right and maybe the room cat group i just don't want the bill right click remove other columns so this one's going to be used when i'm doing sums of amounts by day you know mike received this is going to be for my occupancy calc so the issue is i want to break that check-in date checkout date into multiple rows so i'm going to add column custom column this is going to be called dates present and there's a function called list dot dates okay list.dates this asks for a start date okay the number and then what duration do you want to step in so like one day two days it's gonna be one day for us okay so let's go shift enter just to start a new line and the check-in date will be our start that's the day they were there comma the count of days okay that's the difference between the two dates i'll come back to that okay so i'm just gonna put maybe two days comma and then the duration okay this is the sort of annoying thing is you you put like one in there for a day and then close the bracket and click ok you get this error and it says we cannot convert the value 1 to type duration so going back in here and if i go to learn about power query formulas you can find the syntax of you know if i go here uh comprehensive function reference go to that little link and then search for list dot dates here we are and it gives you a little example of using this hashtag duration so hash duration okay so i'll even just copy that go back in here and rather than this it's going to be hash duration i've got an extra bracket one day no hours no minutes no seconds so it's going to increment it in days okay and do a list two days at a time so if i click okay i've now got this little list and if i click in here and move this across a bit okay you can see that from the first to the second and then if i go to the next one and click on list okay this is running or let me pick a different one let me pick row five say so row five starts at the fourth when's the fifth that's two days so this is the list i need so let me go back into that formula the bit i need to fix up is this number two i need to get the number of days between the check-in date and the checkout date um okay so the first thing i would do is just go okay checkout date minus check-in date okay let's give that a go you click ok and you get an error and it gives you a misleading error it says cannot convert the value duration to number and it's it's not helpful so you know that isn't working for me that check-in date minus checkout date maybe i need to wrap it okay let's try something else i know there's a button that can work out the days so if i click checkout date and then check-in date and i go to the date button on the add column tab i can subtract days and when i do that it does this formula duration dot days okay check-in date minus check out date okay i'm going to grab that okay i'll get rid of that little step it's just a little helper come back in here and paste that bit of code okay duration.days checkout date minus check in date put a little comma there and then click ok and now we've got our list so this list again if i move over here it's running from the first of january to the fourth which is perfect that's the days are there let me pick another one i'll go down to this one here so this is going from the sixth the ninth let's have a quick look yep checked in on the sixth the tenth perfect okay so what do you do now well now you can come to these little expand buttons and you can expand that out to new rows and now for that customer one they were there for four nights four rows this customer was there for five nights five rows so the table's got a lot longer and this can you know slow down your refresh but it could make your dax formulas and your visual interactions a lot quicker than using a big complicated dax function with dax expression okay and the fact that it does break that out into all those different items means that you know you didn't want to include amount because the amount could have been repeated over and over again so you do have to separate these tables so i'm going to change this to a date and then i'm going to get rid of the check-in date and the checkout date okay remove columns and this is all we need okay so let's go home close and apply and then once it's refreshed i'm just going to hook up these tables so i've got a calendar here let me bring in let me click on the old autofit here's my occupancy data here's my bookings data okay let's move these out the way and then we just hook up dates present to date in the calendar and we could do maybe check-in date as the dominant one and check out date as the inactive relationship you know and write some measures on that but it's this one i'm interested in and i really should write a measure for this but let's just be a little bit cheeky well actually let's write the measure so new measure okay let's say uh number of rooms occupied okay it's simply the count rows of the occupancy data okay if i want to do the number of people present i'd sum that occupants column okay so there we go that's just the figure there and let's just add that and put it in there by let's put a month for now i've only got one a couple of months worth of data put that in the axis and there we go let's put date in there and then drill down okay and now you can see it so here in january let's turn some data labels on as well you can see there were two people there on the first then three on the second then four then five and then more people checked out so this is the number of people that were present each day using power query to generate that table okay let me know what you think is that useful do you ever use this have you come across issues with it let me know love getting your feedback thanks for following the channel let other people know about it i'll catch you later

Channel: Access Analytic

Views: 15,801

Keywords: Excel, Power BI

Id: ISDhR-TzwJk

Length: 11min 28sec (688 seconds)

Published: Sun May 01 2022