Evaluate my Data Model/Table Structure!

Hi Lydia, thanks, but unfortunately, your solution doesn't work.  If I do that it evaluates name as a text string and fails when it feeds that value into the first argument of Table.AddColumn, viz...

An error occurred in the ‘addFY’ query. Expression.Error: We cannot convert the value "#"Invoice_Summary FY..." to type Table.

I have posted a minimum example here.

There are two example spreadsheets included with source tables.  You just need to edit the two Invoice_Summary FY to 20XX queries to wire them up to wherever you put the spreadsheets.  The result when I run that example is the same as in my app: the query runs fine in the query editor but throws in the visual editor.  Even if I have no visual and no relationships.

If there is another way to weak-reference to a table from a text string that will also solve my problem!

Hi Lydia, were you able to use the minimum example I posted and repro the problem?

Because it works in the query editor preview (i.e. the query runs correctly) and not in the visual it seems like this is a bug perhaps?

Anonymous

Still hoping for an answer on this so I will try to explain better...

I have a function that uses a Test template and a List of Text as input to allow it to do a similar transformation on a subset of a class of similarly named queries. The Template is a generic name for a class of objects including a $ as a place holder for the unique part of the name, for example, #"Invoice_Summary FY to $". The List is the unique part of the query names, in my example, a year viz: {2016, 2017}

The function then uses the List and the name template in a List.Transform operation to select the required years from the class of similarly named queries. It does this by including a transform function that constructs the name of each object from the template and the list element and passes this as a text object to Expression.Evaluate along with the global, #shared object.

The Expression.Evaluate method uses the text to look up the queries in the global #shared object.

I am aware of Chris Web's blog about Evaluate

and I'm aware that he mentions a caveat for objects that are created in the local context but this is not the case in my function as I am only constructing a weak reference to these already existing global queries. So this should work. But, it doesn't.

The function is…

let
     export = (baseName as text, fy as list) =>
        let
            ret = List.Transform(fy, each
                let
                    name = "#" & """" & Text.Replace(baseName, "$", Text.From(_)) & """",
                    t = Expression.Evaluate(name, #shared),
                    year = _
                in
                    Table.AddColumn(t, "FY", each year)
            )
        in
            ret
in
    export

The call signature is like…

Table.Combine(addFY("Invoice_Summary FY to $", {2016,2017}))

And this works fine in the Query Edit view of Power BI but, it fails when I update changes in the visual.

This seems like a bug, because I can scroll to the end of the combined table in the query editor so I know it works in the Query Editor. But, when the same code is executed in the visual it doesn't work.

I posted this question here and got one reply from a Micrsoft person (thank you @v-yuezhe-msft) but their suggestion did not work and I had no followup from them yet.  So, I'm re-posting here with better description I hope.

I also created a minimum working example to repro the problem and posted it here.  The Power BI file there can be downloaded and it will automaticaly connect to the oyther elements in the same one-drive folder and will throw the following error...