Forum Discussion
Get Table Object based on String
Dear community,
following topic.
Lets say I have 3 different tables (dummy data only, the original table have multiple columns and more rows):
Furthermore, I have a Parameter Table. It contains the list of table names as well as additional parameters. The idea is, that I use this table to call a function (see below) in a custom column. The function expects a table as well as addtional parameters which influence the transformation logic and the table output.
I go on with a very simple formula (without paremters) which only routes through the table without any transformation. But imageine that in the function there would be different steps applied based on the values from the parameter table.
Without any problems I can call the function with a table name (in this case table1) in a custom column like the following:
so this works as you can see in the following picture: I could call the function in a custom column and now I get Table1 in each row. .
But now comes the problem! As you might guess I now want to have in each row a different table based on the value of the first column (TableName). So I tried the following:
and I get an error:
I totally understand the error. The function expects a table and I give astring. This leads to an error. SO now is the question. How can I get a table object based on the table name as a string? Do you have a solution?
Highly appreciate your support.
Best regards
Michael
Hi Mikelytics ,
you could use the function Expression.Evaluate for it, but this won't work if you want to refresh the report in the PBI service.
Instead, I would recommend to append all 3 tables into 1 and create a "TableName" column to distinguish between them. Then you can use a Table.SelectRows command to filter the relevant table for each row, as this allows you to pass a text string.
7 Replies
Hi Mikelytics ,
you could use the function Expression.Evaluate for it, but this won't work if you want to refresh the report in the PBI service.
Instead, I would recommend to append all 3 tables into 1 and create a "TableName" column to distinguish between them. Then you can use a Table.SelectRows command to filter the relevant table for each row, as this allows you to pass a text string.Hi ImkeF,
Thank you for your help! Based on your suggestion I did some further digging. Instead of the Append I now directly create the Parameter table with the Table.FromRecords function. There I can put 1 column for the names and one column for the table object itself. By that everytime when a new table comes I only have to copy a row and set all the parameters.
TO give more details for others who have a similar problem I post the approach below.
now I can use the function:
Thank you again and I wish you a great week!
Best regards
Michael
Mikelytics You can use #sections for this, it wil return all the queries and then you can filter based on the name.
let Source = Table.FromRows ( Json.Document ( Binary.Decompress ( Binary.FromText ( "i45WCijKTylNLilWitWJVgpOzEmFsJxLi0vyc1OLgLxYAA==", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ( ( type nullable text ) meta [ Serialized.Text = true ] ) in type table [ #"Table Name" = _t ] ), ChangedType = Table.TransformColumnTypes ( Source, { { "Table Name", type text } } ), AddedCustom = Table.AddColumn ( ChangedType, "Custom", ( OT ) => try Table.SelectRows ( Record.ToTable ( #sections[Section1] ), ( IT ) => IT[Name] = OT[Table Name] )[Value]{0} otherwise null ) in AddedCustomHi AntrikshSharma ,
it's been a while since I last played with #section and #shared, but to my experience they will not work once you try to load in the datamodel or in the service.
So I am very interested to hear if your query will actually load into the datamodel without causing errors nowadays.ImkeF Yup, you are right it does throw an error. The only case it works for me is if I use it to get the list of Query Names.
= Record.FieldNames ( #sections[Section1] )Do you know why it generates error? Would have been good to use this freely.
Hi AntrikshSharma ,
Great Approach! BAsed on your input I did the following.
Creation of support table using your secrtion comment:
Left Join into my Parameter table based on table name:
Expansion of column and it worked so far!
Now I check whether it works in the service. so I did another expansion of the table to get the actual line-items which I can then load into the data model.
And now comes the weird Thing!
In the data model the items are not shown!
Did I do something wrong?
So I did not even come to the service since the values are not taken over into the data model in Desktop which is a really weird behavior since I could see the actual rows in Power Query. 😕
Or is there something I miss?
Best regards
Michael
Hi AntrikshSharma ,
here is why it won't work in the service: Restrictions when using Expression.Evaluate? (microsoft.com)
With regards to the load in Desktop itself: That's probably a circular dependency here: as #sections will also return the query in which it is called. Don't know why this even works in the editor, tbh.<
