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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sandeep13
Helper II
Helper II

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.

Sandeep13_0-1717404628903.png

 

 

@Amit @amitchandak @Bibi @Expert @mPower 

 

 

 

2 ACCEPTED SOLUTIONS
ManuelBolz
Responsive Resident
Responsive Resident

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!

 

 

 

 

View solution in original post

v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1717567811920.png

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

vheqmsft_1-1717567859097.png

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

 

 

 

View solution in original post

7 REPLIES 7
Sandeep13
Helper II
Helper II

Hi @ManuelBolz ,

 

Thank you so much. your logic work really well.

AlienSx
Super User
Super User

v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1717567811920.png

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

vheqmsft_1-1717567859097.png

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

 

 

 

Hi 

It worked really well.  you guys are awasome😊

Jaytam
Regular Visitor

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!

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 ?

Sandeep13_0-1717652240311.png

 

ManuelBolz
Responsive Resident
Responsive Resident

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!

 

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors