Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataHard
A data analyst is connecting Power BI to an on-premises SQL Server database to retrieve sales data. The analyst needs to ensure that only aggregated data is loaded into Power BI, preventing the transfer of sensitive row-level transactional details over the network, while still leveraging the SQL Server's processing power. Which Power Query concept is most relevant for achieving this goal?
- AM Language Custom Functions
- BQuery Folding
- CData Profiling
- DIncremental Refresh
Show answer & explanationAnswer & explanation
Correct answer: B. Query Folding
Query folding allows Power Query to translate transformations back into the source database's native query language (e.g., SQL). By performing aggregations early in the Power Query steps, folding ensures that only the aggregated, non-sensitive data is processed and returned by the SQL Server, minimizing network traffic and leveraging source system performance.
Why the other options are wrong
- A. M Language Custom Functions allow for reusable logic but do not inherently enable pushing operations back to the source database.
- C. Data Profiling helps understand data quality but does not control what data is loaded or processed at the source.
- D. Incremental Refresh manages loading new or updated data efficiently but doesn't prevent sensitive row-level data from being processed and potentially transferred initially if transformations aren't folded.
Query Folding (Power Query)
The ability of Power Query to translate transformation steps into the source database's native query language (e.g., SQL) and execute them on the source system, thereby reducing the amount of data transferred and leveraging source compute power.
- Optimizes performance for large datasets.
- Reduces network traffic by processing data at the source.
- Not all transformations can be folded; order of operations matters.
Memory trick: Fold the query, refresh in increments, cache your data, for best performance.