Filtering Data in Power BI

Power BI provides a dashboard interface for displaying summaries of data.    In order to avoid attempting to export your entire ESdat database to Power BI to produce these visuals it is important to extract the appropriate data from ESdat, and know how to filter it.  

You will need to construct your filter so that less than 10,000 records are being exported to Power BI for use on reports.  The steps below provide an introduction to filtering data in Power BI.

Firstly select the ESdat data view that you need, ensure you select the most relevant view for your data, for example if you want a summary of Samples you should select the ESdat_Samples view, not one of the Chemistry Views.  The example below shows the user selecting Water Chemistry results.

Click Edit Query to create a Filter for just the data you need.   

The Drop-Downs in Power BI will only have a subset of the available data, so you may not be able to get your final required filter using the drop-downs but it is useful to use these to gain an understanding of how the filter syntax is constructed as items are selected.

As you apply filter items Power BI creates it’s own form of SQL called Power Query M which is shown to the user.   

You can edit it as required to return the specific data you need.   The example returns Water Chemistry results for the Project “DemoProject”, at Locations "BH03" or "BH27", and for the following Analytes ("Alkalinity (Bicarbonate)",  "Calcium" ,  "Lead" ,  "Sodium").   You can click the “Advanced Editor” button to see the full SQL in a larger box.

The Power Query M language for the above example is:

= Table.SelectRows
(ESdat_Water_Chemistry_table, each 
                ([Project] = "DemoProject")        
                and ([Location_Code] = "BH03" or [Location_Code] = "BH27") 
                and ([Chem_Name] = "Alkalinity (Bicarbonate)" or [Chem_Name] = "Calcium" or [Chem_Name] = "Lead" or [Chem_Name] = "Sodium") 
)

For more information on Power Query M language  see https://learn.microsoft.com/en-us/powerquery-m/  and https://learn.microsoft.com/en-us/power-query/power-query-ui