I recently discovered using the excellent #Shared as a source to pulling back all existing queries in the Power BI report and refer to them.
It works well within a simple query, as I can filter this table by the query title im looking for and access this data.
However, when I tweak this same simple #shared query so that it's in a function (with defined variables), I start to get issues. The function works fine in data preview when invoked, but that same data doesn't then show in the model and the function (and reference to #shared) is clearly the root cause.
To be more specific, I am trying to use a text string variable to dynamically reference one of several queries in Power BI. Crucially, it needs to be invokable via a custom function (i.e. the value will vary in the given column that is invoking the function). Given a parameter is fixed once set, this is not sufficient.
So I have a function that uses a text input variable to indicate the query that should be referred to:
let
#"Table"= (yearQuantity,percentileAmount,metricChoice) as table =>
let
Source = Table.SelectRows(Record.ToTable(#shared), each [Name] = "" & metricChoice & ""){0}[Value]
in
Source
in
#"Table"
AND IT WORKS FINE! (I've cut off the rest of the query ultimately)...
Let's say I invoke this with variables as needed, in the "Data Preview" window it works fine and returns the data I need.
But when I try to "Close and Apply" my changes, I get an error message loading the invoked function:
Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Table.. '
I also tried loading the #shared table in a query of its own, then refer to that query and manipulating it accordingly. Again this works fine in data preview; it just won't load the data to the model.
I hope my issue is clear as I would love some much-needed help!! Thank you
