Filtering Rows for all dates within the next two days, excluding weekends.

[pmbaranski]

Hi all,

I am working in Excel. I have been having trouble with the advanced editor where I used to not have issues. For example, I tried modifying the editor to find all dates where the date is in the next two days. I went from the below,

    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each [StartDate] <= #date(2021, 5, 8))

 to the following;

   #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each [StartDate] <= Date.From(DateTime.LocalNow())

Which returns the below error that I have never experienced with this snippet of code;

I had planned to add a bit more to get to the point where this column would include all history and the next two days of work (excluding weekends, so on friday 5/7 the column would show up to 5/11). I have noticed there are more than a few questions about days between dates, excluding weekends, but I could not apply any of those solutions to my situation. Any help is greatly appreciated. Thank you.

[edhans · Accepted solution]

You just need a third closing paren at the end of your Filtered Rows like to fix. As to your question on filtering @pmbaranski try this. It turns this:

into this filtered list:

Today is Thursday the 6th. SO to get the next two days, you need the 7th (friday) and 10th (Monday) as the 8th and 9th are weekends. 

Here is the full code that I worked with:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyMDJQitWJVjJG5pjqm4I4hlCOGTLHApljZIjMMzRA4aHImSNzLKGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Filtered Rows" = 
        Table.SelectRows(
            #"Sorted Rows", 
            let
                varToday = DateTime.Date(DateTime.LocalNow()),
                varFilterDate = if Date.DayOfWeek(varToday) <= 3 then Date.AddDays(varToday, 2) else if Date.DayOfWeek(varToday) = 6 then Date.AddDays(varToday, 3) else Date.AddDays(varToday, 4)
            in
            each [Date] >= varToday and [Date] <= varFilterDate
            )
in
    #"Filtered Rows"

If you just needed the 2 days and excluding the weekends, then this would work (if I got all of the math right 😁)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyMDJQitWJVjJG5pjqm4I4hlCOGTLHApljZIjMMzRA4aHImSNzLKGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Filtered Rows" = 
        Table.SelectRows(
            #"Sorted Rows", 
            let
                varToday = DateTime.Date(DateTime.LocalNow()),
                varFirstDay = if Date.DayOfWeek(varToday) <= 4 then Date.AddDays(varToday, 1) else if Date.DayOfWeek(varToday) = 5 then Date.AddDays(varToday, 2) else Date.AddDays(varToday, 1),
                varLastDay = if Date.DayOfWeek(varToday) <= 3 then Date.AddDays(varToday, 2) else if Date.DayOfWeek(varToday) = 6 then Date.AddDays(varToday, 3) else Date.AddDays(varToday, 4)
            in
            each [Date] = varFirstDay or [Date] = varLastDay
            )
in
    #"Filtered Rows"

It returns this:

How to use M code provided in a blank query:
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
5) See this article if you need help using this M code in your model.

[edhans · Accepted solution]

You just need a third closing paren at the end of your Filtered Rows like to fix. As to your question on filtering @pmbaranski try this. It turns this:

into this filtered list:

Today is Thursday the 6th. SO to get the next two days, you need the 7th (friday) and 10th (Monday) as the 8th and 9th are weekends. 

Here is the full code that I worked with:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyMDJQitWJVjJG5pjqm4I4hlCOGTLHApljZIjMMzRA4aHImSNzLKGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Filtered Rows" = 
        Table.SelectRows(
            #"Sorted Rows", 
            let
                varToday = DateTime.Date(DateTime.LocalNow()),
                varFilterDate = if Date.DayOfWeek(varToday) <= 3 then Date.AddDays(varToday, 2) else if Date.DayOfWeek(varToday) = 6 then Date.AddDays(varToday, 3) else Date.AddDays(varToday, 4)
            in
            each [Date] >= varToday and [Date] <= varFilterDate
            )
in
    #"Filtered Rows"

