cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TechR21
Helper III
Helper III

Duplicate table with selection of records

Hi,

 

I have a table that gets daily refreshed from odatafeed and contains over 100.000 records. I want to duplicate this table, and create a selection to only load the last 2 weeks based on a creationdate of the records in the table, then each time when it gets refreshed it only loads the last 2 weeks.

 

How can I do this? 🙂

1 ACCEPTED SOLUTION

Hi @TechR21 

 

Enter the following code into the formula bar to create a custom step. Replace "previous step name" with the previous step in your PQ. This will take Monday as the week start day. 

= Table.SelectRows(#"previous step name", each [Date] < Date.From(Date.AddDays(DateTime.LocalNow(), -Date.DayOfWeek(DateTime.LocalNow(), Day.Monday))) and [Date] >= Date.From(Date.AddDays(DateTime.LocalNow(), -Date.DayOfWeek(DateTime.LocalNow(), Day.Monday)-14)))

vjingzhang_0-1669283944312.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

4 REPLIES 4
DataSlayer
Continued Contributor
Continued Contributor

Hello again,

 

here you go : 

 

DataSlayer_0-1669210236586.png

 

Please dont forget to accept it as a solution if it worked for you 🙂

DataSlayer
Continued Contributor
Continued Contributor

Hello,

 

On power Query, right click on the table then press duplicate, then on the duplicated table, go to the date column and press on it, then choose dates filter and filter data that is in the last 2 weeks.

 

Please accept this as a solution if it helped you

I can only select last week or from a specific date, but that wouldnt work as it should not be from a specific date

TechR21_0-1669209999150.png

 

and also i see it takes the weekstart as sunday instead of monday

 

Hi @TechR21 

 

Enter the following code into the formula bar to create a custom step. Replace "previous step name" with the previous step in your PQ. This will take Monday as the week start day. 

= Table.SelectRows(#"previous step name", each [Date] < Date.From(Date.AddDays(DateTime.LocalNow(), -Date.DayOfWeek(DateTime.LocalNow(), Day.Monday))) and [Date] >= Date.From(Date.AddDays(DateTime.LocalNow(), -Date.DayOfWeek(DateTime.LocalNow(), Day.Monday)-14)))

vjingzhang_0-1669283944312.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.