Omit certain results from data when pulling from the SQL database

[PhilB]

Hello,

I imagine this is possible, i just cant seem to figure it out myself. And i know i could apply a filter for this on my visualisations. However this could get tedious quickly.

Reason for: 

I have a website running wordpress and MySQL and i am building reports on the sales and registrations over the month. However there are numerous user accounts for testing purposes and some of the accounts (around 20) belong to people who are part of a 'Coaches' group. Right now these accounts are buggering with my figures. 

Is there a way to strip out these accounts in Power BI at the moment that i pull/refresh the data? this would save me a ton of time each month if i could. 

Something like

`select * where username  IS NOT users names, or user ID's`

Any help would be massively appreciated.

Regards

Phil B 

[PhilB]

Hi,

With all the data in my database being a mess, i have gone for @Habib 's suggestion for ease of use and time saving. 

Thank yo for advice though @Seth_C_Bauer it is still much appreciated. 

Kind Regards

Phil B

[PhilB]

Hi again,

Unfortunately for me the information is stored across 100+ tables, its a wordpress website with a plugin called Event Espresso for event management and BuddyPress to take care of the community side of things. The biggest problem im having right now is linking everything together. everything from event espresso is a custom post type in the wordpress posts tables. Its a bit of a mess if im honest.

Thank you for the solutions though i will give them a try once i have finished with some of the other bits and pieces im working on this afternoon.

PhilB

[PhilB]

Hi @Seth_C_Bauer and @Habib thank you for your replies.

In answer to @Seth_C_Bauer'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

[Seth_C_Bauer]

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

[Seth_C_Bauer · Accepted solution]

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

[Habib]

@PhilB You can use filter option in Query Editor. This will add a step in your query settings and every time data refresh is executed this step will be executed as well.

Please refer to below image for reference.