Trying to transform data into different data structure using direct query

Hi,
I have data that is stored in a SQL database that I would like to do some analysis in Power BI using direct query. But since the data structure in the database do not meet my need to present in Power BI, I need to change the formatting and pivot the table before I can do analysis.

Here is the old data structure,

Old data structure

and I would like to transform the data into this using direct query  

So, I wrote a query and tried in Azure to see if I can get the new data structure, and it works

Here is the query

----

WITH CTE_MoSM (BreakOutID, M1, M2)

AS

(Select *

FROM (

Select mosmMetric.MetricName AS MetricName, BreakoutID, Value AS MetricValue from mosmMetricValue

Left join mosmMetric ON mosmMetricValue.MetricID = mosmMetric.MetricID

Where Year = '2021') AS MoSM2021NP

PIVOT

(MAX(MetricValue) FOR MetricName IN ([Sourceable spend as a percent of revenue],

[Managed spend as a percent of sourceable spend])) AS MoSM2021P

)

SELECT BreakoutID, FORMAT(M1, 'P')AS 'Sourceable spend as a percent of revenue',FORMAT(M2, 'P')AS 'Managed spend as a percent of sourceable spend'   FROM CTE_MoSM

---

However, Power BI popped up an error message,

Error

it looks like I can't use CTE in the direct query of Power BI, does anyone have any solution for this? 

Thank you very much for your help!!