using same power query source 12 times..or import full year? RRS feed

  • Question

  • Hi,
    I have 12 power querys for monthly data, sourced from an access query in a local database. This Access source query (qryExpenseByYear) selects data for a full year.

    I assume for each month's power is rerunning the full year's access query, which seems inefficieent with network etc.

    IF so...should i just bring in the full years data once into a sheet, then have each of the monthly Power queries go against that sheet?

    (Here's sample of monthly for January query, with each subsequent month's query just changing the [monthnbr]

    Source = Access.Database(File.Contents("J:\Geriatrics\CAPC\Unrestricted\Finance\Access\MonthlyRptsTesting\CAPC Finance Database_beTest.accdb"), [CreateNavigationProperties=true]),
        _qryExpenseByYear = Source{[Schema="",Item="qryExpenseByYear"]}[Data],
        #"Filtered Rows" = Table.SelectRows(_qryExpenseByYear, each [monthnbr] = 1),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"monthnbr"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Foundation", "PROJECT_CODE", "Month", "INVOICE_DATE", "VENDOR_NAME", "AMT", "PROJECT_CODE2", "SubCode", "Title", "DESCR1", "ENTRY_DATE", "UpdateDate", "Updated"})
        #"Reordered Columns"

    Tuesday, June 30, 2020 9:45 PM

All replies

  • In Powerquery there is the "folding" concept.
    More about it in the Query Folding chapter here : Power Query M Primer (Part 5): Paradigm

    It means that whenvever possible, the filters and transformations will happen at the server side.
    In your case, your access database will provide only 1 month of data to each query. And then your query looks good and efficient.

    • Edited by anthony34 Thursday, July 2, 2020 7:25 AM
    Thursday, July 2, 2020 7:22 AM