Azure Data Studio accepts SQL statement but Power BI rejects and I don't know why

Hi Anonymous ,

Please try to update the query as below and check if it can load the data in Power BI successfully...

WITH EpicBenefitsData1 
AS (SELECT [Epic Benefits Field Name], [Epic Benefits Field Value], [FK Epic ID], 
Epics.[Custom Text 1] AS “Z Initiative ID”, 
Themes.[Theme Name] AS “Theme Title”,
Epics.[Epic Name] AS “Initiative Name”, 
Users.[Full Name] AS “Initiative Owner Name”,
Epics.[Epic Description] AS “Initiative Description”,
Epics.[Custom Dropdown 3] AS “Initiative Status”,
Epics.[Epic State] AS “Initiative State”, 
Epics.[Developmental Step] AS “Initiative Process Step”, 
Programs.[Program Name] AS “Primary Program”,
Portfolios.[Portfolio Name] AS “Portfolio”
FROM [current_dw].[Epic Benefits] AS EpicBenefits1 
INNER JOIN [current_dw].[Epic] AS Epics ON Epics.[Epic ID] = EpicBenefits1.[FK Epic ID]
INNER JOIN [current_dw].[User] AS Users ON Users.[User ID] = Epics.[FK UserOwner ID]
INNER JOIN [current_dw].[Program] AS Programs ON Epics.[FK Program ID] = Programs.[Program ID]
INNER JOIN [current_dw].[Portfolio] AS Portfolios ON Epics.[FK Portfolio ID] = Portfolios.[Portfolio ID]
INNER JOIN [current_dw].[Theme] AS Themes ON Themes.[Theme ID] = Epics.[FK Theme ID]
WHERE EpicBenefits1.[Epic Benefits Field Set Name] = 'Default'
UNION 
SELECT [Epic Benefits Field Name], [Epic Benefits Field Value], [FK Epic ID], 
Epics.[Custom Text 1], 
Themes.[Theme Name],
Epics.[Epic Name],
Users.[Full Name],
Epics.[Epic Description],
Epics.[Custom Dropdown 3],
Epics.[Epic State], 
Epics.[Developmental Step], 
Programs.[Program Name],
Portfolios.[Portfolio Name]
FROM [current_dw].[Epic Benefits] AS EpicBenefits2 
INNER JOIN [current_dw].[Epic] AS Epics ON Epics.[Epic ID] = EpicBenefits2.[FK Epic ID]
INNER JOIN [current_dw].[User] AS Users ON Users.[User ID] = Epics.[FK UserOwner ID]
INNER JOIN [current_dw].[Program] AS Programs ON Epics.[FK Program ID] = Programs.[Program ID]
INNER JOIN [current_dw].[Portfolio] AS Portfolios ON Epics.[FK Portfolio ID] = Portfolios.[Portfolio ID]
INNER JOIN [current_dw].[Theme] AS Themes ON Themes.[Theme ID] = Epics.[FK Theme ID]
WHERE EpicBenefits2.[Epic Benefits Field Set Name] = 'Z Lean Business Case')

SELECT 
[Z Initiative ID],
[FK Epic ID] AS “Initiative ID”,
[Theme Title],
[Initiative Name],
[Initiative Owner Name],
[Initiative Description],
[Initiative Status],
[Initiative State], 
[Initiative Process Step], 
[Primary Program],
[Portfolio],
[Analysis Summary], 
[Current Situation], 
[Funding Stage], 
[Future State & Desired Outcome], 
[In Scope], 
Notes, 
[Out of Scope],
[Recommendation & Desired Solution], 
[Sponsors], 
[Support Required & Products Affected]
FROM 
EpicBenefitsData1
PIVOT (MAX(EpicBenefitsData1.[Epic Benefits Field Value]) 
FOR EpicBenefitsData1.[Epic Benefits Field Name] IN ('Funding Stage', 'Future State & Desired Outcome', 'Current Situation', 
'Recommendation & Desired Solution', 'Support Required & Products Affected',
'Analysis Summary', 'Notes', 'Sponsors', 'In Scope','Out of Scope'))
AS EpicBenefitsData4

If the above one not working, you can consider to create a view in SQL to replace the above query and refer the view directly in Power BI.

SELECT * FROM VIEWNAME

Best Regards