locked
Parameter for a filter for Power Query in Excel 2013 RRS feed

  • Question

  • Hello,

    I need guidance on how to parameterize a filter for Power Query in Excel 2013. I am retrieving data from and Excel file and SQL server. 

    Thanks for the help!

    Best,

    John

    Friday, August 26, 2016 9:16 PM

Answers

  • The easiest way is to first build a non-parameterized query, through the GUI or in M. Then, open the advanced editor which shows the M script for the query. Identify the parts in the script that you want to be a parameter. Add a new line to the script before all others with the syntax

    (Param1, Param2, Param3) =>

    and replace the constants in the script by the Param1 (2, 3) keyword.

    For example, a query loading a worksheet could look like

    let

       Source = Excel.Workbook(File.Contents("C:\Data\Example.xlsx"), null, true),

       <etc.>

    This can be parameterized, or changed into a function, by

    (Path, File) =>

    let

       Source = Excel.Workbook(File.Contents(Path & File), null true),

       <etc.>

    • Proposed as answer by Imke FeldmannMVP Tuesday, August 30, 2016 5:22 AM
    • Marked as answer by Otto Knoke Wednesday, August 31, 2016 7:27 PM
    Monday, August 29, 2016 9:47 AM