cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rh203
Helper I
Helper I

Parameter with Query as Source

Hi,

 

im trying to create a basic parameter to feed into a filter for a table at refresh runtime in power query.

 

I have created a list from a table in excel and then created a parameter which uses this query as the source.

 

The list has a set of values that I would like the users to select from when the query is executed but I am completely confused as to how a parameter which uses a query as the source is expected to work.

 

It doesn't ask for any input from the user, nor does it show the options to select from in the parameters management screen.

 

am I missing something? I would have expected the whole point of a parameter with a query as the source would be to do this at runtime and not rely on a user to input the parameter manually in the 'current value' section.

 

not at my machine right now so can't post screenshots but it is as simple as the above...

 

thanks 

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

this

 

lol

 

FILTER('PFR Date','PFR Date'[PFR Week]=VALUE(" & Text.From(SELECTED_PFR_WEEK) & ")),"

View solution in original post

18 REPLIES 18
ToddChitt
Post Prodigy
Post Prodigy

If you have a query that you want to use to supply the validated list to a Parameter, then you need to convert the query to a list. There is a button in Power Query that will do that for you. After that, you can edit the parameter and select the Query as the source.

But even so, it does NOT give you that validated list in the Manage Parameters dialog box. That list of validated values only shows up after you do a Close and Apply, then go to Edit Paramters option under Transform Data on the Home menu.

 

Hope that helps

Thanks Todd,

 

I managed to get that far - I'm actually working in excel power query for this one so I'm not sure how an end user would change the parameter from the validated list within ther parameter from the excel interface?

 

Maybe its just a PBI thing?

 

Thanks

Jimmy801
Community Champion
Community Champion

Hello @rh203 

 

in Excel you have the chance to change this kind of parameters and then reload with the new setting. In Power BI there is no chance to use the input in your visuals in Power Query. In Excel you can do something like this

Jimmy801_0-1602221943372.png

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Value", type any}, {"Selection", type any}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each ([Selection] = "x"))[Value]{0}
in
    #"Gefilterte Zeilen"

Jimmy801_1-1602222098812.png

 

Load this table into Power Query and filter by Selection=x and pickup the value like YourTable[Value]{0} or you create a table with one cell and a dropdown and load it to power query. In this scenario you don't need any filters, just the reference as above mentioned.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi Jimmy,

 

Thanks for this - I had already managed to achieve this last night but this doesn't use parameters at all (only a named list)

 

it's fine - it does the job, but I'm still lost as to what the purpose of a parameter with a query as source is in excel... it never picks up the values and relies on the 'current value' field anyway...

 

Thanks for your help on this

 

Ryan

Jimmy801
Community Champion
Community Champion

Hello @rh203 

 

so, when it does the job, why still not satisfied? 😉

Parameters are useful when you transform a Query into a function. There you need a parameter. The advantage is that you use the query to change your function automatically. And when you are using a query to feed your parameter you can change the settings in your Excelsheet instead in the parameter-section

 

Hope this helps

 

Jimmy

Hi Jimmy,

 

I'm satisfied I have something which 'does the job' but I like to always find out the correct / best way of doing something which I might need in the future also.

 

In an ideal world I would like to pass this value into a DAX query which is being sent to the SSAS to collect the source data - I understand this may be possible using parameters but not so sure on just using a 'list'... any ideas?

 

thanks 

Jimmy801
Community Champion
Community Champion

Hello @rh203 

 

passing data to a DAX-query in Power Query has nothing to do if you are using parameters or not. I don't know how your DAX-query is looking like, but lets take an example that want to add a filter in DAX, that takes a text string like "[Project number].[M-121-12345],[Project number].[M-117.64455]" then you can use a list of Project number like {"M-121-12345", "M-117-64455"} transform it to the above mentioned format and use Text.Combine to get an text string, connecting both with a ",". This text string you can then input in your DAX-query

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Thanks Jimmy,

 

I actually only need to pass a single value (stored in a list) and don't need to do any combining at all.

 

is there a way to pass a single value (or a single value from a list) in the way you explained?

 

(I don't need to do any combination of text before sending the value into the DAX query)

 

thanks

ryan 

Jimmy801
Community Champion
Community Champion

Hello @rh203 

 

you can reference a list item like this {1,2,3,4}{0}. This code picks up the first item of your list.


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Thanks Jimmy,

 

Still a bit stuck..

 

I am using the below filter statement within a DAX query to send to the SSAS to retreive the data required but when I run it, I get the error message below also.

 

I'm using a single value (instead of a list) so not sure if I have used the correct DAX to drive this?

 

 

FILTER('PFR Date','PFR Date'[PFR Week]={SELECTED_PFR_WEEK}),

 

 

rh203_0-1602227160542.png

 

Jimmy801
Community Champion
Community Champion

Hello @rh203 

 

i don't know your variable SELECTED_PFR_WEEK if this is a text, list or a table. And what now is also not clear... do you want to make a DAX-statement in Power query or in Power BI?

 

supposing its in PowerQuery and your variable is a text

 

 

FILTER('PFR Date','PFR Date'[PFR Week]=" & SELECTED_PFR_WEEK & "),"

 

 

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

Hi Jimmy,

 

Everything in power query (excel to be precise).

 

The 'variable' (single value) is of type whole number.

 

I tried what you proposed and get the below messagw - think I understand the reason but not clear on how to pass the value into the query as a number instead?

 

Thanks

 

rh203_0-1602229841368.png

 

Jimmy801
Community Champion
Community Champion

Hello @rh203 

try this

FILTER('PFR Date','PFR Date'[PFR Week]=" & Number.From(SELECTED_PFR_WEEK) & "),"

Hi,

 

No sorry I get this message:

 

It looks like it ipicking up the correct value (202040) from the SELECTED_PFR_WEEK value but it is not happy with the '&'

 

 

rh203_0-1602233495505.png

 

Jimmy801
Community Champion
Community Champion

Hello @rh203 

 

last try

 

FILTER('PFR Date','PFR Date'[PFR Week]=VALUE(" & SELECTED_PFR_WEEK & ")),"


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi,

 

No, not happy 😞

 

rh203_0-1602236775303.png

 

Thanks

 

 

Jimmy801
Community Champion
Community Champion

this

 

lol

 

FILTER('PFR Date','PFR Date'[PFR Week]=VALUE(" & Text.From(SELECTED_PFR_WEEK) & ")),"

View solution in original post

Perfect!

 

Thanks for sticking with it 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors