#DataTransformation

Does Referencing Queries in Power Query Really Reduce Database Load? Power Query Editor needs no introduction for anyone who works with Excel’s Get Data experience, Power BI Desktop, Power BI and Fabric dataflows, or Power Platform dataflows. This same interface has even been introduced recently in Paginated Reports. Referencing Power Query queries is one of the most commonly used techniques in the Power Query Editor. Developers use it to reuse transformation logic, create staging queries, and build layered query structures that are easier to maintain. Many developers believe that when Query B references Query A, Power Query evaluates Query A first and then reuses its results, avoiding additional database queries. In this blog, I will test this assumption using a few simple scenarios and show how Power Query actually evaluates queries when one query depends on another. Scenario 1: Direct Query Referencing I created a query named dimaccount in Power Query that reads data from a Fabric Lakehouse using the SQL Analytics endpoint. I then created another query named dimaccountReference, which references the dimaccount query. dimaccount * let Source = Sql.Database(HostName, DatabaseName), dbo_dimaccount = Source{[Schema="dbo",Item="dimaccount"]}[Data] in dbo_dimaccount dimaccountReference * let Source = dimaccount in Source Query Dependency View After loading both queries into Power BI, I used the Query activity view to observe the database queries executed during refresh. Results Two queries hit the database: * Several metadata and preview queries are executed first. These queries validate object existence, schema access, and permissions. * The dimaccount table is then queried twice, once for dimaccount and once for dimaccountReference. Even though dimaccountReference references dimaccount, both queries independently execute against the data source. There is another common assumption that using the Table.Buffer function on the referenced query (dimaccount) will prevent the referencing query (dimaccountReference) from hitting the database again. This assumption is also incorrect. Buffered results are only reused within the scope of a single query evaluation and are not shared across different queries. In the scenario described above, buffering dimaccount can actually degrade performance, because the results would be loaded into memory twice, once for each query evaluation. More details on this behavior can be found here and here. Let’s explore few more scenarios Scenario 2: Appending Queries In this scenario, I created two queries, dimaccountAssets and dimaccountLiabilities. Both queries read from the same dimaccount table but apply different filters. dimaccountAssets * let Source = Sql.Database(HostName, DatabaseName), dbo_dimaccount = Source{[Schema = "dbo", Item = "dimaccount"]}[Data], #"Filtered Rows" = Table.SelectRows(dbo_dimaccount, each ([AccountType] = "Assets")) in #"Filtered Rows" dimaccountLiabilities * let Source = Sql.Database(HostName, DatabaseName), dbo_dimaccount = Source{[Schema = "dbo", Item = "dimaccount"]}[Data], #"Filtered Rows" = Table.SelectRows(dbo_dimaccount, each ([AccountType] = "Liabilities")) in #"Filtered Rows" I then created a third query that appends these two queries using Table.Combine Query Dependency View All three queries were loaded into Power BI without disabling any of them. Results Three queries hit the database: * dimaccountAssets queried the table once to retrieve Asset accounts. * dimaccountLiabilities queried the table once to retrieve Liability accounts. * dimaccountAppended executed a separate query that scanned the table twice and combined the results using UNION ALL. The generated SQL clearly shows two filtered scans of the same table, appended together. Once again, this indicates that the appended query does not reuse the results of the referenced queries. It independently evaluates the logic defined in those queries. In this case, the append operation successfully folded, which prevented an even worse outcome. If folding had failed, Power Query would have retrieved full datasets and performed the append locally. Scenario 3: Merging Queries In this scenario, I merged the dimaccount table with a factfinance table to bring an aggregated value into the dimension. This example is intentional. In a tabular model, this logic should be handled through relationships and measures, not in Power Query. The purpose here is to illustrate query evaluation behavior, not to recommend this pattern. factfinance * let Source = Sql.Database(HostName, DatabaseName), dbo_factfinance = Source{[Schema="dbo",Item="factfinance"]}[Data] in dbo_factfinance dimaccount (after merge) * let Source = Sql.Database(HostName, DatabaseName), dbo_dimaccount = Source{[Schema = "dbo", Item = "dimaccount"]}[Data], #"Merged Queries" = Table.NestedJoin( dbo_dimaccount, {"AccountKey"}, factfinance, {"AccountKey"}, "factfinance", JoinKind.LeftOuter ), #"Aggregated factFinance" = Table.AggregateTableColumn( #"Merged Queries", "factfinance", {{"Amount", List.Sum, "Sum of Amount"}} ) in #"Aggregated factFinance" Query Dependency View To measure query execution accurately, I disabled the standalone factfinance query and loaded only dimaccount. Results 100 queries hit the database: * dimaccount was queried once. * factfinance was queried 99 times. The reason is straightforward. The dimaccount table contains 99 distinct account keys, and for each key, Power Query executed a separate query against the factfinance table with a filter on that key. While Power Query avoided scanning the entire fact table for each query, this execution pattern is still highly inefficient and illustrates how query dependencies can explode database activity. You can easily handle this scenario by writing a SQL statement with a join condition and avoid hitting the db these many times, I would still want to suggest an option which I learned from Chris Webb’s blog post. Here it is Using Table.AddKey function, add account key column as primary key in dimaccount and then perform the merge operation between factfinance and dimaccount in a different table, in my case I created a table called dimaccountMerged, below is the M code dimaccount * let Source = Sql.Database(HostName, DatabaseName), dbo_dimaccount = Source{[Schema="dbo",Item="dimaccount"]}[Data], Custom1 = Table.AddKey(dbo_dimaccount, {"AccountKey"}, true) in Custom1 dimaccountMerged * let #"Merged Queries" = Table.NestedJoin( dimaccount, {"AccountKey"}, factFinance, {"AccountKey"}, "factfinance", JoinKind.LeftOuter ), #"Aggregated factfinance" = Table.AggregateTableColumn( #"Merged Queries", "factfinance", {{"Amount", List.Sum, "Sum of Amount"}} ) in #"Aggregated factFinance" Query Dependency View This has reduced the number of queries from 100 to 5. One query to read the data from factfinance table The other 4 queries are to perform the complete join operation, Power Query grouped 99 AccountKeys into multiple batches (25 accountKeys per batch) and executed one query per batch. As Chris Webb mentioned in his article, this has significantly reduced the number of queries and can improve the performance. Key Observation based on 3 scenarios The key takeaway here is this: Each query is evaluated independently and referencing one query in another reuses transformation logic, not query results. Queries run in parallel, maximum number of queries that run in parallel depends on maximum number of concurrent jobs settings in Power BI Desktop. If query referencing does not reduce database calls, how can we do that effectively? Using Power BI Dataflows You can create a dataflow and move all source queries other queries that contain repetitive transformation logic into it, such as dimaccount and factfinance in the scenarios discussed above. In your Power BI semantic model, connect to the dataflow and consume the data from there. With this approach, even if multiple queries reference the same data in the semantic model, the requests are handled by the dataflow rather than being sent to the underlying data source. While dataflows introduces one more artifact into the solution and increases the maintenance efforts, using it is one of the most effective way to reduce number of queries that hit the db. Additional Optimization Recommendations If dataflows are not an option, the following practices can still help reduce database load and refresh time to some extent: * Prefer native SQL queries for complex transformations. This reduces dependency on query folding and avoids inefficient execution patterns. In the merge scenario above, a single SQL query could have replaced dozens of individual queries. * Uncheck “Enable load to report” for staging queries and any queries that are not directly consumed by the report. * For truly static tables whose data never changes, Uncheck the “Include in report refresh” option. This avoids unnecessary database reads while still allowing the table to be used in the model. In this blog, I shared my observations on how Power Query evaluates queries when using a Fabric Lakehouse SQL Analytics endpoint as the data source. Based on my experience, the behavior is largely consistent across other SQL Server–based sources, though it may vary with other sources like: Databricks, Snowflake and Redshift etc. Credits: Some of the topics discussed in this blog are inspired by Microsoft documentation and the excellent articles written by Chris Webb. I hope this blog helped clarify some common assumptions and provided a few useful insights. Feel free to share your thoughts, observations, or feedback in the comments section. Happy learning!!!

