help with query Expression.Error

[fbastian]

Hello experts,
I am trying to create the following query which will (when working) query a list I created in power query.
List is below:

i get error below:

Expression.Error: We cannot convert the value "let
Top5FeederT..." to type List.
Details:
Value=let
Top5FeederTable = PowerBI.DataSources.Query("Top5Feeders2"),
Top5FeederList = Table.Column(Top5FeederTable, "FeederName")
in
Top5FeederList
Type=[Type]

when trying to create SQL query below:

let
Top5FeederList = Text.Combine(List.Transform(Top5FeederList, each "'" & _ & "'"), ","),
FeederNamesString = "('" & Text.Replace(Top5FeederList, ",", "','") & "')",
SQLQuery = "
WITH StartNodes AS (
SELECT
span.id,
span.hv_feeder_id,
h.asset_label AS feeder_name,
span.centreline.STStartPoint() AS start_geometry
FROM
[RDS_GSA_ASSET].[span_curr_VW] AS span
INNER JOIN [RDS_GSA_ASSET].[hv_feeder_curr_VW] h
ON span.hv_feeder_id = h.id
WHERE
span.function_ = 'HV'
AND h.asset_label IN " & FeederNamesString & "
),
EndNodes AS (
SELECT
span.id,
span.hv_feeder_id,
h.asset_label AS feeder_name,
span.centreline.STEndPoint() AS end_geometry
FROM
[RDS_GSA_ASSET].[span_curr_VW] AS span
INNER JOIN [RDS_GSA_ASSET].[hv_feeder_curr_VW] h
ON span.hv_feeder_id = h.id
WHERE
span.function_ = 'HV'
AND h.asset_label IN " & FeederNamesString & "
),
StartNodes_U AS (
SELECT
cable.id,
cable.hv_feeder_id,
h.asset_label AS feeder_name,
cable.centreline.STStartPoint() AS start_geometry
FROM
[RDS_GSA_ASSET].[cable_curr_VW] AS cable
INNER JOIN [RDS_GSA_ASSET].[hv_feeder_curr_VW] h
ON cable.hv_feeder_id = h.id
WHERE
cable.function_ = 'HV'
AND h.asset_label IN " & FeederNamesString & "
),
EndNodes_U AS (
SELECT
cable.id,
cable.hv_feeder_id,
h.asset_label AS feeder_name,
cable.centreline.STEndPoint() AS end_geometry
FROM
[RDS_GSA_ASSET].[cable_curr_VW] AS cable
INNER JOIN [RDS_GSA_ASSET].[hv_feeder_curr_VW] h
ON cable.hv_feeder_id = h.id
WHERE
cable.function_ = 'HV'
AND h.asset_label IN " & FeederNamesString & "
),
CombinedNodes AS (
SELECT
s.id,
s.hv_feeder_id,
s.feeder_name,
'span' AS object_type,
CONCAT(CAST(s.start_geometry.STX AS NVARCHAR(MAX)), ' ', CAST(s.start_geometry.STY AS NVARCHAR(MAX))) AS start_coordinates,
CONCAT(CAST(e.end_geometry.STX AS NVARCHAR(MAX)), ' ', CAST(e.end_geometry.STY AS NVARCHAR(MAX))) AS end_coordinates
FROM
StartNodes s
JOIN
EndNodes e ON s.id = e.id
UNION ALL
SELECT
su.id,
su.hv_feeder_id,
su.feeder_name,
'cable' AS object_type,
CONCAT(CAST(su.start_geometry.STX AS NVARCHAR(MAX)), ' ', CAST(su.start_geometry.STY AS NVARCHAR(MAX))) AS start_coordinates,
CONCAT(CAST(eu.end_geometry.STX AS NVARCHAR(MAX)), ' ', CAST(eu.end_geometry.STY AS NVARCHAR(MAX))) AS end_coordinates
FROM
StartNodes_U su
JOIN
EndNodes_U eu ON su.id = eu.id
)
SELECT
id,
object_type,
hv_feeder_id,
feeder_name,
CONCAT('LINESTRING (', start_coordinates, ', ', end_coordinates, ')') AS coordinates
FROM
CombinedNodes
",
Source = Sql.Database("spatial-datahub-aze-prd-sql.database.windows.net", "spatial_datahub", [Query = SQLQuery])
in
Source

Any idea of how to overcome this?

thank you.

[lbendlin]

What is "PowerBI.DataSources.Query" supposed to do?