Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Getting days of the week in a date range

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? 

 

Untitled.png

 

I need to visualise a table like this

Leave day table.png

 

Do I need columns or measures?

 

Happy to provide more details. 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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

It turns this:

2020-04-15 20_01_55-Untitled - Power Query Editor.png

into this:

2020-04-15 20_02_22-Untitled - Power Query Editor.png

 

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"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
edhans
Super User
Super User

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

It turns this:

2020-04-15 20_01_55-Untitled - Power Query Editor.png

into this:

2020-04-15 20_02_22-Untitled - Power Query Editor.png

 

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"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
amitchandak
Super User
Super User

@Anonymous 

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.