cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
austinsense Impactful Individual
Impactful Individual

Re: Query with CTE gives error

That's a lot of data. For starters you could strip off most of the columns just to get the data loaded - Power BI has a very good compression engine depending on the data in your tables but no need to load everything right from the start.

 

You're right M2M relationships aren't possible directly - you'll need a bridge table (1:M,M;1) which is where the query editor comes in.  There are lots of good posts on here about writing calculations with M2M table relationships.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
michaeljc70 Advocate II
Advocate II

Re: Query with CTE gives error

Thanks for the information.  

 

In a lot of the data I'm dealing with, the record counts explode because of history. Typically, they would be working with the last available date.  They could also be using the last month end or comparing 2 months or weeks.  To cover all those scenarios with views would be difficult.   Stored procedures an end user can call with a date(s) might work.  Or an automated process to create a view per month might work.  But there are many other subject areas too that would need to be addressed. 

 

I think that the term "self-service" needs to be used more carefully.  

 

From a philosophical perspective , a large company uses a staff of accountants to do the accounting, not some guy in each area doing pieces of it part-time that he learned from a 2 day training class.  Most (large) companies have a legal team.  They don't expect each team or area to train someone with another job to learn to handle legal issues.  It seems that IT professionals are viewed differently and that their work (or some of it)  can be done efficeintly by anybody with a couple days training.  Obviously, at smaller companies things are different than at larger companies.  Personally, I think it is a lot more efficient having IT professionals write and test reports.  It is fine having people do ad-hoc things will carefully cultivated data too.  Ultimately, I do whatever my clients want. 

 

This current client had two business area people from finance create their own SQL Server "warehouse". They were self taught, which is not necessarily bad as I am mostly self-taught.  However, it wasn't properly tested or designed and much of the data was inconsistent and eventually it got thrown away and was dollars down the drain.  

 

 

 

 

rowlandshaw
Frequent Visitor

Re: Query with CTE gives error

The detail I'd add here, is that the query preview works, it's when evaluating the query that it falls over

Highlighted
rbbi Helper II
Helper II

Re: Query with CTE gives error

your issue and thoughts about it are exactly what I'm facing today. Would be nice if it jsut passed through the query and used the results like it does in the preview. My query also works fine in PowerPivot with a recursive CTE and I was just trying it in Power BI but the same code in Power BI isn't accepted. cheers

JimLuo
New Member

Re: Query with CTE gives error

I have the same issue with CTE. It will be much convenient to support CTE.   I use a workaround to save data into excel and import it.  

Mandr0id
New Member

Re: Query with CTE gives error

Same issue with CTE... Someone got it working?

geoffwill
Regular Visitor

Re: Query with CTE gives error

Having the same issue with CTEs, however was able to re-write to use a subquery and it works

Nobby
Frequent Visitor

Re: Query with CTE gives error

It's a shame. I also have this problem. I've removed the CTE but the code is much less readable/maintainable. Ho hum.

jimjao
New Member

Re: Query with CTE gives error

Hi all,

 

Using Power BI Desktop (October 2017), I was able to use CTE query along with an OLE DB connection to the SQL Server.

This is no longer truth.

 

Jim Jao

 

jimjao
New Member

Re: Query with CTE gives error

Hi all,

 

Using Power BI Desktop (October 2017), I was able to use CTE query with an OLE DB connection to the SQL Server.

So, Power BI does support CTE.

 

Jim Jao

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors