0:00
Hello everyone and welcome back to the third case study in Power Query
0:04
Now this case study would be very interesting for you. Now what exactly is this case study all about
0:08
I want to find out only dates excluding the weekends, that is Saturdays and Sundays
0:14
and also I want to exclude the holidays part here. So find only dates excluding holidays and the weekends
0:20
That's very interesting, very useful. So what I've done is I have created a date column very small data so that I can explain you properly
0:26
In the company, you usually have very big data, huge data. From 10th of September 2019 to 24th of September 2019
0:33
So these are approximately 15 dates which are given. In those 15 dates, there are three holidays which are given 11, 16 and 22nd of September 2019
0:42
Now, my question is, I want to also exclude these three holidays and I also want to remove Saturdays and Sundays
0:48
And get the only working days in this column here. Okay, only working days in this column
0:53
How do I get it? Let us see that practically. So first thing is I have to take this both into, you know
0:59
into my power query, I'll just keep my cousin data, click on data tab, and I can click on
1:06
from table. But before that, what is the name of the table? Name of the table is table two
1:11
Name of the table is table one. So why not give a proper name? I'll write the name of this
1:16
table as holiday table. Okay. And the second table, I'll just write name as date table
1:26
Okay or I write as calendar table So calendar table and holiday table is there I just bring this both into power query and just load as a connection
1:36
So keep your cursor in the data. Data tab from table. Ignore the warning
1:41
That is compatibility warning. And then from this column, what we'll do is for safer side, we'll try to convert into date data type
1:49
So if I click on this button here, I'll select the option as date. Click on replace current step
1:54
So on the left side, if you see, I have a calendar. table converted into query here. Now that's a calendar table query. I'll just click on
2:01
close and load. Close and load to only create connection and click on load. So connection
2:09
will not occupy space. It will be just creating a connection. Repeat the same step for the
2:14
holiday table. From table, pull the data into power query, convert the data type to date
2:20
Okay, I'll just click on this button, select the option date, replace current step. And you see
2:25
on the left-hand side that's a query pane there are two queries load this again as a
2:29
connection click on close and load to and only select as only create connection now after doing
2:35
this what I want is I only want the columns which are in the first table not matching with
2:41
the second only rows in the first not matching with the second so I'll go to this new query
2:46
click on this combined queries and there's an option given as merge merge option always try to pick up a table from the top which is having transactional or more
2:55
So I pick up here as calendar table and from the bottom as holiday table Both the table are having a common column that is total dates and holidays So there are dates on common Now this is the main part After selecting this we are creating
3:08
a relation. From this drop down, I have to select the relevant option. That means only matching
3:13
from the first table, not from the second. If you observe, there are 15 records, 15 dates
3:18
and there are three dates which are matching on the right inside. So 15 minus 3, I should get 12
3:23
as the working dates. So which one should I select all from the, I will select from here as
3:29
yeah. So I'll select from here as left ante that is rows only in the first option. So if you
3:35
observe here, if I select left anti rows only in first, so it says the selection has matched
3:40
three out of 15 rows. So once I click on okay, okay, once I click on okay, so here if you see
3:45
I'm getting as 12 matching rows. Earlier there were 15. Let me show you. Let me go back. In the calendar
3:50
table if you observe there were 15 dates but in the merge query which new query has been created so
3:56
three dates have been subtracted so what are the day if i remember 16th 20 second you see 20 second is not
4:01
there out of this both 16 also is not there 11th is also not there so successfully we have removed
4:07
the holidays from there now i don't need this column that is holiday table i can just click on remove
4:12
now the second thing which is there which is very important i don't want saturdays and sundays in this
4:18
table how do i get it so select the column add column and on the right end side if you see there the option given as date day and there the option given as name of the day Okay it looks very simple very interesting So if I select the option as name of the day I be getting Saturdays Sundays and so on From this drop down I filter if I click on this drop down I will filter only I remove this Saturdays and Sundays from here
4:44
I'll exclude the Saturday Sundays and I'll just click on okay. So 12 were the number of dates
4:49
If I remove the Saturday Sundays, I'm getting total nine working days. Nine working days, that's it, very simple
4:55
Now, I can just right click on the column and I can remove this column. is of no use right now, I'll just click on this remove button
5:02
Now, this is the final output. Out of 15, 3 removed were 12
5:06
Out of 12, there were 3 again Saturday, Sundays. Now I'm getting 9 records
5:11
Now I can click on Home tab, close and load, close and load 2
5:15
And I'll bring the table here itself, so we can actually verify the answer. So only not create connection, I'll select existing, click on this button, select the cell, click on okay, and load
5:26
So this would be the final output. Let's cross-verify. So 11th, 16 and 20 seconds should not be present in this table
5:33
So I'll just move this data. 11th, 16 and 20 seconds should not be present in this table
5:39
And now whichever are the working days or you can say Saturday or Sundays, those are also excluded from this table
5:44
We have seen there at the back end. So I hope you've understood how to find out only dates excluding holidays and also the weekend, Saturdays Sundays
5:52
If you're working in some other country, if in Arab countries, the weekends should be different, Fridays and Saturdays
5:57
some other country, the name weekends should be different. So depending upon your condition, you can filter the data and bring it here
6:03
I hope you have understood. That's all for this video
