Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
IWallis
Helper I
Helper I

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 structureOld data structure

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

2021-12-03_8-46-41.jpg

 

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,

ErrorError

 

 

 

 

 

 

 

 

 

 

 

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

16 REPLIES 16
v-angzheng-msft
Community Support
Community Support

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
Super User
Super User

@IWallis  you missed a semicolon

 

;WITH CTE_MoSM (BreakOutID, M1, M2)

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

IWallis_0-1638899968205.png

 

@IWallis  can you please post the new query now

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

Hi, 

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

@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.

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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
Helper I
Helper I

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.

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:

AlexisOlson_0-1638895247626.png

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?

IWallis_1-1638900279372.png

 

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]

AlexisOlson_0-1638901387343.png

 

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.

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
Super User
Super User

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.