Query string reference problem

[ronrsnfld]

Based on other topics, I can refer to queries using strings. And the query works as desired in the Power Query UI.

However, when I try to "Close and Apply", I receive an error message

If I run the same query in Microsoft Excel, there is no error on "Close and Load". The error only occurs in Power BI.

The use case is that I have a situation where there are multiple queries, but I only want to pick out those queries that end with a four digits representing a year in the range of this decade.

I do have other methods that I can use, but why doesn't this work? And why is the problem only with PBI and not with Excel?

Simplified code to show the problem, with hard-coded text strings rather than the more complex List.Select method that is unneccessary to demonstrate.

Assume the PQ has multiple queries

The following code runs to completion in PQ but causes the error message with Close and Apply

let
    queries=#sections[Section1],
    
    #"Selected Fields" = {"Table2022","Table2023","Table2024"},
    

    queryList = Record.FieldValues(Record.SelectFields(queries,#"Selected Fields")),
    combine = Table.Combine(queryList)
in
    combine

 If the queryList is replaced with the actual query names, eg:

queryList = {Table2022,Table2023,Table2024},

then the query runs and Close and Apply works without an error.

[BA_Pete · Accepted solution]

Hi @ronrsnfld ,

As far as I'm aware, the intrinsic variables (#sections, #shared) can only be used within PQ when using PBI Desktop and not passed to the model. My assumption is, because they are environmental keywords i.e. they give information about the current environment, and the data model can't give that environmental info, this is what breaks it.

Why it seems to work in PQ for Excel I have no idea.

You could possibly try @'ing Ehren into this thread if you need the technicals on it.

Pete

[BA_Pete · Accepted solution]

Hi @ronrsnfld ,

As far as I'm aware, the intrinsic variables (#sections, #shared) can only be used within PQ when using PBI Desktop and not passed to the model. My assumption is, because they are environmental keywords i.e. they give information about the current environment, and the data model can't give that environmental info, this is what breaks it.

Why it seems to work in PQ for Excel I have no idea.

You could possibly try @'ing Ehren into this thread if you need the technicals on it.

Pete

[Ehren · Accepted solution]

For perf reasons, in PBIDesktop refresh, we trim any queries that aren't being referenced (directly or indirectly) from the mashup document and replace them with a null value. References by name via #sections or #shared don't count, so they get turned into null.

[ronrsnfld]

Thanks. I guess that's only for PBI Desktop and not for Excel. I don't suppose there is any way to bypass that behavior for particular queries in PBI Desktop?

[Ehren]

No, I'm not aware of any way to bypass this behavior in PBIDesktop.