Power Query Parameters

D

dicken

Active Member
Joined
Feb 12, 2022
Messages
290
Office Version
  1. 365
Platform
  1. Windows
Hi,
if you use the option to use a query as the ' suggested value' what does it do,
I created a query = {"A","B","C", "D"} ,

and put this into the suggested value for new parameter and A; it does not give a list , B; it does not restrict the values I can enter manually to
one from that list,
so in the below I could enter any value I like or null. so what does it do? I

=
Excel Formula: 
Table.SelectRows(Custom1, each ([#"Ttable "] <> parameter))

Richard
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Try List.Contains function to check if a value exists in a list or not.

Sample (I assume your column name with space is correct):

Power Query: 
let
    Custom1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    parameter =  {"A","B","C", "D"},
    Result = Table.SelectRows(Custom1, each not List.Contains(parameter, [#"Ttable "]))
in
    Result
 
Upvote 0
smozgur said:
Try List.Contains function to check if a value exists in a list or not.

Sample (I assume your column name with space is correct):

Power Query: 
let
    Custom1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    parameter =  {"A","B","C", "D"},
    Result = Table.SelectRows(Custom1, each not List.Contains(parameter, [#"Ttable "]))
in
    Result
Click to expand...
Puzzled, what does that have to do with the parameter, I entered the list I created as the values of the parameter, but they are
not or don't seem to be, they neither limit the entries I can make nor create a list to pick from so what does it do?
 
Upvote 0
Please try posting some sample data (source and desired output - make sure it is not sensitive real data) and your entire query.
 
Upvote 0
smozgur said:
Please try posting some sample data (source and desired output - make sure it is not sensitive real data) and your entire query.
Click to expand...
How, I can't see where to attach a file, also it's a fairly straing forward question ;

A ; create a query = { 1,2,3} ,
B I create a table = #table( {"A"} , { {1}, {2}, {3}} )
use the first as the values of a new parameter,

then use that parameter in another ; P= parameter ;

Table.SelectRows( Source,(A)=> A [A] = P )

But I can make the values in P anything I like, it is not limited to the values 1,2,3 they do not create a list to choose from as using the list option would so
what does it do? if you don't know fine, but I can't keep asking the same question,

Richard.
 
Upvote 0
It's a parameter and works the same way as any other parameter.

dicken said:
they do not create a list to choose from as using the list option would
Click to expand...
Not sure what you mean by that exactly?
 
Upvote 0
RoryA said:
It's a parameter and works the same way as any other parameter.


Not sure what you mean by that exactly?
Click to expand...
Hi,
when you say it works as any parameter, I don't see that it does, if I create a list eg {1,2,3}, then use that as the value in new parameter
type any suggested value query; and the the name of the query {1,2,3} = Parameter ,

if i then use that eg ;
Excel Formula: 
 Table.SelectRows ( Source ,each [TableA] > Parameter1)

I can enter any value as current value or change it to anything in the parameter box, 1 , 100, "XXX", so what does entering a query as the parameter
achieve.
what is it supposed to do, what is the difference between using a query as suggested value and 'Any value'?


Richard
 
Upvote 0
The parameter is a list, so using > Parameter1 doesn't really make any sense. It would allow you to have a dynamic filter option for a query for example, where you could filter for In Parameter1.
 
Upvote 0
RoryA said:
The parameter is a list, so using > Parameter1 doesn't really make any sense. It would allow you to have a dynamic filter option for a query for example, where you could filter for In Parameter1.
Click to expand...
 
Upvote 0
Sorry for not responding sooner,
Well this is the question, why use a query as a parameter if it has no effect on that parameter, eg
if i have a table of rows a,, b, c , d , e and then create a list { a, b, c } and use the parameter option query,

Table.SelectRows( Source, each [Tabel] = Parameter1) , this works, but I can type in a , b but x , z or anything else I choose,
so why use a query in a parameter, if I use the list option in new parameter and again ; a, b c, they are values of that parameter,
which is what I would have expected from the first.
Richard
 
Upvote 0
You must log in or register to reply here.

Similar threads

jlhoover3
  • Solved
Power Query - Filter table column based on cell value
Replies
3
Views
201
alansidman
A
earthworm
  • Question
Exporting Data from Power Query into Multiple Sheet and merging steps into one.
Replies
3
Views
477
Matt Allington
M
D
  • Solved
Power Bi Parameters
Replies
4
Views
307
dicken
D
M
  • Question
Can you change a source in power query for excel?
Replies
1
Views
158
alansidman
A
A
  • Solved
Table data not extracted - Power query
Replies
5
Views
455
Ahmed Hashish
A

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top