Filter Query by last Working Day

[Skybluekid]

Hi

I am a little stumped.

I would like to filter my query by the previous working day. So, if I was to run my query on a Monday it would only bring back items with a date of the previous Friday. But when I run it on the Tuesday, it would bring back results for the Monday etc.

Thanks in Advance

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    d = Date.From(DateTime.LocalNow()),
    dow = Date.DayOfWeek(d, Day.Saturday),
    // if day of week (dow) is Sat = 0, Sun = 1, or Mon = 2 then return Friday else yesterday
    filterdate = if dow < 3 then Date.AddDays(d, -dow -1) else Date.AddDays(d,-1),
    Result = Table.SelectRows(tbl, each [Date] = filterdate)
in
    Result

Book1
	A	B	C	D	E	F	G	H
1	Data				Query Output		(on May 3, 2023)
2	ID	Date	Qty		ID	Date	Qty
3	1	5/1/2023	11		2	5/2/2023	32
4	2	5/2/2023	32		3	5/2/2023	5
5	3	5/2/2023	5		4	5/2/2023	35
6	4	5/2/2023	35		11	5/2/2023	16
7	5	4/28/2023	33		15	5/2/2023	22
8	6	4/30/2023	23		19	5/2/2023	18
9	7	4/30/2023	24
10	8	4/30/2023	2
11	9	5/1/2023	7
12	10	5/1/2023	29
13	11	5/2/2023	16
14	12	4/27/2023	27
15	13	4/28/2023	19
16	14	4/30/2023	15
17	15	5/2/2023	22
18	16	5/1/2023	20
19	17	4/29/2023	38
20	18	5/1/2023	4
21	19	5/2/2023	18
22	20	5/1/2023	30
23
Sheet4

 

[Skybluekid]

This looks that it might work. Just a really stupid question, as I am still quite new to Power Query, where in my steps would I put this?

 

[JGordon11]

open a blank query, open the advanced editor, replace all the text in the advanced editor with the above code. Edit the code data source and column name to match your data source and column header of your Date column.

 

[Skybluekid]

Thank you very much for the Code, it works a treat.

 

[Skybluekid]

@JGordan11, Is there a way to incorporate the code into my original query? What my query does scan a folder of PDF's and extracts data. So with your solution, I am refreshing two queries. I should have mentioned this in my original question.