Getting only the latest database data instead of downloading the whole table in the beggining

[shamilka]

Hi All, 

I'm using the following code to load the data to Power BI. Since my filteration at the the next step of the code, it will download the whole table. I want to download only the latest data for the past 15 days. Could you please let me know whether we can adjust the following code on the highlighted first row to extract the data only related the past 15 days? 

My code is given below. Please ammend the code accordingly. 

Thank you.

Code:

 AB_history = Source{[Schema="AB",Item="history"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(AB_history,{"partno", "serialno","del_date"}),

    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([vm] = "YA" or [vm] = "YE")),

    #"Ad_Date" = Table.AddColumn(#"Filtered Rows", "Date", each Date.AddDays(#date(1971,12,31), [created_date]), type date),

    #"FilteredLast15DaysFromToday" = Table.SelectRows(#"Ad_Date", each [Date] >= Date.AddDays(Date.From(DateTime.LocalNow()), -15))

in 

  #"FilteredLast15DaysFromToday"

[v-junyant-msft]

Hi @shamilka ,

If you want to import only the last 15 days of data when Power Query connects to the data source, rather than importing all the data and then filtering for the last 15 days, you can either pre-filter the data source for the last 15 days before connecting to the data source, or you can enable an advanced setting when connecting to the data source to use some database code (e.g., SQL) to limit the data to only the last 15 days data can be imported.

That's why we need to know what your data source is. Because not all data sources support advanced settings.

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

[shamilka]

Could you please write me a sample query to filter the data for the last 15 days please?

[v-junyant-msft · Accepted solution]

Hi @shamilka ,

I will use SQL Server as datasource for an example:
Sample data (Today is 2024.8.20):

When connecting to SQL Server:

Here is the SQL Code in the SQL statement:

select * from Table_4 where date <= CAST(GETDATE() as date) AND date >= DATEADD(DAY, -15, CAST(GETDATE() as date));

And the final output is as below:

Here is the M code in Advanced Editor:

let
    Source = Sql.Database("DM1", "Import", [Query="select * from Table_4 where date <= CAST(GETDATE() as date) AND date >= DATEADD(DAY, -15, CAST(GETDATE() as date));"])
in
    Source

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

[ahadkarimi]

Hey @shamilka, could you let me know if the data is being loaded from SQL or ...?

[shamilka]

Hi ahadkarimi,

I'm loading from the database

[watkinnc]

If you make a separate query (let's name it LastDate) that returns just the Date-15 days, using the Dates.AddDays method that you already know, you can then rearrange your original query by making your Date filter the first filter, and make your SelectRows statement each [Date] = LastDate. This should change your SQL to include "Where .Date = (the value of LastDate). If you check that step, you should see it folding.

--Nate