Styjun

Have powerful mythological heroes ever run away or been deeply afraid?

Why is Colorado so different politically from nearby states?

Story about a toddler with god-like powers, dangerous tantrums

Is it possible to kill all life on Earth?

Is it legal in the UK for politicians to lie to the public for political gain?

Can you please explain this joke: "I'm going bananas is what I tell my bananas before I leave the house"?

How can Iron Man's suit withstand this?

If a problem only occurs randomly once in every N times on average, how many tests do I have to perform to be certain that it's now fixed?

If Sweden was to magically float away, at what altitude would it be visible from the southern hemisphere?

How should I push back against my job assigning "homework"?

Hygienic footwear for prehensile feet?

Is there a rule that prohibits us from using 2 possessives in a row?

Rotated Position of Integers

Is there any Biblical Basis for 400 years of silence between Old and New Testament?

Humans meet a distant alien species. How do they standardize? - Units of Measure

Asking bank to reduce APR instead of increasing credit limit

Working in the USA for living expenses only; allowed on VWP?

What is the correct expression of 10/20, 20/30, 30/40 etc?

Imperfective Aspect in German "not since" constructions

How can I offer a test ride while selling a bike?

How can a single Member of the House block a Congressional bill?

Get value of the passed argument to script importing variables from another script

What's the most polite way to tell a manager "shut up and let me work"?

How to provide realism without making readers think grimdark

Powerquery to SQL using Cell value as parameter very slow

Set a default parameter value for a JavaScript functionDoes Java support default parameter values?Excel 2003 send date parameters to SQL queryGet url parameter jquery Or How to Get Query String Values In jsSQL: Parameters from excel generate error: String data, right truncationExcel Input Parameters queryPowerQuery - Using a cell in a table as part of the code in a query (dynamically or not)Excel Query Parameter Update when Cell Value ChangesReplace SQL query parameter in a Excel power queryCell reference to a powerquery

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

scenario:

---

Running an XL sheet, using a single powerquery to retrieve data from a SQL server. The resulting dataset gets used in two subqueries. All three datasets are then loaded to pivot tables in the XL sheet.

objective:

---

Send query using 2 parameters retrieved from XL sheet, thereby reducing size of returned dataset. Folding query back onto SQL server, rather than filter post-retrieval in PQ.

description of problem:

---

if I use the below configuration for the main query:

let
 dbQuery = "SELECT * FROM dbo.somequery",
 Source = Sql.Database("<server>", "<database>", [Query=dbQuery])
in
 Source

This works fine and returns about 6500 rows almost instantly.

The following function is defined as 'GetRange' in PQ to retrieve a cell value:

(rangeName) => 
 Excel.CurrentWorkbook()[Name=rangeName][Content]0[Column1]

Two parameters get retrieved by using below syntax for each:

= GetRange("<named cell>")

If I now change the dbQuery to:

dbQuery = "SELECT * FROM dbo.xlPAS_PivotOutput 
 WHERE parameter1=" & Text.From(parameter1) & " and parameter2=" & Text.From(parameter2),

The query does compile, and results are returned, but Excel PQ takes about 1 to 2 minutes to actually return a value?
It appears that simply retrieving the 2 cell values and using them as input parameters takes a huge effort for some reason ?

excel parameters powerquery

share|improve this question

asked Mar 24 at 12:22

mtholenmtholen

506412

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you see the same slowdown if you put the parameters directly into your query? If you ignore privacy levels for your document (from Options | Current Workbook | Privacy), does the query speed up?
  
  – Alejandro Lopez-Lago - MSFT
  Apr 3 at 23:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @AlejandroLopez-Lago-MSFT, Many thanks for your reply. If I put the values directly in query no problem, quick response. Only when values get read from the sheet there is a problem. Privacy settings are set to ignore, no difference there...
  
  – mtholen
  Apr 5 at 15:27
  
  

  
  
  
  

add a comment | 

0

scenario:

---

Running an XL sheet, using a single powerquery to retrieve data from a SQL server. The resulting dataset gets used in two subqueries. All three datasets are then loaded to pivot tables in the XL sheet.