1 month ago

Does Referencing Queries in Power Query Really Reduce Database Load?: Power Query Editor needs no introduction for anyone who works with Excel’s Get Data experience, Power BI Desktop, Power BI and Fabric dataflows, or Power… @PowerBI #PowerQuery #PowerBI #DataAnalytics #Excel #DataTransformation

1 0 0 0

Power Query vs DAX: Where Should the Logic Live? Struggling to decide whether a transformation belongs in Power Query or DAX? In this practical guide, we break down when and why to use each tool, with clear decision rules, real scenarios, performance considerations, and best practices every Power BI author should know.

1 month ago

Power Query vs DAX: Where Should the Logic Live?: Struggling to decide whether a transformation belongs in Power Query or DAX? In this practical guide, we break down when and why to use each tool, with clear decision… @PowerBI #PowerQuery #DAX #PowerBI #DataTransformation #BusinessIntelligence

1 0 0 0

Ken Puls, FCPA, MS MVP

@excelguru.ca

1 month ago

The real teaching moment went beyond the mechanics of #DataTransformation. Sometimes It's best to separate context from detail, transform each piece correctly, and then recombine them once the logic is sound. Trying to force everything in one step often creates silent errors that only show up later.

0 0 1 0

#analytics #automation #ai #llm #taxonomy #spreadsheets #ai #llm #artificialintelligence #dataextraction #dataclassification #datacategorization #spreadsheetai #aiformula #dataanalytics… | Matasoft - ... The underrated business use case for artificial intelligence: AI inside formulas means you can fill down like any spreadsheet—no separate scripts, no one-off notebooks, no brittle glue. It’s still a s...

