close

Can We Use A ‘Where’ Clause In Power BI?

Posted by Sas Chatterjee on May, 14, 2021 08:05

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

  1. Click on the edit query option as you want to use the ‘where’ clause.
  2. This will make you open the power query editor window where you will see a down arrow key.
  3. Click on the down arrow key sign present in the right of the column name where you will see the number filter.
  4. 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.

image

Advanced– This method is quite the same but you can apply more than one number filter on more than one column here.

image

EPCGroup also provides Power BI Consulting Services for organization. We have more than 70+ In house Business Intelligence experts with collective experience of more than 100+ years. You can also check out Power BI Cost and different difference 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

image

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>
Power BI Consulting

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.

[gravityforms id=41 title=”true” description=”false”]
<div class='gf_browser_chrome gform_wrapper exit_intent_popup_wrapper gform_legacy_markup_wrapper' id='gform_wrapper_41' > <div class='gform_heading'> <h3 class="gform_title">Exit Intent</h3> <span class='gform_description'></span> </div><form method='post' enctype='multipart/form-data' id='gform_41' class='exit_intent_popup gform_legacy_markup' action='/%E2%80%98where%E2%80%99-clause-in-power-bi/' > <div class='gform_body gform-body'><ul id='gform_fields_41' class='gform_fields top_label form_sublabel_below description_below'><li id="field_41_1" class="gfield gform_hidden field_sublabel_below field_description_below gfield_visibility_visible" ><div class='ginput_container ginput_container_text'><input name='input_1' id='input_41_1' type='hidden' class='gform_hidden' aria-invalid="false" value='https://www.epcgroup.net/%E2%80%98where%E2%80%99-clause-in-power-bi/' /></div></li><li id="field_41_9" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_9' >Full Name<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_text'><input name='input_9' id='input_41_9' type='text' value='' class='medium' placeholder='Full Name' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_6" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_6' >Email<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_email'> <input name='input_6' id='input_41_6' type='text' value='' class='medium' placeholder='Email Address' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_7" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_7' >Phone<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_phone'><input name='input_7' id='input_41_7' type='text' value='' class='medium' placeholder='Phone Number' aria-required="true" aria-invalid="false" /></div></li><li id="field_41_10" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_10' >Company Name<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_text'><input name='input_10' id='input_41_10' type='text' value='' class='medium' placeholder='Company Name' aria-required="true" aria-invalid="false" /> </div></li><li id="field_41_8" class="gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible" ><label class='gfield_label' for='input_41_8' >Message<span class="gfield_required"><span class="gfield_required gfield_required_asterisk">*</span></span></label><div class='ginput_container ginput_container_textarea'><textarea name='input_8' id='input_41_8' class='textarea medium' placeholder='Type your message here...' aria-required="true" aria-invalid="false" rows='10' cols='50'></textarea></div></li></ul></div> <div class='gform_footer top_label'> <input type='submit' id='gform_submit_button_41' class='gform_button button' value='Submit' onclick='if(window["gf_submitting_41"]){return false;} window["gf_submitting_41"]=true; ' onkeypress='if( event.keyCode == 13 ){ if(window["gf_submitting_41"]){return false;} window["gf_submitting_41"]=true; jQuery("#gform_41").trigger("submit",[true]); }' /> <input type='hidden' class='gform_hidden' name='is_submit_41' value='1' /> <input type='hidden' class='gform_hidden' name='gform_submit' value='41' /> <input type='hidden' class='gform_hidden' name='gform_unique_id' value='' /> <input type='hidden' class='gform_hidden' name='state_41' value='WyJbXSIsIjEwNTJhNGVmMWMyNzI3YTJmMjdiZTA1NjU4ZDMzYzY3Il0=' /> <input type='hidden' class='gform_hidden' name='gform_target_page_number_41' id='gform_target_page_number_41' value='0' /> <input type='hidden' class='gform_hidden' name='gform_source_page_number_41' id='gform_source_page_number_41' value='1' /> <input type='hidden' name='gform_field_values' value='' /> </div> </form> </div>