Forum Discussion

Sandeep13's avatar
Sandeep13
Helper III
2 years ago
Solved

Get latest 7 days data to TABLE View

Hi Team

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

DateTOTAL_COUNT
25-May-24532765
26-May-24451288
27-May-24424338
28-May-24245246
29-May-24498585
30-May-24471757
31-May-244541757
1-Jun-241000
2-Jun-242000
3-Jun-245000
4-Jun-24700

 

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 

 

 

 

Need Help
  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    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!

     

     

     

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

     

     

7 Replies

  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    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!

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Sandeep13
      Helper III
      2 years ago

      Hi 

      It worked really well.  you guys are awasome😊

  • Jaytam's avatar
    Jaytam
    Frequent Visitor
    2 years ago

    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's avatar
      Sandeep13
      Helper III
      2 years ago

      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 ?