Limiting data pulled with Power Query using a Parameter

Question

I have a Power Query which is pulling data from a very large table (over a million rows). When I try to add a parameter, no matter where in the query I put it, the query pulls the entire table, and then filters it down based on the parameter. It's taking a full minute to pull all the information, so my question is this:

Is there a way to structure the Power Query such that it only pulls the data I want, based on the parameter, and not the entire table?

Thanks! :)

The connection is using Excel's Get Data function, and the parameter, is being entered as part of a filter

Here's an example of the type of query where BegPer and EndPer are the beginning and ending parameters:

let
Source = Sql.Databases("TIRPROD-MIRROR"),
SLZZZAPP = Source{[Name="SLZZZAPP"]}[Data],
dbo_AcctHist = SLZZZAPP{[Schema="dbo",Item="AcctHist"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_AcctHist,{{"FiscYr", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [FiscYr] >= BegPer and [FiscYr] <= EndPer)

in #"Filtered Rows"

What kind of a connection are you using? How did you setup the parameter? Please show an example of what you are doing. With most database connectors PQ should be able to convert into SQL on the backend, so the only data it returns is the result of that query. If you're running on something through ODBC though, it cannot do this and your only option is to send a single initial custom SQL query as a string. — Wedge
– Wedge, Commented Dec 10, 2019 at 16:20
I edited my question to add some more information. I'm self-learning most of this, so it might look a little strange how I'm doing things. — Alec Zrike
– Alec Zrike, Commented Dec 10, 2019 at 18:19

Dreekun · Accepted Answer · 2019-12-11 10:08:18Z

1

Yes, you can connect to SQL Server, and then under "Advanced options" insert your SQL Statement. Then, create a parameter. After that, edit your query step, and in the SQL connection you'll see your query, that you can modify and insert the parameter you want.

See this example in Advanced Query Editor:

let
    Source = Sql.Database("my_server", "my_database", [Query="SELECT *  FROM [my_database]. 
       [dbo].[table1] Where column1 = '" & parameter & "'"])
in
    Source

The syntax is quite weird, so you have to try it out. See this for reference: https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716

https://channel9.msdn.com/Blogs/MVP-Azure/Pass-parameter-to-SQL-Queries-statement-using-Power-BI

answered Dec 11, 2019 at 10:08

Dreekun

4422 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wedge · Accepted Answer · 2019-12-11 16:34:20Z

OK, I tested some stuff out, and it looks like the issue is you didn't select an initial database. Seems like not selecting an initial database causes it to write the query in a way that native query folding does not support. If you tried to create the query again using the menu option but selected an initial database, the code it writes would change from this:

Source = Sql.Databases("TIRPROD-MIRROR"),
SLZZZAPP = Source{[Name="SLZZZAPP"]}[Data],
dbo_AcctHist = SLZZZAPP{[Schema="dbo",Item="AcctHist"]}[Data],

into this:

Source = Sql.Database("TIRPROD-MIRROR", "SLZZZAPP"),
dbo_AcctHist = Source{[Schema="dbo",Item="AcctHist"]}[Data],

(You can of course just manually edit the code in your existing query to match this). When it is setup like that, everything should be correctly converted to SQL on the back-end. You can check to see if this process is happening by right clicking the step in the "Applied Steps" list on the right and seeing if "Native Query" can be selected or is grayed out. If you can select it, it will show you the back-end SQL being run. If not, then that means all data at the last step it was working, will be fully downloaded to be processed locally.

Collectives™ on Stack Overflow

Limiting data pulled with Power Query using a Parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related