Get Table Object based on String

[Mikelytics]

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

[ImkeF]

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.<

[ImkeF]

Hi @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.

[Mikelytics]

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!

@AntrikshSharma 

@ImkeF 

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

[AntrikshSharma]

@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.

[AntrikshSharma]

@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
    AddedCustom

[ImkeF · Accepted solution]

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.

[Mikelytics]

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