Forum Discussion
Evaluate my Data Model/Table Structure!
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
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!