locked
Restrictions when using Expression.Evaluate? RRS feed

  • Question

  • In order to dynamically choose which query to reference I'm using Expression.Evaluate like this:

    = Table.Combine({Dispo, Expression.Evaluate(Table.SelectRows(EYs, each ([EY] = ParaPlan))[VorhPlan]{0}, #shared)})

    It returns everything correct and complete in the preview-window.

    However, when I try to load it into the spreadsheet, the following error is thrown and the download doesn't complete:

    Am I doing sth wrong here or are there know restrictions when using Expression.Evaluate?


    Imke Feldmann TheBIccountant.com

    Friday, April 22, 2016 7:25 PM

Answers

  • The query that's run in refresh is a trimmed version of the query that's run in the editor. In particular, a kind of static analysis is used to remove any queries which don't appear to be used in the top-level refresh query. This decision predates me, so I'm not sure about its motivations. Arguably, the presence of #shared should disable this trimming.

    In general, the combination of Expression.Evalute and #shared is dangerous as it prevents us from statically analyzing the query. For instance, this isn't supported for service refresh inside Power BI.

    Monday, April 25, 2016 2:33 PM

All replies

  • Could it be that this bug: https://social.technet.microsoft.com/Forums/en-US/dee5172a-44e4-4100-a1ca-0827f949d304/power-query-pivot-column-error-on-sorted-data-works-when-not-sorted?forum=powerquery

    corrupts the whole workbook?

    I've rebuild my workbook from stratch - 2x.

    Command also works standalone (with output to table), but not within the combination (just in preview - with refresh, but error when table output). Buffer doesn't help.

    Would the bugfix heal the corruption as well?


    Imke Feldmann TheBIccountant.com

    Saturday, April 23, 2016 4:55 PM
  • The query that's run in refresh is a trimmed version of the query that's run in the editor. In particular, a kind of static analysis is used to remove any queries which don't appear to be used in the top-level refresh query. This decision predates me, so I'm not sure about its motivations. Arguably, the presence of #shared should disable this trimming.

    In general, the combination of Expression.Evalute and #shared is dangerous as it prevents us from statically analyzing the query. For instance, this isn't supported for service refresh inside Power BI.

    Monday, April 25, 2016 2:33 PM
  • Thanks!

    If that's not a good way - how to do it better?: Reference different queries depending on a condition - use a lookup-table to fetch the query-name?


    Imke Feldmann TheBIccountant.com

    Monday, April 25, 2016 3:34 PM