Forum Discussion

shamilka's avatar
shamilka
Frequent Visitor
2 years ago
Solved

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

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"

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

    • shamilka's avatar
      shamilka
      Frequent Visitor
      2 years ago

      Hi ahadkarimi,

       

      I'm loading from the database

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      shamilka
      Frequent Visitor
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.