Power Query: Make a Record containing Tables using M

[richardnlove918]

Hi all,

I am looking to create a record containing tables.  I can do this by hardcoding table names in the query ...

.... but I'm looking to do this using M providing as input a table/column of table names:

I've tried Record.FromList and other combinations but am stuck.

Thank you,

Richard

[v-piga-msft]

Hi @richardnlove918 ,

You could create a custom column with the formula below.

= Table.AddColumn(#"Changed Type", "Value", each Expression.Evaluate("Table1",#shared) )

Here is the output.

Best  Regards,

Cherry

[Anonymous]

@v-piga-msft  there is a mole in this solution, you cannot use this if the table name has spaces in it eg: Student Details. it shows error in that case.

how can we correct that.

[OwenAuger]

Hi @richardnlove918 

If you construct a table that has a "Name" column (containing desired field names) and a "Value" column (containing desired field values) you can then use Record.FromTable to convert the table to a record.

The below code should work assuming you have existing queries called table1, table2 & table3.

It first creates a table with a Name column containing the required table names, then uses Expression.Evaluate to add a column containing the table corresponding to the text value in the Name column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKklMykk1VIrVgTKNEExjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    AddValueColumn = Table.AddColumn(#"Changed Type", "Value", each Expression.Evaluate([Name], #shared), type table),
    ConvertToRecord = Record.FromTable(AddValueColumn)
in
    ConvertToRecord

Regards,

Owen