Get latest 7 days data to TABLE View

[Sandeep13]

Hi Team

I have one excel source which contain Date column  like below.

Date	TOTAL_COUNT
25-May-24	532765
26-May-24	451288
27-May-24	424338
28-May-24	245246
29-May-24	498585
30-May-24	471757
31-May-24	4541757
1-Jun-24	1000
2-Jun-24	2000
3-Jun-24	5000
4-Jun-24	700

Now I want to filter latest 7 days data to my Table view.

So when Next time 5 Jun-24 is added in my source. It is auto take latest 7 days (5 june - 30 May). I am trying this in power  query  using date filter.

I am trying using below logic but its now working.

@Amit @amitchandak @Bibi @Expert @mPower 

[ManuelBolz · Accepted solution]

Hello @Sandeep13,

try this solution:

let
    Source = YourExcelFile,
    Datetype = Table.TransformColumnTypes(Source,{{"Date", type date}, {"TOTAL_COUNT", Int64.Type}}),
    Filter = Table.SelectRows(Datetype, each [Date] >= Date.AddDays(List.Max(Datetype[Date]),- 6) and [Date] <= List.Max(Datetype[Date]))
in
    Filter

The magic is in the Filter Step:
1. Get the max value of your [dates].

2. Filter between max [dates] and max [dates] -7

Did I answer your question? Mark my post as a solution!

[Sandeep13]

Hi @ManuelBolz ,

Thank you so much. your logic work really well.

[AlienSx]

Table.LastN

[Anonymous · Accepted solution]

Hi @Sandeep13 ,
Thanks to @ManuelBolz  and @Jaytam reply.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc27DcMwDIThXVhbgPg4kV4hQCYwVGSBdCmyfQLQDpX2w+G/4yBBuz/eTYw2gooP0Ny+PIoNLBHJvrCY6slRLAaxkbwv6z0Q2da+sLPDk3m9tJ9zu72eydx7z3KZXKZluMzK/CT85WjODw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, TOTAL_COUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"TOTAL_COUNT", Int64.Type}}),
    #"SortedTable" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"rowCount" = Table.RowCount(#"SortedTable"),
    #"rowsToSkip" = if #"rowCount" > 7 then #"rowCount" - 7 else 0,
    #"lastSevenDays" = Table.Skip(#"SortedTable", #"rowsToSkip")
in
    #"lastSevenDays"

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Sandeep13]

Hi 

It worked really well.  you guys are awasome😊

[Jaytam]

Hi @Sandeep13 

You can follwo the following steps which i have done as a sample use case:

• Open Power Query Editor:

  • Click Transform Data to open the Power Query Editor.

• Add Seven Days Ago Column:

  • Go to Add Column -> Custom Column.
  • Name the column SevenDaysAgo and use the formula Date.AddDays(DateTime.LocalNow(), -7).

• Filter the Data:

  • Select your Date column.
  • Click on the filter icon, choose Date Filters -> Is After or Equal To.
  • In the filter window, select the SevenDaysAgo column.

• Remove Helper Column:

  • Remove the SevenDaysAgo column by selecting it, right-clicking, and choosing Remove Columns.

• Close and Apply:

  • Click Close & Apply to load the filtered data into Power BI.

@Sandeep13  let me know if this works for you and if this works please marked as a solution done!

[Sandeep13]

Hi @Jaytam ,

Thanks for your comment. I tried your approch but stuck at below part.

• Click on the filter icon, choose Date Filters -> Is After or Equal To.
• In the filter window, select the SevenDaysAgo column.

What will be need to select here ?

[ManuelBolz · Accepted solution]

Hello @Sandeep13,

try this solution:

let
    Source = YourExcelFile,
    Datetype = Table.TransformColumnTypes(Source,{{"Date", type date}, {"TOTAL_COUNT", Int64.Type}}),
    Filter = Table.SelectRows(Datetype, each [Date] >= Date.AddDays(List.Max(Datetype[Date]),- 6) and [Date] <= List.Max(Datetype[Date]))
in
    Filter

The magic is in the Filter Step:
1. Get the max value of your [dates].

2. Filter between max [dates] and max [dates] -7

Did I answer your question? Mark my post as a solution!