1. Custom columns
Welcome back! In this chapter we'll explore how to create custom columns with Power Query in Excel.
2. M Formula Language
It's important to first consider M formula language, which lives behind the scenes of every applied step in Power Query.
M formula language is also known as 'M' for short, which stands for Data Mashup.
M is a functional programming language built for Power Query. You'll see this same language in Power BI's Power Query editor.
It is a case-sensitive language, and is comprised of a wide range of built-in functions. These can vary from simple steps to more complex transformations.
3. Generating M code
Power Query conveniently writes M code for us with every applied query step.
Therefore, any new column created or transformation made automatically has an associated line of M code.
We can preview the code by looking at the formula bar or opening Power Query's advanced editor. We will delve deeper into the Advanced editor in the next chapter of this course.
4. Custom Columns
Now we will switch our attention to custom columns. Custom columns are a powerful tool to apply customized transformations with broad flexibility.
Written in M language, these custom formulas enable us to extend beyond the typical functionality of built-in transformations.
This can include more advanced nested conditional logic, advanced indexing, or more complex calculations.
Let's explore some examples.
5. Nested Conditional Logic
A common custom column use case is a conditional column, which might reference one or more columns based on values of interest.
This can involve multiple levels of conditional statements stemming from If.. Then.. Else.
Custom columns let us extend logic further by adding additional logical operators, like AND and OR.
For example, a basic conditional column might check for age above 65 and the arrival mode of "Car" to define groups.
6. Nested Conditional Logic
However, extending this further, an example here shows how we can further specify the age range to group those below the age of 80 for arrival by car into a group and add a second group that identifies the same age ranges for ambulance arrivals. Any record that doesn't meet either criteria will default to "group2".
7. Advanced Indexing
Advanced indexing in Power Query involves creating custom indices or rankings based on defined groups.
8. Advanced Indexing
Let's say this column has 300 rows and three groups. If we added a standard Index column, it might look like this.
9. Advanced Indexing
However, we often need to create indices within groups, for instance, determining rankings between 1 and 100 within these three groups.
10. Advanced Indexing
We must rely on custom columns to extend beyond the basic index column function to prepare an advanced index like this.
We accomplish this in Power Query by first leveraging the Group By operation with an "All Rows" aggregation.
Grouping by "All Rows" allows you to aggregate and access values from all rows within a specific column in smaller grouped tables.
Now, the 300 rows are essentially compressed but can still be referenced with an M table function.
11. Advanced Indexing
With this grouped aggregation, we can leverage M table functions such as Table.AddIndexColumn, to create a custom index specific to grouped column values of interest.
With the grouped index added, you can then expand the rows back to their original 300-row structure.
12. Let's practice!
We will practice these functions on our hospital dataset to delve into important patient groupings.