Forum Discussion

kattlees's avatar
kattlees
Post Patron
8 years ago
Solved

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 w...
Need Help
  • parry2k's avatar
    parry2k
    8 years ago

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

     

sanjoyleo's avatar
sanjoyleo
Helper I
8 years ago

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 !

kattlees's avatar
kattlees
Post Patron
8 years ago

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. 

  • sanjoyleo's avatar
    sanjoyleo
    Helper I
    8 years ago

    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

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    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

  • sanjoyleo's avatar
    sanjoyleo
    Helper I
    8 years ago

    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's avatar
    Anonymous
    Not applicable
    8 years ago

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

  • sanjoyleo's avatar
    sanjoyleo
    Helper I
    8 years ago

    Hi GeetzAnju,

     

    I  have seen that MS is going to implement,not sure if they implement in last 2 months as I was not following that.

    But as of now there is no was to do partial import or Delta import. In my Solution I moved to Direct query as I used date filter to show the data. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thank you so much for your response sanjoyleo I am also working in the same scenario, I have two parameters such as the StartDate and EndDate and it works fine in the Power BI Desktop and Power BI Service too but my problem is it was not working properly in Power BI embedded, I have embedded my report in Asp.Net MVC application and for the parameters I have provided two listboxes for the user to select the StartDate and EndDate, and I am using javascript for specifying the filters to Power BI Service but the filter was not reflecting in the report. Do you have any idea about this please provide your suggestion.

     

    Kindly Regards,

    Geetha

  • kattlees's avatar
    kattlees
    Post Patron
    8 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thank you kattlees for your suggestion and it is perfectly working already but my issue is on Power BI Embedded could you please give me a suggestion on that.

     

    Kindly Regards,

    Geetha

  • sanjoyleo's avatar
    sanjoyleo
    Helper I
    8 years ago

    Hi,

     

    I didn't work on that scenario.. so can't help much here.

  • lavanyak's avatar
    lavanyak
    Advocate I
    7 years ago

    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