Getting days of the week in a date range

[Anonymous]

Hi folks, 

I'm new to PowerBI. I have a table "Leave Data" of leave taken by employees in the last financial year. It has two columns - LeaveStartDate and LeaveEndDate. Sometimes when employees take 1 day leave, the two dates are the same. When they take more days, the dates are different.  I need to build a visual that can show pattern of total leaves across the days in the week - the axis should be Mon,Tue, Wed, Thu, Fri.  My chart should answer "Are more leaves are being taken on Monday? Or on a Friday?"  How can I get days of the week from the two columns? 

I need to visualise a table like this

Do I need columns or measures?

Happy to provide more details. 

[edhans · Accepted solution]

I did this in Power Query. See the M code at the bottom.

It turns this:

into this:

Enter the code below into a blank query to see how it works. All of it is pretty easy. THe key is the Date Range step. I am taking the start and ending date and creating a date range from start to end:

{Number.From([Leave Start Date])..Number.From([Leave End Date])}

Then I get the day of week, and remove weekends, then do a grouping. 

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3MtI3MjC0VNJBZeuZAsngzORspVidaCVDQ31DU5gcCgdToQWSOgtUZQGpRcX5eYk5YKXm+iYwWUt9IzQDQ1JzCxR8UhPLUsFqjfUNDYAKjAyAUkC2GYwNURyWmJxYkpmfpxQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Leave Start Date" = _t, #"Leave End Date" = _t, Hours = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Leave Start Date", type date}, {"Leave End Date", type date}, {"Hours", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Date Range" = Table.AddColumn(#"Added Index", "Date Range", each {Number.From([Leave Start Date])..Number.From([Leave End Date])}),
    #"Expanded Date Range" = Table.ExpandListColumn(#"Added Date Range", "Date Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}),
    #"Added Weekday" = Table.AddColumn(#"Changed Type1", "IsWeekday", each Date.DayOfWeek([Date Range], Day.Monday), Int64.Type),
    #"Filtered out weekends" = Table.SelectRows(#"Added Weekday", each [IsWeekday] <= 4),
    #"Inserted Day Name" = Table.AddColumn(#"Filtered out weekends", "Day Name", each Date.DayOfWeekName([Date Range]), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Day Name", {"Type", "Day Name"}, {{"Total Hours", each List.Sum([Hours]), type number}})
in
    #"Grouped Rows"

[amitchandak]

@Anonymous 

This file should help. You need to have weekday in calendar

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0