2 months ago

Data extraction, data categorization, attributes extraction , data mapping - all can be automated by AI spreadsheet formula magic of (Un)Perplexed Spready:
www.linkedin.com/posts/mataso...
#AI #LLM #DataTransformation #TaxonomyClassification #Taxonomy #ProductTaxonomy #FutureOfWork #AIFormulas

0 0 0 0

An example of using (Un)Perplexed Spready in a real life use case - Furniture catalog taxonomy classification AI-driven spreadsheet software (Un)Perplexed Spready automates complex data tasks by integrating advanced AI models directly into spreadsheets. This article demonstrates how (Un)Perplexed Spready, combined with SearXNG, Matasoft Web Search tool and Ollama Cloud provided GLM-4.7 LLM model, can perform advanced AI-driven mapping and categorization of products to appropriate hierarchical product taxonomy.

2 months ago

The products list taxonomy categorization project that will prove it to you that AI belongs in every spreadsheet. Final word: matasoft.hr/QTrendContro...

#AIRevolution #ToolStack #AI #LLM #DataTransformation #TaxonomyClassification #Taxonomy #ProductTaxonomy #FutureOfWork #AIFormulas

0 0 0 0

An example of using (Un)Perplexed Spready in a real life use case - Furniture catalog taxonomy classification AI-driven spreadsheet software (Un)Perplexed Spready automates complex data tasks by integrating advanced AI models directly into spreadsheets. This article demonstrates how (Un)Perplexed Spready, combined with SearXNG, Matasoft Web Search tool and Ollama Cloud provided GLM-4.7 LLM model, can perform advanced AI-driven mapping and categorization of products to appropriate hierarchical product taxonomy.

2 months ago

The case study that's causing data teams to re-evaluate their entire tool stack. Join them: matasoft.hr/QTrendContro...

#ToolStack #AI #LLM #DataTransformation #TaxonomyClassification #Taxonomy #ProductTaxonomy #FutureOfWork #AIFormulas #UnPerplexedSpready #WebResearch #SpreadsheetAI #DataAnalysis

0 0 0 0

An example of using (Un)Perplexed Spready in a real life use case - Furniture catalog taxonomy classification AI-driven spreadsheet software (Un)Perplexed Spready automates complex data tasks by integrating advanced AI models directly into spreadsheets. This article demonstrates how (Un)Perplexed Spready, combined with SearXNG, Matasoft Web Search tool and Ollama Cloud provided GLM-4.7 LLM model, can perform advanced AI-driven mapping and categorization of products to appropriate hierarchical product taxonomy.

2 months ago

