cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Source Data

Hello

 

My work is using Power BI and Power Query daily. I have some beginner SQL skills in writting select statements, modeling my data but I am not a code writter. Bad at typing better at clicking. 

 

The question is it better to have my source data come in a SQL format for speed and effecentcy? My power BI files connect a lot of tables together and I get some reports with 15 plus different tables. 

 

If my data starts in an SQL format can I still use the Query Editor the same way I am using it or do I need to switch to writting more M code?

1 ACCEPTED SOLUTION

@NLZ  the below paragraph is taken from microsoft docs:

 

Sources that support folding

Most data sources that have the concept of a query language support query folding. These data sources can include relational databases, OData feeds (including SharePoint lists), Exchange, and Active Directory. However, data sources like flat files, blobs, and web typically do not.

 

Query folding will work in dataflowd as long as the dataflow is not accessed from within power bi desktop and if enhanced compute engine is enabled.

 

Just make sure whatever source you connect you dataflow to upon creation is of query language support. (Flat files will not work)

 

@johnlhaase If this helped you please mark my response as a solution

View solution in original post

3 REPLIES 3
Resolver IV
Resolver IV

@johnlhaase  I would not recommend specifying the query yourself,  doing so loses the performance advantage of query folding within the Mashup engine

 

If you want to improve performance then probably get more focused views from the sql side and connect to those views in PQ. 

 

You should watch the video on this web page to get a better understanding of how query folding works in Power Query

 

https://exceleratorbi.com.au/how-query-folding-works/

Regular Visitor

Hi @mpicca13 ,

 

your answer was what I was looking for. However, I am relatively new to Power BI and am having a similar question for our project. We want to connect multiple spreadsheets that are updated daily to weekly with around 100.000 rows. My plan was to put them on a sharepoint and then set up a dataflow to connect them. As far as I have understood, the data will then be stored in a Azure Data Lake. The only source, if a data flow is enabled for a query folding is a blog post. Some official posts from 2019 stated it was not enabled. Do you know if my plan would work out or not? How would you engage this problem?

 

Blogpost on the possibility to enable query folding:
https://ssbipolar.com/2019/10/12/power-bi-dataflows-and-query-folding/

 

Thanks for your help!

@NLZ  the below paragraph is taken from microsoft docs:

 

Sources that support folding

Most data sources that have the concept of a query language support query folding. These data sources can include relational databases, OData feeds (including SharePoint lists), Exchange, and Active Directory. However, data sources like flat files, blobs, and web typically do not.

 

Query folding will work in dataflowd as long as the dataflow is not accessed from within power bi desktop and if enhanced compute engine is enabled.

 

Just make sure whatever source you connect you dataflow to upon creation is of query language support. (Flat files will not work)

 

@johnlhaase If this helped you please mark my response as a solution

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors