Restrict rows retrieved by query?

[SamTrexler]

I am evaluating Power BI for our user community, and it looks great. 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?

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. So it is not possible or practical to retrieve the entire table in Power BI and do the filtering there. 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.

I could easily do this by modifying the SQL query, but most of our users can't. 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. We've given them other tools that require using SQL to get the data, and it has not proven useful or productive.

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. Is this possible?

Thanks for your help.

Sam

[gyangupta]

Go to Power Query Editor and click on Table icon in front of first column header.

Click on Keep Top Row column and enter how many rows you want to have.

[rockdrigom]

classical problem. Hit the table and ask, what do you want?

I think this approach is better than "here is all the information, ask yourself"

[SamTrexler]

I'm sorry, I don't understand your response. What approach is "better than 'here is all the data, ask yourself'"?

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. 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. Can you imagine refreshing a query on a regular basis, if it retrieves all rows and then filters them? Ugh!

But it seems that's just not possible with this tool. Is that correct? Too bad, the capabilities of Power BI are great for end-user analysts to define and publish useful management and staff visualizations, but the learning curve is too steep (or the tool is not appropriate) if there's no simple way to do such things.

Am I missing something? I'm new to the product, so that's very possible - and the purpose of this question. I'd like to use Power BI if it has the needed capabilities.

Thanks for your response and help.

[Seth_C_Bauer]

@SamTrexler Is there some reason that you haven't gone down the modeling route? Using SSAS Tabular for instance? Everything you describe as it relates to an end user filtering/slicing and dicing without the need for complex queries is the purpose of developing a BI layer to your relational data. The end result is a model that contains the pertenant business information, and gives the end users the ability to search anything they want in the model.

[SamTrexler]

@Seth_C_Bauer, that's where we're headed. We're looking at using SSAS Tabular modeling for business analytics. But at this stage of the project, we have flattened operational data that we intend to load into SSAS, and I was wondering if we could use that data for operational analysis as well - retrieving detail rows for analysis instead of the lowest level of aggregation.

I have prototyped this in Excel using a Microsoft Query data connection and applying criteria to the query that are linked to cells in the spreadsheet. This gives the user the ability to change the value of the parameters in the spreadsheet and get the corresponding rows. It's also not too hard for someone experienced in Excel to add criteria to the query, without knowing SQL. (There are some gotchas there, which we'll need to train on.)

When I saw Power BI, I liked the power of reports, visualizations and dashboards very much. And I wondered if it could do the same thing, with greater presentation capabilities. But it appears the answer is no, so we may have to remain "old school" with Excel and Microsoft Query, unless someone has a solution or recommendation?

Thanks.

[v-qiuyu-msft]

Hi @SamTrexler,

I agree with @Seth_C_Bauer that the Query Parameter feature is the thing you are looking for. We can create one or more query parameters, then add filter conditions to limit data based on parameter values. When the user runs the report, the Enter Parameters window will popup and user can check his prefer values to filter rows. Please take a look at links below:

Deep Dive into Query Parameters and Power BI Templates

query parameter

If you have any quetsion, please feel free to ask.

Best Regards,
Qiuyun Yu 

[SamTrexler]

Qiuyun_Yu,

Thanks for your feedback. The first of the links you gave was informative and I hadn't seen it before.

The problem I'm having is trying to relate the query parameters to the SQL Server select statement. As the link shows, it is possible to change the values used for a data source. And there are some good blogs that show how to change the SQL Server instance that the query connects to, for example. But I cannot find a simple way to modify the SQL query so that it incorporates the query parameters, without learning the new language and creating very specific code in the Advanced Editor to use it.

I'm new to Power BI, so please correct me if I'm wrong, but the problem I see with using filter conditions is that SQL Server will still return all of the universe of rows it contains, and the filter will be applied when it is displayed or used in a visualization or report. I do not want SQL Server to return 123 million rows so that the user can filter down to the one thousand or so rows he/she is interested in. Instead, the user should be able to enter values for the parameters, which are then sent to SQL Server as part of the query, and it returns just the one thousand rows of interest.

Is this possible? I haven't found a way to do it, but I sure would like to - without advanced coding, something our users can set up and do. Each user will have specific columns of data they are interested in seeing from the operational data store, and specific criteria to use to select the rows they are interested in. And the number of such requests is huge - far more than our staff can develop for each user. Essentially, we would like Power BI to be an end-user ad hoc tool for operational data as well as for aggregated data from sources like SSAS. And I'm not seeing a way to do it.

Thanks for yoru help.

[sdjensen]

@SamTrexler - it will be possible to implement these parameters in the SQL Query - I do it in a "content pack" I have created for our customers using Microsoft Dynamics NAV. The problem with creating a dynamic model for Dynamics NAV is that the tables, where the data is stores, is prefixed with their company name so table names is always different from customer to customer.

So what I did was creating a file with 3 free text parameters Server, Database and Company and I pass all 3 parameters on to the queryes against the source SQL database.

As I read the thread free text paramaters will not cut it for you and there is some limitations that you need to be able to fullfill if this is the right way for you to go.

1. You need to be able to create full parameter lists with all the values your users would want to filter on.

2. It will satisfy your users need only to be able to select on value for any of your parameter lists.

2. I don't think it will work for you if your users will need to be able to filter on a lot of different columns in your solution

3. You need to be able to write the query so it can handle that your users only filter perhaps 1 out of x parameters in your model.

Basically you will have to write one SQL statement that can handle all the mixes of parameter selections made by the user. The user however is limited to only being able to select the values of the parameters provided by you and from reading your previous posts this just seems to be to simple compared to what you want?

However if you want to test this I will give you an example of how you can use parameters in your SQL query.

In this example I wish to return all the customers from my customer table where country is US - the country is what my user selected from a parameter list containing all the possible countries in my table. In this example I have called my parameters list CountryParameter

let
    Source = Sql.Database(Server, Database, [Query="
--SQL Query Start
SELECT
	a.DWID_Customer AS 'CustomerKey'
	, a.CustomerNo AS 'Customer No.'
	, a.Name AS 'Customer Name'
	, a.CustomerLabel AS 'Customer'
	, a.CustomerPostingGroup AS 'Cust Post. Group'
	, a.Country AS 'Country'
	, a.City + ', ' + a.Country AS 'City'
FROM D_Customer a
WHERE a.Country = '" & CountryParameter & "'"
--SQL Query End
    "])
in
    Source

I hope it makes sence I am a bit tired.. he he

[SamTrexler]

@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.

[sdjensen · Accepted solution]

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 & ")"

[SamTrexler]

@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

[SamTrexler]

@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

[Seth_C_Bauer]

@SamTrexler Take a look at Parameters, they were just added to the Desktop in the April release. They may provide a way to filter the information, but I haven't played with them yet enough tell you how they work and/or if they'll work for your use case.

[SamTrexler]

Eno1978, Thanks for your responses.

I have looked at query parameters, but can't find a way to have them affect the SQL statement sent to SQL Server, without resorting to coding on the Advnaced Editor. And this needs to be an end-user capability, so that's out for my immediate purposes.

Any suggestions?  

[arify]

As far as I understand, you would want something like, "picking the columns before loading rows"? It does seem possible in the future, but we don't have a direct way of doing this yet. I couldn't find a request for it here, so you might need to create a new one.

In the query editor, we don't load more than 1000 rows, so that you can preview the query and do your operations easier. But if your table has so many columns that even 1000 rows would be too much, you can cancel (on the bottom right side) and restrict the number of rows to something much lower, like 10. (Home tab of Query Editor >  Keep Rows > Keep Top Rows)