Create table with dynamically fetched type data

[Anonymous]

Hello, I have a REST service that gives me data along with type information. Right now I am transforming the type information into a columnHeaders list of {name, type} pairs and creating a table using just #table(columnNames, rows) and then using Table.TransformColumnTypes(table, columnHeaders) to assign it the type data. It seems to be less efficient and more conversion error-prone than creating the table with the type info immediately.

Now, I know that I can supply a table type instead of the columnNames list, but I can't find a way to construct it dynamically without using Chris Webb's ugly hack of constructing a table type literal string and evaluating it.

Is there a better way? I even tried turning the columnHeaders into a record directly resembling the row-type part of the table type and doing type table myRecord, but that also fails with "cannot convert from type Record to type Type".

[ImkeF · Accepted solution]

Now I get it. 

I'm not aware of an alternative solution to it. 

[Anonymous · Accepted solution]

While researching something else I stumbled on a solution - one can create a closed record, easiest way being the Type.ForRecord function, and then do type table (myRecord), which actually works.

[ImkeF]

Hi @Anonymous 

could you please post the link to the "ugly hack" and briefly explain what you'd like to have removed to suit your requirements?

Thanks!

[Anonymous]

Hacky way:
https://blog.crossjoin.co.uk/2018/10/03/function-m-table-type/

Generating an expression via string concatenation and evaluating it from string is basically the definition of an ugly hack, even if done well. I don't mean to detriment Chris' expertise, I am just hoping that there is a power query functionality intended for such basic use case.

[ImkeF · Accepted solution]

Now I get it. 

I'm not aware of an alternative solution to it. 

[v-piga-msft]

Hi @Anonymous ,

I'm still a little confused about your scenario. 

If it is convenient could you share some screeshots and your desired output so that we could understand better.

Hope @ImkeF who is good at M query could have any ideas.

Best  Regards,

Cherry