cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kattlees
Post Patron
Post Patron

Limit import based on date

I am new to Power BI and have decided to do import vs direct query.

 

Is there a way to limit the records imported based on date to save time/space? Our system has 20 years of data and if we only want to import the last 5 or 10 years. Is this possible?

1 ACCEPTED SOLUTION

select filter on your date column and then choose parameter as show below in below circle, and select your parameter.

 

filter.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

31 REPLIES 31
graeffel
Regular Visitor

I have the same problem and get a runtime error when I pull data in Power BI from a datacube of our navision system. How can I limit the query to one year which would be then 'lighter' and would hopefully work? Can someone help me with the code, which I could then enter manually? 

 

Here is standard query in power bi:

 

let
Source = AnalysisServices.Databases("AZR-xxx\xxx", [TypedMeasureColumns=true, Implementation="2.0"]),
SA4NAV4_NORD = Source{[Name="SA4NAV4_NORD"]}[Data],
#"Perfect Order Analysis NAV1" = SA4NAV4_NORD{[Id="Perfect Order Analysis NAV4"]}[Data],
#"Perfect Order Analysis NAV2" = #"Perfect Order Analysis NAV1"{[Id="Perfect Order Analysis NAV4"]}[Data],
#"Added Items" = Cube.Transform(#"Perfect Order Analysis NAV2",

sanjoyleo
Helper I
Helper I

Hi ,

 

How did you solve this ? I need the same...as I want to limit last 5 years of data while using Import option from SQL DB . Table has 10 years of data  . So it shuld always fetch this year i.e 2018 and 2017,2016, 2015,2014 .

 

And when it is  2019 then 2018,2017,2016, 2015..

 

Thanks in Advance

 

I went to the edit queries and just limited records by a specific date. Using the date functions, I think you can do last x number of years.

@sanjoyleo I can help with you that. Not sure how familiar you are with Power Query "M" language, if you can send your query script, I will provide you the solution.

 

Go to query editor, click advanced editore, and copy the script and paste it here.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi,

Thanks for your reply!! Please find below ..Table name is TestResults . It has a Column called "ResultTime" with Date-Time format ..sample values like :

3/9/2017 3:39:53 PM

 

So here What I want is to fetch only 3 years of data (2016, 2017,2018 )  and my table has 10 years of data.

Please note from Jan 1st 2019 , data should be fetched automatically for 2017,2018,2019 ...

So  basically last 2 years and current years which will be incremental.

 

Please help  !

 

-------------------

let
Source = Sql.Databases("179.90.2.99"),
WebFT_SanjoyB = Source{[Name="WebFT_SanjoyB"]}[Data],
dbo_TestResults = WebFT_SanjoyB{[Schema="dbo",Item="TestResults"]}[Data]
in
dbo_TestResults

---------------

let
Source = Sql.Databases("179.90.2.99"),
#"Year" = Date.Year(DateTime.LocalNow())-3,
WebFT_SanjoyB = Source{[Name="WebFT_SanjoyB"]}[Data],
dbo_TestResults = WebFT_SanjoyB{[Schema="dbo",Item="TestResults"]}[Data]
#"Filtered Rows" = Table.SelectRows(#"dbo_TestResults", each Date.Year([Scheduled Date]) >= #"Year")
in
    #"Filtered Rows"

Above red lines are the changes in your code and replace blue [Scheduled Date] with the date column name in your model.

 

This will do it.

 

Thanks,

P






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi parry 2k,

 

Hoping you can help me with something similar. My table has 10 years worth of data as well, but I would like to filter on a date before all 10 yrs load. Ideally I would like to load current month or current year, either one works. Here is the info:

 

 let
    Source = Odbc.DataSource("dsn=Chempax Live", [HierarchicalNavigation=true]),
    CHEMPAX_Database = Source{[Name="CHEMPAX",Kind="Database"]}[Data],
    SQLVIEW_Schema = CHEMPAX_Database{[Name="SQLVIEW",Kind="Schema"]}[Data],
    #"BATCH-REC-HDR_View" = SQLVIEW_Schema{[Name="BATCH-REC-HDR",Kind="View"]}[Data],
    #"Filtered Rows" = Table.SelectRows(#"BATCH-REC-HDR_View", each Date.IsInCurrentMonth([#"Receipt-Date"]))
in
    #"Filtered Rows"

parry  

In query editor, I opened up the source of the table and clicked on advanced options and wrote my own sql statement to limit by date.

@Kabartz let me know if you still need assistance.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

Yes, I do. If you have some time to help I would appreciate it!

 

