CODEWITHSUNDEEP
parameterspowerquery
Alec Zrike
Alec Zrike

Reputation: 47

Limiting data pulled with Power Query using a Parameter

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"

Upvotes: 0

Views: 3638

Answers (2)

Wedge
Wedge

Reputation: 1826

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.

Upvotes: 0

Dreekun
Dreekun

Reputation: 442

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

Upvotes: 1

Related Questions