One can link different types of data sources while using the power BI desktop or the Power BI service (SAAS BI). Power BI helps in data import as well as connects to the original data from the repository also called DirectQuery. Power BI offers a wide range of benefits in regard to data import and data transfer.
Data should be imported to Power BI as and when desired. The power BI imports data by means of a high-performing query engine of Power BI, which in turn gives a highly enhanced experience to the users. With time, Power BI has evolved flexible data import systems like imports can be used in more cases, overcoming shortcoming of direct query, etc.
How Importing Data Is Easy With Power BI?
Power BI can be used to import data to and from a large number of data sources. Power BI connects online services like Salesforce, Dynamics 365, databases like server, Access, Amazon Redshift, simple files like Excel, JSON, other data sources such as spark, Web sites, Microsoft Exchange, etc. Some of these sources can be accessed using direct query also. There are more sources to be added to the list in the near future.
This is a most asked question regarding power query which most people want to know. So here it is. You will get all the details further from the steps to various other points.
Query Data or editor plays the role of data for editing or modifying it. You can filter the result-sets which will act as the ‘where’ clause.
- Custom Query Editor
- Advanced Editor
Method 1: Custom Query Editor
- Click on the edit query option as you want to use the ‘where’ clause.
- This will make you open the power query editor window where you will see a down arrow key.
- Click on the down arrow key sign present in the right of the column name where you will see the number filter.
- Select a number filter and set the condition according to your requirements.
Basic– In this method after selecting the conditions ( equal, does not equals, end with) you need to enter the value on which you want to apply the given number filter.
Advanced– This method is quite the same but you can apply more than one number filter on more than one column here.
EPC Group also provides Power BI Consulting Services for any organization. We have more than 70+ in-house Business Intelligence experts with collective experience of more than 100+ years. You can also check out the cost cost of Power BIand the difference between each module of Microsoft Power BI.
Method 2: Advanced Editor
Suppose you have two columns which are namely total sales and country name and my query is to filter out the data which contains total sales for the USA country. First, select the edit queries option.
- Then select the advanced editor and within the editor enter the below query and you will get the desired result.
Test_where=calculate ( [total sales], [country] =USA)
There is also one more way but only if you are not worried about your data privacy settings. By this, you have to turn your settings off.
File>options and settings>query options>privacy
You can also clean up the code for it to look like a single query that looks like this:
Let
Cust ID=Excel.CurrentWorkbook(){[Name= “CustID”]}[Content]{0}[Column1],
Source=Sql.Database ( “VI107064\SQLEXPRESS”, “Stock”){[Schema= “dbo”,Item= “customer”]}[Data],
# “Filtered Rows”= Table.SelectRows(Source, each([CustID]=CustID))
In
# “Filtered Rows”
If you right-click on the last step in the Applied Steps pane, you can see that this get interpreted in the SQL Native Query as follows:
select [_]. [ <A bunch of columns here> ],
[_].[ <…> ],
[_].[ <etc> ]
from [dbo].[customer]as [_]
where [_].[CustID] = <CustID value here>
Conclusion Hence query editor in Power BI is an effective solution for using the ‘where’ clause. Several preprocessing functions are involved in this which will be easy after you go through the above-mentioned steps accurately. These steps will surely help you with using the ‘where’ clause.