I tried to re-create your solution for someone else on this string to pull in 1 yrs worth of data instead of the full 10 years and then filtering. Do I have my #Year in the correct spot? This is an ODBC connection to an OpenEdge software called Chempax.

 

let
    Source = Odbc.DataSource("dsn=Chempax Live", [HierarchicalNavigation=true]),
    #"Year" = Date.Year(DateTime.LocalNow())-1,
    CHEMPAX_Database = Source{[Name="CHEMPAX",Kind="Database"]}[Data],
    SQLVIEW_Schema = CHEMPAX_Database{[Name="SQLVIEW",Kind="Schema"]}[Data],
    #"BATCH-REC-HDR_View" = SQLVIEW_Schema{[Name="BATCH-REC-HDR",Kind="View"]}[Data],
    #"Filtered Rows" = Table.SelectRows(#"BATCH-REC-HDR_View", each Date.Year([#"Receipt-Date"]) >= #"Year"
    )
in
    #"Filtered Rows"

@sanjoyleoI will get back to you on this later today. Stay tuned.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





This is how I would do it.

 

Click on your EDIT QUERIES tab

Select the filed you want to limit to 3 years

Click on the drop down arrow to the right of that field

Choose Date Filters

Choose In the Previous

When Filter Rows comes up, have it read is in the previous 2 years then OR

is in year This Year

 

See screenshot

 

ScreenShot.jpg

Thanks a lot @kattlees !! It works .

 

I can see after doing that  , I got below line in Qry .

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousNYears([ResultTime], 2) or Date.IsInCurrentYear([ResultTime]))

 

One question here -- It's not the case that all the Table's data has been fetched into my System (as I did import) and from there this filter has been applied ? Or this filter applied while fetching the data itself from SQL DB ?

 

Thanks in Advance !

I believe all the data is imported in and then filtered by the date you chose. You can change the date and get more data if you want.

 

If you don't want it to import at all, I would use @parry2k solution as he is a power bi super user! He has helped me on numerous occassions. 

To @parry2k 

 

Can you please give your valuable feedback here ? I was thinking If my Transaction table has 10M of data , then pulling all records and then filter in Desktop is a good option or not as I am using Import as connection.

 

Thanks

Sanjoy

@sanjoyleo What is your data soure? Sql? Better to restrict at the source if you can. May be create a view at source, and pre filter the data for the period you need and then use that view in PowerbI.

 

Let me know what is your data source then we can work from there.

 

Thanks,

P






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Thanks for your Reply @parry2k !

 

My Source is Microsoft SQL Server 2016 ,Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64>  .

 

Transaction Table has 15 M data ,other Transaction_details tables also approx 16M data, card related data 10M .. all those are with 10 years of data.. so I might be working on last 3 years of data , which would be coming to 4ML approx from each tables.

 

At this time I started from Import as in directquery not sure what option I will not be able to get , as I read in Directquery there are some limitation.

 

This is the first time I am going to create live report for company and in PROD I will not have access, so how my qry/report refresh will be performing is a big question to me !! I am creating report with Dev data and when it will be published Senior folks will be refreshing data from PROD.

 

So let me know what should be my approach ..do we need to create de-normalaize DB on top of this transaction ? Or view should be fine enough ?

 

Another question-- Suppose I have 5 tables called A,B,C,D .. now if   And B gets joined I will get X number of rows.. if A & C gets join I would Y(less than X as only few data would be joined on key)   number of rows.

My report needs X data for one type of report and another report needs Y data. SO in Relationship Should we have join relationship Betn B->A->C  ?? Or I should create sepated pbx file importing A & B and in another pbx file A & C ?

 

 

 

Anonymous
Not applicable

Hai @sanjoyleo did you solved this scenario, if yes could you please share the procedure...

Yes, in Query Editor under source, then under advanced you can write and SQL statement to only pull in fields you want and put limits on such as dates.   Really helps limit the amount of data you can pull in.

Hi Kattlees,

 

I have a simillar problem of restricting the number of rows while importing the data from SQL server to power bi using Import connection .

 

I have seen the code for limiting the records from the thread and tried the same but it is showing error.I could not understand the error.

 

Below is my code.

 

let
Source = Sql.Databases("ejazah-live-rds.c52bhszlapad.ap-south-1.rds.amazonaws.com"),
EjazahLive = Source{[Name="EjazahLive"]}[Data],
dbo_tb_SalesData = EjazahLive{[Schema="dbo",Item="tb_SalesData"]}[Data]
FilteredRows = Table.SelectRows(dbo_tb_SalesData, each Date.Month([booking_date])=3)
in
FilteredRows

 

booking_date is adatetime field in the database.

 

Your help is much appreciated!

Thanks

Lavanya

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!