Use Power Query with Parameter to Select Records from SharePoint Online List RRS feed

  • Question

  • Hi,

    I've connected a sharepoint online list to an excel spreadsheet with Power Query.  All works fine.  When I refresh the data source, power query returns all of the rows from the list. This takes quite a bit of time as there are over 4000 records in the list.

    I want to parameterize the source query of the list so that it only returns items based on a selection from a dropdown list in the spreadsheet, therefore speeding up the refresh time.

    Any help would be appreciated!



    Tuesday, July 24, 2018 4:52 PM


  • Hi David

    Name your drop-down list ie. mySelection with a workbook scope. Then, at the top of your query M code add:

        // Parameter
        QryParam = Excel.CurrentWorkbook(){[Name="mySelection"]}[Content]{0}[Column1],

    then use QryParam to filter...

    Tuesday, July 24, 2018 7:14 PM