From "that's impossible" to "that's done" in one meeting. Data extraction, data categorization, attributes extraction , data mapping - all can be automated by AI spreadsheet magic of (Un)Perplexed Spready: matasoft.hr/QTrendContro...

#AI #LLM #DataTransformation

0 0 0 0

An example of using (Un)Perplexed Spready in a real life use case - Furniture catalog taxonomy classification AI-driven spreadsheet software (Un)Perplexed Spready automates complex data tasks by integrating advanced AI models directly into spreadsheets. This article demonstrates how (Un)Perplexed Spready, com...

2 months ago

The case study that makes you question every manual data process in your organization. Why would you spend countless days to do repetitive and tedious manual work in spreadsheets, when AI can do it instead?
matasoft.hr/QTrendContro...
#AI #LLM #DataTransformation #TaxonomyClassification #Taxonomy

0 0 0 0

An example of using (Un)Perplexed Spready in a real life use case - Furniture catalog taxonomy classification AI-driven spreadsheet software (Un)Perplexed Spready automates complex data tasks by integrating advanced AI models directly into spreadsheets. This article demonstrates how (Un)Perplexed Spready, com...

2 months ago

The taxonomy project you keep pushing to next quarter? AI can do it this afternoon, in (Un)Perplexed Spready. Schedule it: matasoft.hr/QTrendContro...

#AI #LLM #DataTransformation #TaxonomyClassification #Taxonomy #ProductTaxonomy #FutureOfWork #AIFormulas #UnPerplexedSpready #WebResearch #BI

0 0 0 0

An example of using (Un)Perplexed Spready in a real life use case - Furniture catalog taxonomy classification AI-driven spreadsheet software (Un)Perplexed Spready automates complex data tasks by integrating advanced AI models directly into spreadsheets. This article demonstrates how (Un)Perplexed Spready, combined with SearXNG, Matasoft Web Search tool and Ollama Cloud provided GLM-4.7 LLM model, can perform advanced AI-driven mapping and categorization of products to appropriate hierarchical product taxonomy.

2 months ago

From data chaos to taxonomy clarity. Before/after in one spreadsheet: matasoft.hr/QTrendContro... #BeforeAfter #DataTransformation #FutureOfWork #AIFormulas #UnPerplexedSpready #AI #LLM #WebResearch #SpreadsheetAI #DataAnalysis #DataCategorization #DataExtraction #DataClassification #DataAnalysis

0 0 0 0

The ConTech Crew

@thecontechcrew.bsky.social

2 months ago

Digital transformation means nothing without clean data.

🎧 Listen to the full episode https://youtu.be/Mkm_Sf_-sJQ

#cleanconstructiondata #AIinconstruction #datatransformation #constructiontech #contechcrew #contractorinsights #jobsiteintelligence #digitalsystems #fielddata

0 0 0 0

Skillwave Training

@skillwave.training

2 months ago

But the real teaching moment went beyond #DataTransformation mechanics. Sometimes the smartest move is to separate context from detail, transform each piece correctly, and then recombine them once the logic is sound. Trying to force everything in 1 step often creates errors that only show up later.😖

0 0 1 0

OriginLab

@originlab.bsky.social

2 months ago

Statistical Process Control: Report-style Output for Capability Analysis Tools YouTube video by OriginLab Corp.

The Capability Analysis tools in OriginPro2026’s SPC app now include report-style output for easier cross checking. #SPC #CapabilityAnalysis #NormalCapabilityAnalysis #NonnormalCapabilityAnalysis #DataTransformation #originpro2026 #OriginPro #originlab #statistics #DataAnalysis #DataVisualization

0 0 0 0

Power Query Functions Explained: From Simple Cleanup to Dynamic Transformations Power Query functions are the backbone of efficient and reusable transformations in Power BI. In this hands-on post, we’ll explore three practical examples, from simple text cleanup to a dynamic pivot and automatic INR currency conversion.

2 months ago

Power Query Functions Explained: From Simple Cleanup to Dynamic Transformations: Power Query functions are the backbone of efficient and reusable transformations in Power BI.

In this hands-on post, we’ll explore… @PowerBI #PowerQuery #PowerBI #DataTransformation #ExcelFunctions #DataAnalytics

1 0 0 0