Query filter example steps

  1. Create a new blank query and open the Advanced Editor window.
  2. Use the Table.SelectRows() function to apply the US query predicate.
  3. From a new blank query, filter on the Children column with a >= operator.
Figure 50: M expression for the United States Customers query
Figure 51: M expression for the Customers w3+ Children query
  • Table.SelectRows() is the primary table-filtering function in the M language.
  • It's functionally aligned with the FROM and WHERE clauses of SQL.
  • Observe that variable names are used as inputs to M functions.
  1. From a new blank query, use the conditional logic operator or to define the filter condition for the Middle Name and Title columns.
Figure 52: M expression for the Missing Titles or Middle Names query
  • Use the lowercase literal null to represent the absence of values.
  1. From a new blank query, use the #date literal to apply the 2012-2013 filter.
Figure 53: M expression for the 2012-2013 First Purchase Customers query
  • Literals are also available for DateTime, Duration, Time, and DateTimeZone.
  1. From a new blank query, use parentheses to define the filter conditions: management occupation and either female gender or bachelors education.
Figure 54: M expression for the Mgmt and Female or Bachelors query
  • The parentheses ensure that the or condition filters are isolated from the filter on Occupation.
  1. From a new blank query, reference the United States Customers query and use the Table.Sort() function to order this table by the Annual Income column. Finally, use the Table.FirstN() function to retrieve the top 100 rows.
Figure 55: M expression for the Top 100 US Customers-Income query
  • Table.Sort() supports multiple columns as per the Importing data to Power BI Desktop models recipe.
  • 100 Rows are returned by the query starting from the very top of the sorted table. In this example, the set returned is not deterministic due to ties in income.
  1. From a new query, use the List.Distinct() and List.Sort() functions to retrieve a distinct list of values from the Customer Sales Territory Country column.
Figure 56: M expression for the Customer Sales Territory List query
Figure 57: List Preview from the Customer Sales Territory List query
  • A list of distinct values can be used in multiple ways, such as a dynamic source of available input values to parameters.
  1. From a new query, use the DateTime.LocalNow(), DateTime.Date(), and Date.Year() functions to retrieve the trailing 3 years from the current date.
Figure 58: M expression for the Trailing Three Years query
  • The current date and year are retrieved from the DateTime.LocalNow() function and then compared to columns from the date dimension with these values.
上一章目录下一章