objective:

---

Send query using 2 parameters retrieved from XL sheet, thereby reducing size of returned dataset. Folding query back onto SQL server, rather than filter post-retrieval in PQ.

description of problem:

---

if I use the below configuration for the main query:

let
 dbQuery = "SELECT * FROM dbo.somequery",
 Source = Sql.Database("<server>", "<database>", [Query=dbQuery])
in
 Source

This works fine and returns about 6500 rows almost instantly.

The following function is defined as 'GetRange' in PQ to retrieve a cell value:

(rangeName) => 
 Excel.CurrentWorkbook()[Name=rangeName][Content]0[Column1]

Two parameters get retrieved by using below syntax for each:

= GetRange("<named cell>")

If I now change the dbQuery to:

dbQuery = "SELECT * FROM dbo.xlPAS_PivotOutput 
 WHERE parameter1=" & Text.From(parameter1) & " and parameter2=" & Text.From(parameter2),

The query does compile, and results are returned, but Excel PQ takes about 1 to 2 minutes to actually return a value?
It appears that simply retrieving the 2 cell values and using them as input parameters takes a huge effort for some reason ?

excel parameters powerquery

share|improve this question

asked Mar 24 at 12:22

mtholenmtholen

506412

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you see the same slowdown if you put the parameters directly into your query? If you ignore privacy levels for your document (from Options | Current Workbook | Privacy), does the query speed up?
  
  – Alejandro Lopez-Lago - MSFT
  Apr 3 at 23:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @AlejandroLopez-Lago-MSFT, Many thanks for your reply. If I put the values directly in query no problem, quick response. Only when values get read from the sheet there is a problem. Privacy settings are set to ignore, no difference there...
  
  – mtholen
  Apr 5 at 15:27
  
  

  
  
  
  

add a comment | 

scenario:

---

Running an XL sheet, using a single powerquery to retrieve data from a SQL server. The resulting dataset gets used in two subqueries. All three datasets are then loaded to pivot tables in the XL sheet.

objective:

---

Send query using 2 parameters retrieved from XL sheet, thereby reducing size of returned dataset. Folding query back onto SQL server, rather than filter post-retrieval in PQ.

description of problem:

---

if I use the below configuration for the main query:

let
 dbQuery = "SELECT * FROM dbo.somequery",
 Source = Sql.Database("<server>", "<database>", [Query=dbQuery])
in
 Source

This works fine and returns about 6500 rows almost instantly.

The following function is defined as 'GetRange' in PQ to retrieve a cell value:

(rangeName) => 
 Excel.CurrentWorkbook()[Name=rangeName][Content]0[Column1]

Two parameters get retrieved by using below syntax for each:

= GetRange("<named cell>")

If I now change the dbQuery to:

dbQuery = "SELECT * FROM dbo.xlPAS_PivotOutput 
 WHERE parameter1=" & Text.From(parameter1) & " and parameter2=" & Text.From(parameter2),

The query does compile, and results are returned, but Excel PQ takes about 1 to 2 minutes to actually return a value?
It appears that simply retrieving the 2 cell values and using them as input parameters takes a huge effort for some reason ?

excel parameters powerquery

share|improve this question

asked Mar 24 at 12:22

mtholenmtholen

506412

let
 dbQuery = "SELECT * FROM dbo.somequery",
 Source = Sql.Database("<server>", "<database>", [Query=dbQuery])
in
 Source

(rangeName) => 
 Excel.CurrentWorkbook()[Name=rangeName][Content]0[Column1]

= GetRange("<named cell>")

dbQuery = "SELECT * FROM dbo.xlPAS_PivotOutput 
 WHERE parameter1=" & Text.From(parameter1) & " and parameter2=" & Text.From(parameter2),

share|improve this question

asked Mar 24 at 12:22

mtholenmtholen

506412

share|improve this question

asked Mar 24 at 12:22

mtholenmtholen

506412

asked Mar 24 at 12:22

mtholenmtholen

506412

asked Mar 24 at 12:22

mtholenmtholen

506412