Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TechR21
Helper V
Helper V

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
MAwwad
Super User
Super User

Hello again,

 

here you go : 

 

DataSlayer_0-1669210236586.png

 

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

MAwwad
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.