Forum Discussion

richardnlove918's avatar
richardnlove918
Frequent Visitor
7 years ago

Power Query: Make a Record containing Tables using M

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 tabl...
Need Help
OwenAuger's avatar
OwenAuger
Super User
7 years ago

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