1. Custom columns in Power Query with M
Let’s open up our previous working file and look at the dailycensus query.
We can see how each applied step taken is tied to M code visible in the formula bar.
M Code can be edited in the formula bar and the advanced editor. Let’s quickly look at the advanced editor from the Home tab.
Here, we can see how each named step aligns with an associated M code expression. We will delve deeper into the advanced editor in the next chapter.
Power Query has a helpful feature called “Column from Examples” in which rows are automatically filled in with estimated M code logic based on the sample output provided.
Let’s test out “Column from Examples” for a selected column of SPEED_MAX_GUST. Double-clicking the sample region reveals various default options pertaining to the selected column.
Let’s say we want to bin values. Simply typing in 40-50, Power Query automatically recognizes that we are defining a range and has generated output for all rows. Click OK and review the new custom column’s M code in the formula bar.
We can also edit directly in the formula bar. For instance, let’s switch the bin size to 15 instead and see how the groups update.
Clicking on the gear of this shows the M code within the custom column pop-up. This also allows you to check the M code to ensure the syntax is written as expected.
Now, let’s say we want to create a conditional column on mean temperature from the dailycensus query.
We can create this using the basic “conditional column” button on the toolbar ribbon, with null as "no temp", >=25 as "severe", otherwise "normal".
However, to make more advanced conditional logic we must use the custom column feature to enter in the code directly. Now we can specify a more precise grouping of ranges, such as the threshold of <= -10, to also be classified as "severe".
Now, let’s say we needed to rank total patient activity within each temperature group by day, from lowest to highest. To do this, we need to create a grouped index.
We start by selecting our temperature group column and perform the Group By operation with “all rows” and name it “Grouped”. Let’s also add a row count to see how many days fall into each group.
Notice how the rows have now all been condensed by their group. However, we can still visualize the embedded table details by clicking on the white space of each row, for instance, the 88 days of “severe” temperature.
M code has many powerful Table functions that enable users to perform transformations on the data, even while embedded within these tables. We can reference them by the column name, which comprises the embedded tables.
For instance, we can specify the sorting of data within embedded tables using the M Table.Sort() function. This function first references the column housing the embedded tables, followed by the specific column name we wish to sort by and the order within curly brackets. In our case, Table Sort for the column "grouped" which contains our embedded tables, followed by the sort specification for patient days in ascending order.
Previewing this grouped_sorted custom column compared to the previous one shows the impact of the Table.Sort() function.
Now, to create an index ranking within each group, we require another custom column with an M table function. Using the Table.AddIndexColumn function, we specify the index to be created on the sorted grouped column, add the name of the new index column, and specify the index starting point. Let's name it grouped_index and start the sort from 1.
This creates a new column, grouped_index, which can be expanded to reveal the sorted rankings of patient days within each temperature grouping.
We have a custom grouped index that fits our custom M table function specifications.
Now it's your turn!
2. Let's practice!