Forum Discussion
Restrict rows retrieved by query?
The single-qoutes is needed as part of my SQL query to filter a string column in the SQL database
The 1st double-qoute stop the "free text query" and allows to insert a parameter
Ampersand is needed to merge the parameter with the SQL code around paramater
The 2nd double-qoute continue the "free text query"
I hope this explains the code.
Now back to your problem... 19 foreign keys "scary face". 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. 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 don't think this is the right path for you. 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.
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. 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 & ")"
sdjensen, thanks for the example. I'll give it a try. Just to make sure I'm reading it right, the parameter should be specified as: <single-quote><double-quote><space><ampersand><space><parameter I created in Power BI><space><ampersand><space><double-quote><single-quote>?
So far, we've identified 19 foreign keys that will probably becomes dimensions in our data warehouse - and are probably fields users will want to select on. Right now I'm working with a subset - two dates and three text fields. If I can get those to work and be optional, I'll see about sharing the query, letting it retrieve all columns (the user can hide columns they don't need.) That might be workable.
Thanks for the suggestion. I'll reply again with the results.
The single-qoutes is needed as part of my SQL query to filter a string column in the SQL database
The 1st double-qoute stop the "free text query" and allows to insert a parameter
Ampersand is needed to merge the parameter with the SQL code around paramater
The 2nd double-qoute continue the "free text query"
I hope this explains the code.
Now back to your problem... 19 foreign keys "scary face". 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. 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 don't think this is the right path for you. 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.
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. 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 & ")"
sdjensen, that got me the solution I needed. Thanks so much, I'll Accept your answer.
I actually took a slightly different tack in the end. Instead of modifying the WHERE clause in the Advanced Editor, I created a filter on each column that referred to the parameter. So for a range of dates against a DateTime column in the database I created two parameters with type "any" and entered the formula
= Table.SelectRows(#"Reordered Columns", each [KeyDateTime] >= DateTimeZone.From(StartDate) and [KeyDateTime] <= DateTimeZone.From(EndDate))
and for a number column in the database that can contain nulls, I created a parameter with type "Text" and entered the formula
= Table.SelectRows(#"Filtered Rows2", each (Text.From([InspMethod]) = InspMethod) or (InspMethod = "ALL") or (([InspMethod] = null) and (InspMethod = "BLANK")))
So that the user can enter "ALL" to retrieve all rows including nulls, "BLANK" to retrieve rows with null, or a value to retrieve rows with that value.
The advantage of this approach is that I only need a generic query, not 19*18*17... queries or a complex one that tests for the presence of parameters, or all 19 parameters in every query with a convoluteted WHERE clause, etc. It appears that the query gets generated with the parameters it needs.
It seems to be working great.
Thanks for the lead and great information.
Regards,
Sam
sdjensen, thanks for the explanation.
And you're exacty right about the combinations of parameters, that's what worries me and what I meant by making them "optional". If I can find a way to make the WHERE clause ignore a parameter if it's null or if it has a certain value (like "ALL"), without sending performance down the drain, then I can create a single query for everyone to use. Otherwise this will be prohibitive.
Not matching on an empty parameter value is the same problem I have when I try to use Microsoft Query in Excel (which allows me to link a query parameter to a cell on the spreadsheet.) I'm hoping that with the capabilities of Power BI I can get around it.
Thanks again,
Sam