Forum Discussion
SQL Statement to limit Data import
I have this sql server with 5 database that contains millions of rows and it slows down the data IMPORT. I would like to import the data direct from the source with already limited data from the past 5 yrs (i.e. 2023, 2022, 2021, 2020, 2019) instead of filtering it from the query itself to lessen data processing. Column "Fiscal Year" is available in the database.
I am not familiar with SQL statement. Appreciate any help on how to code this. Thanks!
Hi kaye123 ,
I don't know what you are using the query language, so I will write an example in MSSQL for you to get the last 5 years. I recommend that you get the data by filtering it on the sql side.
select * from table_name where FY>=YEAR(GETDATE())-5
but if you want to get all the data then you have to add the following step to the data having the Power Query side
= Table.SelectRows(#"Changed Type", each [FY] >= Date.Year(DateTime.LocalNow())-5)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best regards 🙂
4 Replies
Hi kaye123 ,
I don't know what you are using the query language, so I will write an example in MSSQL for you to get the last 5 years. I recommend that you get the data by filtering it on the sql side.
select * from table_name where FY>=YEAR(GETDATE())-5
but if you want to get all the data then you have to add the following step to the data having the Power Query side
= Table.SelectRows(#"Changed Type", each [FY] >= Date.Year(DateTime.LocalNow())-5)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best regards 🙂kaye123 , You write a sql like
select * from Table_name where FY = 2022
or you can use M Parameter.
Refer to three ways from Guyincube too
Hi kaye123 ,
If you retrieve the data from one table in SQL Server database and limit the date range, you can follow the suggestion from amitchandak and Anonymous to write the below SQL statement and put it in SQL statement text area. And you can refer the following video to get it.
Pass parameter to SQL Queries statement using Power BI
However, if you need to get the limited data from multiple tables, you can consider to create a view in your sql server data source and refer that view in Power BI Desktop. Maybe there is other method can achieve the same requirement. Anyway, the method depends on your actual scenario. Please provide more details on it. Thank you.
Best Regards
Thanks everyone for the input. I have now used below SQL as my final code. Note FiscalYear is not a date but just a 4 digit year number. so I added a VARCHAR.
SELECT Column1, Column2, Column3
FROM DATAAll
WHERE FiscalYear >= CAST(YEAR(GETDATE()) - 5 AS VARCHAR(4))
