Is it possible to create a query from text?

[arpost]

I have a scenario where I need to generate a dynamic query and wondered if it is possible to do so by "creating" a query from text.

For example, if I had a text field that contained the following:

let
    Source = 1 = 1
in
    Source

could that then be executed as a query somehow?

[mahoneypat]

Looks like you are missing the let and in parts of your query inside your Expression.Evaluate.

Pat

[arpost]

That did the trick! It's a bummer the applied steps aren't listed using this method, but the method is great just the same. Appreciate it!

[mahoneypat · Accepted solution]

Yes.  You can use Expression.Evaluate("M expression") for that.

Expression.Evaluate - PowerQuery M | Microsoft Docs

Pat

[arpost]

Very cool! Didn't know about that function, @mahoneypat. Can this handle more complex queries? I keep getting an error when I try something like the following:

= Expression.Evaluate(    "Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(""i45WclTSUTI0MFCK1YlWcgKyjaBsZyDbGMSOBQA="", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Name"", type text}, {""Value"", Int64.Type}}),
    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each ([Name] = ""B""))""""")

Here's the original in the Advanced Editor:

and here's the original code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YlWcgKyjaBsZyDbGMSOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Name] = "B"))
in
    #"Filtered Rows"