Forum Discussion
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 table/column of table names:
I've tried Record.FromList and other combinations but am stuck.
Thank you,
Richard
3 Replies
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 ConvertToRecordRegards,
Owen
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
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.
