Trying to transform data into different data structure using direct query

[IWallis]

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!!

[v-angzheng-msft]

Hi, @IWallis 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@IWallis  you missed a semicolon

;WITH CTE_MoSM (BreakOutID, M1, M2)

[IWallis]

Hi,

Thank you for replying! I tried to put a semicolon in there, and it does show the preview of the data, but in the end, it gives me this error message.

[smpa01]

@IWallis  can you please post the new query now

[IWallis]

;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

[smpa01]

@IWallis  I found the bug

Apprently, you need to put a single space between semicolon and With. SMMS is indifferent but PQ is not.

So not like

;WITH CTE1 as

but

; WITH CTE1 AS

I have successfully debugged two versions

; with cte1 as(
select top 1 [source_system_id] from
[schema].tbl)
select * from cte1

declare @t1 as table (source_system_id int)
; with cte1 as(
select top 1 [source_system_id] from
[schema].bl)
insert into @t1
select * from cte1

select * from @t1

So please give this a try

; 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 

[IWallis]

It is just very weird that I can preview the data and it was the correct ones but after I clicked load, it just popped the error message....

[IWallis]

Hi, 

Thanks for the query. I tried it, but it still pops up the same error message....

[smpa01]

@IWallis  Sorry tried to help you.

Don't know what is wrong. I was able reproduce the issue with

following

;WITH CTE1 AS

but it went away with

; WITH CTE1 AS

Try inserting the query result in a table variable like I demonstrated and see if it resolves.

[IWallis]

@smpa01 Thank you so much for your help on this, I will try that as well and let you know if it works! Thanks again!

[IWallis]

Yeah I was able to pivot and format the data in Power Query, if I used the import method to get the data instead of direct query. When I use direct query to pull out the data, power BI doesnt allow me to use power query to transform the data, or the changes wont be updated.

[AlexisOlson]

I've tested it and it's possible to pivot in the Query Editor without breaking DirectQuery.

It does require you to use an aggregation function rather than choosing Don't Aggregate though:

[IWallis]

I did try to use pivot and it will do it for me in power query but it said on the top that it won't apply, but I am wondering if it is because I am not authorized to write in the database?

[AlexisOlson]

That's odd. I've definitely done this with a table from a SQL database and it gives me a native query like this when I check out the code for the final step:

select [$Table].[NR],
    [$Table].[PE],
    [$Table].[RA],
    [$Table].[RE],
    [$Table].[VC]
from 
(
    select [_].[AssetClass],
        [_].[IRR]
    from [dbo].[DBTable] as [_]
) [$Pivot] pivot (max([IRR]) for [AssetClass] in ([NR], [PE], [RA], [RE], [VC])) as [$Table]

This should need any write permissions for the database (I don't have write permission on the DB I tested against.). I wonder if you're connecting to a database that Power Query doesn't know how to natively fold a pivot into the query.

[IWallis]

Yeah, that is very weird. I mean I can see the data being formatted in the way that I wanted in power query but it just wont apply the changes...

[AlexisOlson]

Have you tried pivoting in the Power Query?

https://docs.microsoft.com/en-us/power-query/pivot-columns

https://radacad.com/pivot-and-unpivot-with-power-bi