Forum Discussion
Filtering Rows for all dates within the next two days, excluding weekends.
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.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 )
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.
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.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 )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.