I'm building a report in PowerBI paginated reports, fed by a dataset that that is tied to a SQL stored procedure. Everything was working fine, until I modified the SP to include a UNION to another table. The query runs fine in SSMS and I get the intended results, however when I try to validate the procedure in Report Builder I get the error:
"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.
An item with the same key has already been added."
I've tried creating a new blank report, readding my Datasource and trying to add the dataset again with the same results. I've looked over the SP and I cannot for the life of me figure out why it would be throwing that error, however all of the obscure posts & whatnot I've found in my research seem to suggest this happens on a table join/merge, so more than likely it's something with how PowerBI is handling the UNION portion of my query. Any thoughts? Im attaching a slightly scrubbed version of my SP query below:
SELECT
E.ID,
E.EstimateNumber,
E.divisionNumber,
E.deptNumber,
E.JobName,
E.JobLocationCity,
E.JobLocationState,
E.EstimateDateDue,
E.EstimateTimeDue,
E.ExtendedDate,
E.EstimateNotes,
E.OutstandingDate,
E.GCorCMorOwner,
E.NameOfGCorCMorO,
IIF(E.EstimateDateDue<E.ExtendedDate,E.ExtendedDate,E.EstimateDateDue) AS DateDue,
IIF(LEN(DATEPART(HH,EstimateTimeDue))<2,CONCAT('0',STR(DATEPART(HH,EstimateTimeDue))),STR(DATEPART(HH,EstimateTimeDue))) AS HourDue,
DATEPART(N,EstimateTimeDue) AS MinuteDue,
E.JobWorkType,
E.ServiceCommercial,
E.BidBond,
E.SiteVisit,
E.WageType,
CONCAT(TRIM(Estimators.EstimatorFirstName),' ', LEFT(Estimators.EstimatorLastName,1), '.') AS EstimatorNm,
E.BudgetEstimate,
STUFF((SELECT ', ' + RS.[SystemType]
FROM [dbo].[EstRoofSystems] RS
WHERE RS.[EstimateID] = E.[ID]
FOR XML PATH('')), 1, 2, '') AS SystemTypes,
STUFF((SELECT ', ' + CAST(RS.[Price] AS VARCHAR)
FROM [dbo].[EstRoofSystems] RS
WHERE RS.[EstimateID] = E.[ID]
FOR XML PATH('')), 1, 2, '') AS Prices,
STUFF((SELECT ', ' + R.[RoofSystemDesc]
FROM [dbo].[EstRoofSystems] RS
INNER JOIN [dbo].[RoofSystems] R ON RS.[RoofSysID] = R.[ID]
WHERE RS.[EstimateID] = E.[ID]
FOR XML PATH('')), 1, 2, '') AS RoofSystems,
NULL AS EndDate, --Adding column to accommodate any scheduled event's ending dates from the SchedEvents table.
E.EstimateNotes,
NULL AS eventType, --eventType
NULL AS who --who
FROM
Estimates E
LEFT JOIN Estimators ON Estimators.EstimatorID = E.EstimatorID
WHERE
(LEFT(E.EstimateNumber,1)='E') AND E.RemoveFromSchedule = 0 AND ((((E.WonLost) Not In ('W','L'))) OR (((E.WonLost) Is Null)) OR (((E.WonLost) In ('W','L')) AND ((E.KeepOnEstSchedule)=1)))
UNION ALL
-- Selecting all records from SchedEvents table
SELECT
S.ID,
S.estimateNum,
S.divisionNumber,
S.deptumber,
S.eventType, -- JobName
NULL, -- JobLocationCity
NULL, -- JobLocationState
S.startDate, -- EstimateDateDue
S.eventTime, -- EstimateTimeDue
NULL, -- ExtendedDate
NULL, -- EstimateNotes
NULL, -- OutstandingDate
NULL, -- GCorCMorOwner
NULL, -- NameOfGCorCMorO
S.startDate, -- DateDue
NULL, -- HourDue
NULL, -- MinuteDue
NULL, -- JobWorkType
NULL, -- ServiceCommercial
NULL, -- BidBond
NULL, -- SiteVisit
NULL, -- WageType
NULL, -- EstimatorNm
NULL, -- BudgetEstimate
NULL, -- SystemTypes
NULL, -- Prices
NULL, -- RoofSystems
CASE
WHEN S.startDate = S.endDate THEN NULL
ELSE S.endDate
END AS EndingDate,
NULL, -- EstimateNotes
S.eventType,
S.who
FROM
SchedEvents S;
Thanks in advance.
