Excel 2016 query editor manage parameters
- #EXCEL 2016 QUERY EDITOR MANAGE PARAMETERS HOW TO#
- #EXCEL 2016 QUERY EDITOR MANAGE PARAMETERS CODE#
- #EXCEL 2016 QUERY EDITOR MANAGE PARAMETERS FREE#
SQL Server should do the work of retrieving the appropriate rows (which it can do quickly and efficiently), the tool should not require the user to bring in all of the data and create complex mechanisms to filter it down to the data they want. What I am looking for is the capability to create custom queries in Power BI as the basis for reports and visualizations - not limiting it to generic queries or pre-defined queries built by a guru, but letting end users build their own queries, reports and visualizations without having to be an expert in SQL, DAX, M or some other specialized language or feature set in order to limit the data they look at. What approach is "better than 'here is all the data, ask yourself'"? I'm sorry, I don't understand your response.
Then you will be able to chance the SQL to use IN instead of =, but your users will only get the wanted result if they learn to write the right text - using my example from before this would be 'US', 'UK' and your where clause should be "WHERE a.Country IN (" & CountryParameter & ")"
#EXCEL 2016 QUERY EDITOR MANAGE PARAMETERS FREE#
With single value per parameter I mean they will not be able to select both US and UK customers but only one of them - unless you make you parameters free text values.
#EXCEL 2016 QUERY EDITOR MANAGE PARAMETERS CODE#
You will need to many different variations of your code because consider that your user only select a parameter in a few of your lists, then the rest will be blank but if you query your SQL with WHERE a.Country = '' then you will only get the customer with no country value (blank), but what you really want is not to filter the country column in your SQL table, so you will somehow have to handle all the combinations of posible where clauses you need. I don't think this is the right path for you. As I see it you will have to create 19*18*17*16*15*14*13*12*11*10*9*8*7*6*5*4*3*2 combinations of your code + create 19 parameters lists with a complete set of values for your user - and this will only give them the option to select a single value per parameter. I assume that you users will need to be able to filter several parameters at once? This will force you to type SQL code from now until you retire. The 2nd double-qoute continue the "free text query" The 1st double-qoute stop the "free text query" and allows to insert a parameterĪmpersand is needed to merge the parameter with the SQL code around paramater The single-qoutes is needed as part of my SQL query to filter a string column in the SQL database I can do this fairly easily using a Microsoft Query connection in Excel and our users probably could, too, but that has some issues. I'm hoping Power BI would be a good end-user tool for this data retrieval and analysis need, and I'd like to give them the capabilities of Power BI as well. We've given them other tools that require using SQL to get the data, and it has not proven useful or productive. And they need to be able to create a query with the parameters they need - there are several dozen columns they might want use, alone or in combination, to restrict the rows retrieved.
I could easily do this by modifying the SQL query, but most of our users can't. What I need to do is allow the user to easily create parameters (several, both date ranges and text "Like" clauses), set their values, and send the query with parameters to SQL Server to retrieve the several hundred rows they are interested in. So it is not possible or practical to retrieve the entire table in Power BI and do the filtering there. The SQL Server table that contains the data of most interest to users contains over 123,000,000 rows, and each row contains 140 columns - a couple of KB per row in CSV format.
#EXCEL 2016 QUERY EDITOR MANAGE PARAMETERS HOW TO#
However, there's a problem I can't seem to get around: How to easily restrict the rows returned by a query? Can anyone point me to an easy, end-user-friendly solution? I am evaluating Power BI for our user community, and it looks great.