Forum Discussion
Omit certain results from data when pulling from the SQL database
PhilB Are you ever going to need the info for those users? If not, you could just filter this out by referencing a SQL View where the logic is built in to exclude those users, but return the relevant information you want for the sales table.
Hi Anonymous and Habib thank you for your replies.
In answer to Anonymous's question, no i wont need them accounts again. I use some of them for running test payments through the website and for testing purposes. Others are admin accounts and the rest are what we class as a 'coach' (the website sells golf coaching) so i would not need to use them again. I would not know where to begin with the SQL query though. I can manage a select query but have not tried anything more complex than a couple of JOINS. I would be willing to try that method though, mainly as it would be a learning curve.
Habib you're method seems a bit easier to implement so i would be inclined to try that first.
Which method would be best? or does the filter method create the SQL query itself in the background?
Thanks PhilB
PhilB Couple things. If you are just importing data from a table, you are pulling in all the columns (which you most likely don't need). You could pull in the sales table (or wherever your "coach" value column is) via a sql query. This is an option when you connect to the SQL database from Power BI. In there you can write a select * from table where column name != 'Coach'
Or, if it is a set of values you would write something like "Where column name NOT IN ('Coach', 'Some Value', Some Value')
The above method would filter your data prior to being imported into the Power BI model.
If you use the other "Step" method, the data is first pulled into the model, then filtered out. If your data set isn't very large, this may not be a concern, but if it is a lot of records filtering in SQL is better.
The other option I referenced was to create a VIEW in SQL. This is nothing more than a select statement that allows you to only return the specific columns you want to pull in to Power BI without all the over head of additional columns you won't use when you connect to a table. This method also allows you to add and remove columns in the VIEW and Power BI will just pull them in when you refresh. From the Power BI side, you would just click the view instead of the table.
Depending on your data size, and where you are most comfortable, Power BI offers all the abilities to customize and shape your data in the "Data" section, so don't feel that you have to use SQL. Just throwing out options for you.