Forum Discussion
Helper IWildcards and comparisons
I just started using Power BI with data from our Learning Management System. I have quite a bit of experience with Access, Excel & SQL, but some of the concepts & terminology in Power BI seem a bit different. That make searches on both the message boards here as well as the web not very successful.
I have some tables in which I'm attempting to limit which data is included, so I don't have to filter those results out later in all of my reports/graphs.
For instance, I have a users table that includes several generic accounts for students and teachers. I would like to exclude these from the table. When I go in to transform data, I can filter on the name column, but I'm limited to 1000 results, so I only find a fraction of the accounts to exclude. We have over 1000 of these teacher & student accounts alone, since we created both for each faculty member at our college. In SQL, I can easily accomplish this with the expression
WHERE (((User_dim.[name]) Not Like "*student*")) AND (((User_dim.[name]) Not Like "*teacher*"));
Is it possible to accomplish something like this in Power BI?
Hi power-bi-noob
The table editor in Power Bi is called Power Query. It is a but like Acces wizard but much more powerful.
In Power Query you can click on the arrow in a column heading and apply text filters / contains / advanced
that will do the same as SQL LIKE "*student" and not Like "*teacher*
Give it go onz small amount of test data until you have learnt how to use it.
Please click the thumbs up and accept as solution. Thanks 😀
If you are new then please raise one ticket per problem.
6 Replies
speedrampsSuper User
Hi power-bi-noob
The table editor in Power Bi is called Power Query. It is a but like Acces wizard but much more powerful.
In Power Query you can click on the arrow in a column heading and apply text filters / contains / advanced
that will do the same as SQL LIKE "*student" and not Like "*teacher*
Give it go onz small amount of test data until you have learnt how to use it.
Please click the thumbs up and accept as solution. Thanks 😀
If you are new then please raise one ticket per problem.
- power-bi-noob
Hi speedramps , thanks for the suggestion!
I had previously tried this approach, but the problem is not all of these users occur within the first 1000 users in the table, and I am limited to 1000 results in the Power BI Query window.
- speedramps
create another query,
click on User_dim.[name]
remove other column
and remove duplicates
You should now have a shorter list that you can use to hard code apply text filters / contains / advanced.
Alterntaively use SQL to create a Good or Bad list of User_dim.[name]
The in Power Query merge the list with your source data and select the inner/outer join option to include good or exclude bad.
- power-bi-noob
Does Power Query Editor support standard SQL? That's what I was attempting to use and did not get the results I was looking for. Also, one thing I did not mention is I'm working with local text files, not connected to a server anywhere at the moment. I have a PowerShell script I wrote that fetches/updates the data stored locally.
By the way, I was able to remove these using your other suggestion, sort of. I replaced my existing table with data that only showed the test users, and unchecked them, so it wrote a long query like such:
Table.SelectRows(#"Removed Columns", each ([name] <> "Aaron Student" and [name] <> "Adam Student" and ... about 250 other names ... and [name] <> "William Student"))
Sort of a hack, but it works. 😁 Still a wildcard search would be more elegant. Not sure how this all will impact performance.
After I was able to deselect all of this data, I updated my data files using the Powershell script I wrote, and refreshed the data, so now it only shows the records I'm interested in.
- speedramps
Hi agian power-bi-noob
The Power Query wizard creates "M" language
which is an universal query langauge.
You can view and edit M by clicking edit query and advanced editor.
Power Bi sends "M" to the SQL server which traslates it to SQL and sends the data back. This is called "query folding".
Power Query is very powerful and I suggest you try learn it and cross train from SQL.
If you click on Get Date, SQL Server, Adavanced option then you can add SQL code into Power BI.
This is a bad pratice !
You should create a SQL view with joins on the server and use that in Power BI rather than access SQL tables directly.
Click here to start learning Power BI for free
Please clcik the thumbs up and accept as solution buttons. Thanks 😀