Mquery list as parameter for SQL

[luzsoulez]

Hi,

I have the following code to make a list into a filter for SQL, but it is giving me firewall errors. The security settings are in "none" so I don't know what to do to solve.

let
// Step 1: Create the list of Corporation IDs
Mylist = Corporation_ID, // Assume Corporation_ID is a list defined elsewhere

// Step 2: Add single quotes around each item in the list
QuotedList = List.Transform(Mylist, each "'" & _ & "'"),

// Step 3: Combine the quoted list into a single text string separated by commas
CorporationID = Text.Combine(QuotedList, ","),

// Step 4: Define the SQL query with a placeholder for the parameter
SQLQuery = "
SELECT
TBD.[clientcorporationid] AS 'Client Corporation ID',
TBD.[activity_id] AS Activity ID',
TBD.[activity_dt] AS 'Activity Date',
TBD2.[Title] AS 'Title',
TBD2.[Joborderid] AS 'Order ID',
FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY [activity_id] ORDER BY [_most_recent_dttm] DESC) AS rnk
FROM [TBD].[XX_activity_TBD]) AS TBD
INNER JOIN
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY Joborderid ORDER BY[ _Most_Recent_Dttm DESC]) AS rnk
FROM (xxxxxxx_Nam.TBD2) AS TBD2
ON TBD.Joborderid = TBD2.Joborderid
WHERE
TBD.Rnk = 1
AND TBD2.Rnk = 1
AND TBD.[Clientcorporationid] IN (" & CorporationID & ")
",

// Step 5: Use Value.NativeQuery to execute the parameterized SQL query
Source = Value.NativeQuery(
Sql.Database("syn-XXXXX.sql.azuresynapse.net", "XXXXXbiteam"),
SQLQuery
)
in
Source

[lbendlin]

Assume Corporation_ID is a list defined elsewhere

That's your problem.

Power Query M Primer (Part 13): Tables—Table Think II | Ben Gribaudo

Either inline the query or completely separate it.