If you just needed the 2 days and excluding the weekends, then this would work (if I got all of the math right 😁)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyMDJQitWJVjJG5pjqm4I4hlCOGTLHApljZIjMMzRA4aHImSNzLKGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Filtered Rows" = 
        Table.SelectRows(
            #"Sorted Rows", 
            let
                varToday = DateTime.Date(DateTime.LocalNow()),
                varFirstDay = if Date.DayOfWeek(varToday) <= 4 then Date.AddDays(varToday, 1) else if Date.DayOfWeek(varToday) = 5 then Date.AddDays(varToday, 2) else Date.AddDays(varToday, 1),
                varLastDay = if Date.DayOfWeek(varToday) <= 3 then Date.AddDays(varToday, 2) else if Date.DayOfWeek(varToday) = 6 then Date.AddDays(varToday, 3) else Date.AddDays(varToday, 4)
            in
            each [Date] = varFirstDay or [Date] = varLastDay
            )
in
    #"Filtered Rows"

It returns this:

How to use M code provided in a blank query:
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
5) See this article if you need help using this M code in your model.

[pmbaranski]

Ed, thank you very much. However, I cannot completely delete the entirety of the the advanced editor as my source is an SQL query. Further, that code elimenates history, where i wanted to see all of the historical dates and any dates within the next two days. Lastly, perhaps it is because I am in excel, but I have never seen that column summary thing you have, how do you activate that?

This code seems like it would be easier to implement in SQL form, see below for a similar query i have created in the past that I may try modifying for this purpose. This code, however, was for determining if work was within this week or the next.

(Case
	When (YEAR(B.DUEDATE)=YEAR(NOW()) and WEEK(B.DUEDATE)=WEEK(NOW()))
	Then '2'
        When duedate<current_date()
	Then '1'
	When(YEAR(B.DUEDATE)<YEAR(NOW()))
	Then '1'
	When duedate>current_date()
	Then '3'
	Else '4' end) as W

[edhans]

My code would be entered into a new blank query for you to examine, then you can change the code as needed and add it to your existing code. There was a link above that showed how to copy code from one query to another and make it work.

as for keeping history, just change the selectrows - probaby get rid of the first filter and only restrict it to the last one.

But if you want more help, you need to provide some expected output.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[pmbaranski · Accepted solution]

In review, i believe all that was needed was the adding a less than or equal to in the lasts line of the editor, as seen below;

 in
            each [Date] <= varFirstDay or [Date] = varLastDay
            )

[edhans]

Great @pmbaranski - glad you got the filter tweaked to suit your needs.

As to the headers of my columns, go to your View tab in Power Query:

Column Profile is interesting too, but it takes up half of the screen, so it is something I turn on then off only when I need it.

[pmbaranski]

Ed, this code is fantastic. However it seems to exclude dates between the defined ranges. Is there a way to modify it to inlcude those, such as something similar to the below?

 in
            each [Date] = varFirstDay or [Date] = varLastDay or [Date] between (varFirstDay,varLastDay)
            )

[pmbaranski]

Solved!! Thank you for the base to work off of!! I will now have to do some testing to ensure this won't exclude weekend dates, as a new requirement has come down that we need to act like weekends are not there but if someone has used a weekend date it must be included. Once again, thanks.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyMDJQitWJVjJG5pjqm4I4hlCOGTLHApljZIjMMzRA4aHImSNzLKGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Filtered Rows" = 
        Table.SelectRows(
            #"Sorted Rows", 
            let
                varToday = DateTime.Date(DateTime.LocalNow()),
                varLastDay = if Date.DayOfWeek(varToday) <= 4 then Date.AddDays(varToday, 1) else if Date.DayOfWeek(varToday) = 5 then Date.AddDays(varToday, 3) else Date.AddDays(varToday, 1),
                varFirstDay = if Date.DayOfWeek(varToday) <= 3 then Date.AddDays(varToday, 2) else if Date.DayOfWeek(varToday) = 6 then Date.AddDays(varToday, 3) else Date.AddDays(varToday, 4)
            in
            each [Date] <= varFirstDay or [Date] = varLastDay 
            )
in
    #"Filtered Rows"

[edhans]

Excellent @pmbaranski - Glad I was able to help.