none
Dynamic Selection of Query to Load to Table RRS feed

  • Question

  • <EDIT>

    When going to try this out using VBA in Excel 2016, I noticed the Expression.Evaluate method appears to work as I intended there. My guess is that maybe because PQ is more integrated into 2016, the scoping issue does not arise there? Either that or this does not work in newer PQ versions because of scoping filtering behaviors that were added (2013 is on version 2.59.xxx and 2016 is on version 2.46.xxx

    </EDIT>

    I have a bunch of datasets that need to have a number of different comparisons/analysis made between them. I've create these different comparisons as connection only queries. I would like to be able to have the user, from the workbook, select one of these comparisons and have it loaded into a set sheet in the workbook. So I tried to use Expression.Evaluate on a blank query and use a parameter (ComparisonQuery) to name the query I want to load.

    = Expression.Evaluate(ComparisonQuery, #shared)


    This works inside of power query, and it works when this "loader" query is initially loaded into a table on the workbook. However when I try to refresh the query I get a "The name (ComparisonQuery) doesn't exist in the current context." error. 

    I also tried to access it by doing

    let
        Source = #shared,
        #"Converted to Table" = Record.ToTable(Source),
        #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = ComparisonQuery)),
        Value = #"Filtered Rows"{0}[Value]
    in
        Value


    And get a "There weren't enough elements in the enumeration to complete the operation." error instead.

    Both of these methods seem to be functioning in the editor. Basically I just want a single reference query that lets me dynamically select which query it is referencing with a parameter value loaded from the workbook, and to load that into a table in Excel. Is there any way I can set this up to work correctly?

    I would imagine it might be possible with VBA in Excel as I think I can just dynamically rewrite the query itself like it was a normal reference query, but I was trying to avoid getting macros involved.



    • Edited by pdelia Wednesday, November 28, 2018 10:56 PM
    Wednesday, November 28, 2018 10:37 PM

Answers

  • Hi Ehren,

    How does your explanation account for the fact that Expresion.Evaluate in this scenario works with Excel 2010 and Excel 2016 but not in Excel 2013?

    Hi Colin. While I'm not super familiar with all the addin mechanics, I was able to find the code that minimizes the mashup package (i.e. removes non-referenced queries from the M formula text) that's stored in the Excel connection string. It gets called in the addin, but doesn't seem to be called for native Excel (Excel 2016). I'm not sure why it isn't also being called in Excel 2010, but perhaps the addin works slightly differently in that context.

    Ehren


    Tuesday, December 4, 2018 6:13 PM
    Owner
  • Hi pdelia. If you know the possible set of queries you'd like the user to choose from, and it's relatively fixed and unlikely to change much over time, you could try coding something like the following:

    = if ComparisonQuery = "Query1" then Query1 else if ComparisonQuery = "Query2" then Query2 else ...

    Avoiding the use of Expression.Evaluate will definitely help your solution be more robust.

    Ehren

    Thursday, November 29, 2018 10:27 PM
    Owner
  • I did work something out, based on setting the parameter in Excel, and then building the ComparisonQuery in Power Query. Credit to Ehren for correcting my earlier, misinformed response.

    1) In Excel, create a list of the table names that the user will select to return to Excel. Give the list a name like ComparisonQueryList

    2) In the cell that you specify for the user selection, use Data Validation, specifying the list name as the source. Select the first entry in the newly created data validation cell (the selection is blank by default).

    3) Name the validation cell ComparisonQuery

    3) Click the validation cell and select Data-->From Table/Range. Power query creates the query ComparisonQuery. Remove the "Changed Type" and "Promote Headers" steps.

    4) Modify the ComparisonQuery query as follows. I actually used Expression.Evaluate in defiance of Ehren's warning :-) 

    let
        SourceParameter = Table.FirstValue(Excel.CurrentWorkbook(){[Name="ComparisonQuery"]}[Content]),
        Source = Expression.Evaluate(SourceParameter, #shared)
    in
        Source

    5) Load the ComparisonQuery query to Excel.

    6) Decide how you will handle refreshes after a user selection (won't occur automatically).


    Thursday, November 29, 2018 11:36 PM

All replies

  • "...select one of these comparisons and have it loaded into a set sheet in the workbook"

    The determination of loading a query to Excel is done in Power Query. There is no way to do so conditionally.

    Thursday, November 29, 2018 6:53 PM
  • Hi pdelia. If you know the possible set of queries you'd like the user to choose from, and it's relatively fixed and unlikely to change much over time, you could try coding something like the following:

    = if ComparisonQuery = "Query1" then Query1 else if ComparisonQuery = "Query2" then Query2 else ...

    Avoiding the use of Expression.Evaluate will definitely help your solution be more robust.

    Ehren

    Thursday, November 29, 2018 10:27 PM
    Owner
  • I did work something out, based on setting the parameter in Excel, and then building the ComparisonQuery in Power Query. Credit to Ehren for correcting my earlier, misinformed response.

    1) In Excel, create a list of the table names that the user will select to return to Excel. Give the list a name like ComparisonQueryList

    2) In the cell that you specify for the user selection, use Data Validation, specifying the list name as the source. Select the first entry in the newly created data validation cell (the selection is blank by default).

    3) Name the validation cell ComparisonQuery

    3) Click the validation cell and select Data-->From Table/Range. Power query creates the query ComparisonQuery. Remove the "Changed Type" and "Promote Headers" steps.

    4) Modify the ComparisonQuery query as follows. I actually used Expression.Evaluate in defiance of Ehren's warning :-) 

    let
        SourceParameter = Table.FirstValue(Excel.CurrentWorkbook(){[Name="ComparisonQuery"]}[Content]),
        Source = Expression.Evaluate(SourceParameter, #shared)
    in
        Source

    5) Load the ComparisonQuery query to Excel.

    6) Decide how you will handle refreshes after a user selection (won't occur automatically).


    Thursday, November 29, 2018 11:36 PM
  • That is already what I was doing. The problem is that using Expression.Evaluate returns the "The name (NameOfDynamicQuery) doesn't exist in the current context." error whenever I try to refresh the query after changing the parameter (or actually trying to refresh it at all after the initial load into Excel). However this error is only occurring in Excel 2013, it seems to be working fine in 2016.

    The list of potential queries to load may be quite large, so I would definitely like to avoid a massive fixed "if else" chain.

    Friday, November 30, 2018 4:48 PM
  • "However this error is only occurring in Excel 2013..."

    That's pretty single-minded. In addition to Excel 2016, the solution I provided works fine in Excel 2010 with the Power Query add-in. Unfortunately, I don't have a machine with Excel 2013 installed to test the solution.

    Friday, November 30, 2018 5:32 PM
  • Hi pdelia. This is why I recommended against using Expression.Evaluate. When you refresh a query (after it's been loaded...not the first time you load it from the Query Editor IIRC), we analyze it and only include the M for the queries it explicitly references. Thus, the query selected by the user is likely not being evaluated and thus can't be dynamically referenced.

    Ehren

    Friday, November 30, 2018 6:02 PM
    Owner
  • Hi pdelia. This is why I recommended against using Expression.Evaluate. When you refresh a query (after it's been loaded...not the first time you load it from the Query Editor IIRC), we analyze it and only include the M for the queries it explicitly references. Thus, the query selected by the user is likely not being evaluated and thus can't be dynamically referenced.

    Ehren

    Hi Ehren,

    How does your explanation account for the fact that Expresion.Evaluate in this scenario works with Excel 2010 and Excel 2016 but not in Excel 2013?

    Friday, November 30, 2018 6:07 PM
  • Hi pdelia. This is why I recommended against using Expression.Evaluate. When you refresh a query (after it's been loaded...not the first time you load it from the Query Editor IIRC), we analyze it and only include the M for the queries it explicitly references. Thus, the query selected by the user is likely not being evaluated and thus can't be dynamically referenced.

    Ehren

    Has this analysis always taken place, or was it a recent optimization? The version of Power Query in Excel 2016 looks like it is from sometime in mid 2017, where in Excel 2013 it is the most recent release. I would be curious what versions Colin was using.

    Friday, November 30, 2018 6:58 PM
  • "I would be curious what versions Colin was using."

    Excel 365 Pro Plus with most recent monthly update.

    Excel 2010 Pro Plus with most recent version of the Power Query add-in.

    Friday, November 30, 2018 7:58 PM
  • Just to be clear on my setup.

    In PQ, I have three queries named Table1, Table2, and Table3. When the user makes a selection of a Table, the SourceParameter step returns either "Table1", "Table2", or Table3".

    The Source step will therefore evaluate to, for example, Expression.Evaluate("Table1", #shared). The expression thus returns the Table1 query. In your original code, it is not clear what ComparisonQuery evaluates to in your Expression.Evaluate expression (which subsequently gives you an error).

    Friday, November 30, 2018 8:40 PM
  • Hi Ehren,

    How does your explanation account for the fact that Expresion.Evaluate in this scenario works with Excel 2010 and Excel 2016 but not in Excel 2013?

    Hi Colin. While I'm not super familiar with all the addin mechanics, I was able to find the code that minimizes the mashup package (i.e. removes non-referenced queries from the M formula text) that's stored in the Excel connection string. It gets called in the addin, but doesn't seem to be called for native Excel (Excel 2016). I'm not sure why it isn't also being called in Excel 2010, but perhaps the addin works slightly differently in that context.

    Ehren


    Tuesday, December 4, 2018 6:13 PM
    Owner
  • Hi Ehren,

    How does your explanation account for the fact that Expresion.Evaluate in this scenario works with Excel 2010 and Excel 2016 but not in Excel 2013?

    Hi Colin. While I'm not super familiar with all the addin mechanics, I was able to find the code that minimizes the mashup package (i.e. removes non-referenced queries from the M formula text) that's stored in the Excel connection string. It gets called in the addin, but doesn't seem to be called for native Excel (Excel 2016). I'm not sure why it isn't also being called in Excel 2010, but perhaps the addin works slightly differently in that context.

    Ehren



    Interesting. Thanks for the additional info.
    Tuesday, December 4, 2018 6:19 PM