cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmbaranski
Frequent Visitor

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

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;Power Query Editor.png

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.

2 ACCEPTED SOLUTIONS
edhans
Super User III
Super User III

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:

edhans_0-1620314871499.png

into this filtered list:

edhans_1-1620314891269.png

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:

edhans_2-1620315163064.png

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.

 



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

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
            )

View solution in original post

8 REPLIES 8
edhans
Super User III
Super User III

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:

edhans_0-1620314871499.png

into this filtered list:

edhans_1-1620314891269.png

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:

edhans_2-1620315163064.png

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.

 



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

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

 

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.



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

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
            )

View solution in original post

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:

edhans_0-1620331326076.png

 

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.

 



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

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)
            )

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"

 

Excellent @pmbaranski - Glad I was able to help.



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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors