Source Data

[johnlhaase]

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?

[mpicca13]

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

[Anonymous]

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!

[mpicca13 · Accepted solution]

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