Power Query - Filter columns where the date is less than the current week

[Dayna]

Hello,

I'd like to filter my source data to show me all records up to the current week, but not including the current week. I've tried adding a custom column where I create a "Today Week" by doing:

= Table.AddColumn(#"Added Custom2", "Week (Today)", each Date.WeekOfYear(DateTime.LocalNow()))

AND

= Table.AddColumn(#"Merged Columns", "Year (Today)", each Date.Year(DateTime.LocalNow()))

Then joining the two fields together, but I'm not seeing how I could then filter my existing year week by this?

Additionally, the formatting gets skewed on the week for when it's single digits - if this was the path to go down, how to I pad it with an extra leading 0 where it's a week number less than 10?

Many thanks for your help,

Dayna

[Dayna]

Thank you @v-easonf-msft 

[v-easonf-msft · Accepted solution]

Hi, @Dayna 

1.As a workaround, you might consider adding an extra column or a parameter 'Start of week' to get the start date of the current week

=Table.AddColumn(#"Added Custom", "Start of Week(Today)", each Date.StartOfWeek(DateTime.LocalNow()))

 and then filter all data with dates less than the start date of the current week.

= Table.SelectRows(#"Inserted Start of Week", each [Date] < Date.StartOfWeek(DateTime.LocalNow()))

Here you need to confirm that the data types of the columns are the same(Date/Time)

2.You can try Text.PadStart to format the WeekOfYear.

=Table.AddColumn(#"Inserted Week of Year1", "New week of Year", each Text.PadStart(Text.From([Week of Year], "en-US"), 2, "0"), type text)

Best Regards,
Community Support Team _ Eason