Hi Everyone,
I’m trying to create a query-based parameter and I’m having trouble getting it to work. The parameter table is contained in the main data spreadsheet and is true/false in a dropdown, currently set to “true”:
I have uploaded this table as a new query, ParamTest, and converted it to a list, which also shows “True.”
Here is the parameter I set up, pointing to the above-mentioned list:
It won’t let me save it without putting something in the “Current Value” field, so I put “True.”
When I go back and change the parameter in the underlying table to “False,” the list updates:
But the parameter does not:
It seems that even though the parameter is pointed to a query, it will only accept what is hard-keyed in there. Can anyone tell me what I’m doing wrong?
Hi @Melissa,
I tried that and while it doesn’t force me to type something in the “Current Value” box, it still isn’t picking up what’s in the query either…it just leaves it blank.
Thanks,
LRSimmons
@LRSimmons I hope @Melissa doensnt mind if I chime in, but I have come across this myself.
I think you are confusing setting up a parameter table in excel vs. setting up parameters in power query. I would assume you would want the T/F dropdown to be dynamic. I’d add the Parameter Table to Power Query and drill down to the value you need. In this case it will be whatever is in cell “B3” but that would only work if you know this will be the only value in the parameter table. If there will be other values, you would need to filter that table down to ensure it is just the value you need. But assuming it will just be that one value, you can just drill down to the T/F value .
Then you can use that value to filter a table. Created a dummy table that had a True/False column
You can load that table to excel and it will be dynamic based on the drop down of T/F in the original Parameter table. Then you after the the T/F dropdown is changed you can refresh the table and it will be updated. Would probably want to use some sort of change event in VBA to refresh the table once the dropdown is changed, but I’ll leave that up to you Excel Dynamic Parameter.xlsx (72.9 KB)
@Melissa and @Nick_M,
Thanks for your responses.
I use the same code in PowerBI to process data for several different clients. Each client’s data is in a separate Excel sheet and I use another parameter to “point” the processing at the appropriate data. That true/false parameter will vary by client, and I don’t want to have to manually adjust it in PowerBI each time, so I need to keep it at the Excel sheet level. In other words, the “true/false” needs to flow from Excel to PowerBI, not the other way around. Does that make sense?
Let me see if I can give a bit more background. When I receive data from each client, there is a column in the original Excel sheet containing names. Sometimes they are formatted as “FirstName LastName” and other times it is “LastName, FirstName”. It is consistent in each sheet, but is varies from client to client…i.e., Client A always sends “FirstName LastName” and Client B always sends “LastName, FirstName”. I already have code in my main PowerBI file that will switch “FirstName LastName” to “LastName, FirstName” but I don’t need to apply it if it’s already done in the Excel sheet. So that’s why I’m trying to add the parameter at the Excel level to indicate True/False if the names need to be switched or leave them alone, and I want PowerQuery to read that parameter from the file instead of me having to manually switch it every time I change clients.
In that case don’t set up a parameter in PQ but like Nick illustrated load your ExcelParameterTable and Drill Down to create a separate query returning the ExcelParameterValue so you can refer to that in other queries within your PBIX file.
@Nick_M thanks for chiming in and please always do if you have something to add
Thanks @Melissa and @Nick_M,
I’ve been tinkering with this for a while and I was able to get the value to load as Nick described, so that’s working okay, but as is often the case, I’ve discovered the other part of the problem.
I need that value to be used in a custom formula, something like this:
let function = (Text) =>
if
testParam = "TRUE"
then <do this thing>
else <do something else>
in
function
where testParam is the value from the Excel parameter table as discussed. I’m trying to apply this function to the names in the column so that if the parameter is TRUE, i.e., they are already switched so leave them alone. If it’s FALSE, it will apply the process to switch the names. I’m having trouble getting the function to recognize the parameter and the logic (the 3rd line). Can you point me in the right direction?
Thanks again.
Think the second makes more sense procedural wise especially for future maintenance of the code
Here’s my sample file eDNA - Conditiona Switch Names.pbix (17.0 KB)
