Forum Discussion
Evaluate my Data Model/Table Structure!
- 9 years ago
I’ve got response from the Product Team.
This is effectively "by design" and will not be changed.
Best Regards,
Herbert
Please check if the following code works in your scenario. If not, please post sample data of your table.
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", [name=name]),
year = _
in
Table.AddColumn(t, "FY", each year)
)
in
ret
in
export
// Table.Combine(addFY("Invoice_Summary FY to $", {2012,2013,2014,2015,2016,2017}))
Reference:
https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/
Regards,
Lydia
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...
- Anonymous9 years agoNot applicable
RobertSlattery,
Thanks for sharing the PBIX file. I can reproduce your issue, I will report this issue internally and post back once I get updates.
Regards,
Lydia- RobertSlattery9 years ago
Responsive Resident
- v-haibl-msft9 years ago
Microsoft Employee
I’ve got response from the Product Team.
This is effectively "by design" and will not be changed.
Best Regards,
Herbert