Power Query Parameter

[JeffreyPowell]

I am trying to limit the number of rows returned from my query,

I would like to use a parameter to do so but can't find a way to get it to prompt for the parameter value or even read one that I setup in power query before execution.

I can get it to prompt from MS Query but not from Power Query, it takes way to long to return 3,000,000 records to then filter them down to 1200.  I am also limited by having to stay on the 32 bit version of Excel and often run into space/size constraints

Any ideas would be appreciated.

Thanks

[JeffreyPowell]

Let me try to clarify:

I have a query in power query that I built in Advanced Option

Where table.B1 I want to be replaced by either a prompted parameter or read from this cell

This table has over 3 million rows and creates problems with our 32 bit version of Excel.

I get different errors but here is a common one

My users want to look at one job at a time

and so in the past they have brought in the entire table then filtered with a parameter setup in power query as a separate query and then referenced through a filter step in the data query 

So to limit amount of data returned I want to filter on Job before executing the query.

Inenter stead of this I want to use a parameter that the user can enter a Job and have it used before the data is fetched from the data query against the table.

I have tried creating the parameter named Job as a separate query and using the M Code filter format "Where [job]=job" It errors.  I Have tried preceding the parameter name with an @, a &, and multiple versions of single and double quotes.

I would be fine with setting up the parameter in Power Query as a parameter, or entering the value in a cell in the worksheet or prompting when refreshed for the parameter.  However I have not found a way to utilize the filter pre-fetching data.  To only bring back the single job that the user wants to look at.

I have been able to do it using Microsoft query but it limits the ability to manipulate the data for analysis after query execution by different users.

The idea of splitting the data into multiple pieces doesn't really solve my problem of maximum number of rows in a 32 bit version of Excel.  And besides I do not need them all.

[Anonymous]

Hi @JeffreyPowell ,

I can't understand exactly what you are talking about:" can't find a way to get it to prompt for the parameter value or even read one that I setup in power query before execution". Can you show me your parameter and related screenshots?
In a Power Query query, add a new step that uses a parameter to control the number of rows returned. For example, you can use the Table.FirstN function to return the first n rows of a table.
And to minimize the limitations of 32-bit Excel, consider the following:
Apply filters early in the Power Query step to reduce the amount of data processed.
For queries with intermediate steps, use Connect Only, that is, uncheck "Enable load".

If possible, use an incremental loading strategy to fetch only new or changed data.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JeffreyPowell]

Here is parameter in Power Query