locked
from MS ACCESS - load only filtered data, not all rows RRS feed

  • Question

  • Hello,

    is it possible to use a parameter while loading data from MS Access?

    From what I understand the build-in MS Access connection loads the whole data table and if I want to filter the records I need to insert a new step Filtered Rows. That means that the initials steps will load the whole table and filters it afterwards. This could significantly increase the processing time, because a lot of unnecessary records will be loaded just to be filtered out in the next step. 

    My current PQ code is:

    let
        Source = Access.Database(File.Contents("G:\xxx\DB.accdb"), [CreateNavigationProperties=true]),
        qry = Source{[Schema="",Item="table"]}[Data],
        #"Filtered Rows" = Table.SelectRows(qry, each ([year] = 2018) and ([month] = 10))
    in
        #"Filtered Rows"

    My question is: Is it possible to declare a parameter while loading the table in the firsts steps and avoid the "post-load" filtering?


    Regards,

    Jakub Dušek


    • Edited by jakub dusek Thursday, October 25, 2018 2:40 PM
    Thursday, October 25, 2018 2:40 PM

Answers

  • Is the premise of this question correct? When you do a filter step after connecting to an Access Database, Power Query should be working as a native query in the background. If I connect to access and apply a filter step, then look at the Native Query happening, it's writing a "SELECT * FROM * WHERE" query on the backend. I don't know the details of how the steps work on a Native Query, but I would think that means it's only using the final query when loading the data?
    • Marked as answer by jakub dusek Monday, October 29, 2018 1:34 PM
    Friday, October 26, 2018 4:51 PM
  • Hello,

    thank you very much for this information! After a brief research I've found out that you are correct. I was able to display the native query for Access data source:

    That shows the following WHERE statement (2018 and 10 are bound to a dynamic parametr):

    This implies that my assumption was wrong and Power Query does not load all rows and filter them aftewards.

    Warm regards,

    Jakub

    • Marked as answer by jakub dusek Monday, October 29, 2018 1:34 PM
    Monday, October 29, 2018 1:34 PM

All replies

  • Hi

    You should build a Query in Access that filters your Table on [year]=2018 and [month]=10, save that query and connect, not to the Table but to the Query. Just checked here and no problem I only get records filtered by the Access query come up

    Cheers


    • Edited by Lz._ Thursday, October 25, 2018 3:03 PM typo
    Thursday, October 25, 2018 3:02 PM
  • Yes that would work for static parameters, But I want the parameters to by dynamic, for example Maximal Year and its maximal month.. or any other dynamic set of parameters - from another query for example.

    Is there any way?

    Regards

    Thursday, October 25, 2018 3:13 PM
  • Hi

    I see what you want to do but don't know if this is something doable with Access (SQL yes - see here).

    I don't want to be pessimistic but I just made a quick test adding a parameter to a basic Access query and when accessing the DB that query isn't available in the Power Query Navigator. Removing the parameter from the Acces query make it available in the Navigator.

    Don't take this as a definitive answer. Maybe someone figured out a way to do it… And in case this could help give it a try with SQL Server Express

    Friday, October 26, 2018 4:07 PM
  • Is the premise of this question correct? When you do a filter step after connecting to an Access Database, Power Query should be working as a native query in the background. If I connect to access and apply a filter step, then look at the Native Query happening, it's writing a "SELECT * FROM * WHERE" query on the backend. I don't know the details of how the steps work on a Native Query, but I would think that means it's only using the final query when loading the data?
    • Marked as answer by jakub dusek Monday, October 29, 2018 1:34 PM
    Friday, October 26, 2018 4:51 PM
  • Hello,

    thank you very much for this information! After a brief research I've found out that you are correct. I was able to display the native query for Access data source:

    That shows the following WHERE statement (2018 and 10 are bound to a dynamic parametr):

    This implies that my assumption was wrong and Power Query does not load all rows and filter them aftewards.

    Warm regards,

    Jakub

    • Marked as answer by jakub dusek Monday, October 29, 2018 1:34 PM
    Monday, October 29, 2018 1:34 PM