Dynamic M Query doesn't take value from slicer as parameter

Hi lenfloge,

I hope you are doing well today 🙂

The problem is likely in how you're handling the parameter binding and the M query syntax

So you are issuing 3 Problem as i can see...

First Problem :

• Your current query is vulnerable to SQL injection and has string formatting issues:

"select id, identifier from energiegemeinschaft.energiegemeinschaft where id = '" & parameterEgmId & "'"

•  You can Fix it by this:

let
    Source = Value.NativeQuery(
        PostgreSQL.Database("XXXXXXXX", "backend"),
        "select id, identifier from energiegemeinschaft.energiegemeinschaft where id = @id",
        [id = parameterEgmId],
        [EnableFolding=true]
    )
in
    Source

Second Problem :  

• Make sure your parameter parameterEgmId is set to Text type
• The Suggested Values should be Any value (not "List of values")

• Your filter table should output simple text values, not complex records:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcuxEcAgCEDRXajDHUREmCVngaL7jxDT5he/e88D00bjtEBRqWfE6FscLVNtNaXhDS5gasqE9MXQrwNz6txJaHddKHsFju0Fw1dIqSVG9R+8ofcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [egmId = _t, Identifier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"egmId", type text}, {"Identifier", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type", {"egmId"})
in
    #"Removed Other Columns"

• Reconfigure the Binding:
  • Go to your filter table query
  • In Bind to parameter, select your parameterEgmId
  • Set Multi select to No (since your query expects a single value)

  • Set Select all to No

Another Approach For this Problem you Can just instead of table you could output a list from your filter query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcuxEcAgCEDRXajDHUREmCVngaL7jxDT5he/e88D00bjtEBRqWfE6FscLVNtNaXhDS5gasqE9MXQrwNz6txJaHddKHsFju0Fw1dIqSVG9R+8ofcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [egmId = _t, Identifier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"egmId", type text}, {"Identifier", type text}}),
    egmIdList = Table.ToList(Table.SelectColumns(#"Changed Type", {"egmId"}))
in
    egmIdList

•  Then bind this list output to your parameter

Finally make sure your main query depends on the parameter:

• The parameter should be referenced directly in your main query

• The filter table should be evaluated before the main query