1. M for Power Query in Excel
Let’s open our Power Query editor and take a look at the Advanced Editor for the dailycensus query. Let’s say we want to add documentation with a multi-line comment. We can enter the desired text between forward slash and asterisk symbols.
Single-line comments require two forward slashes before a line of text and are helpful to document tasks from a given step. A handy feature is that single-line comments become a tooltip in the applied steps list. Hovering over the step shows the description we just entered in the comment line.
Grouping queries is a helpful method to keep queries organized. Let’s group the three queries that made up the dailycensus query. Holding shift and highlighting these queries, then right-clicking, gives a “Move to group” menu option. Let’s create a new group called “sourcefiles” containing these files.
Let's now open up a new blank query. Under “other sources” is the Blank Query option. Entering "= #shared" renders a series of records, each representing Power Query’s built-in M functions. It is helpful to convert to a table to enable easier searching. Now, we can isolate functions by sorting and filtering just as any typical table. We can view full descriptions of each function by clicking on the white space in the cell. For example, the function List.Contains() shows a description of its usage and an overview of inputs and output.
Now, let’s open up another blank query. This time, let’s create a simple list by entering a list of items between curly braces. This returns a single-column list with all entered items. Notice how Power Query aligns the icon next to the query according to the type. In this case, a list-type icon, where others show the Table type.
Power Query can also expand a list based on a desired range; for instance, say we were looking to create a list of numbers from 80 to 100. Entering two dots between listed values within the curly braces renders a full list. We can name this list “List80to100” and then use it as a filter criterion for another query. Let’s open the dailycensus query and add a new custom column using the List.Contains() function for days with admissions values between 80 and 100.
We can also easily create query parameters using the Manage Parameters options. Let’s say we wanted a dynamic date input for filtering the dailycensus query. We can create a new parameter and call it “Start Date”, specify the type as Date, and enter in a current value. Now, we have a new query parameter listed in our queries pane. We can create a filter in the dailycensus to respond to the parameter’s input by specifying a filter on the effective_date field. We then add a Date Filter as normal, however, instead of putting in a static value in the filter options, we specify our named parameter. The query will automatically filter based on the date value we entered in the Start Date parameter. Looking at the automatically generated M function, Table.SelectRows(), we can see how Power Query annotated the reference to the parameter’s name ”Start Date” in the formula bar.
Let’s now see how a custom function can be utilized in the advanced editor for dailycensus. We can create a custom function that converts Celsius to Fahrenheit. We first state the custom function name and its input here. In this case, celsius goes to Celsius times 9/5 and then adds 32. We can add a new column to this table with the Table.AddColumn() function, specifying its new name, and then applying our new custom function to each value of MEAN_TEMPERATURE. Lastly, to return the added column in our query, we need to update the expression name after the “in” clause and ensure no comma is at the end of the last expression step. Now, we have a new column of converted temperature.
Now it’s your turn!
2. Let's practice!