Power BI's Power Query (M) for Python Programmers
Tags: powerbi, m, python
Published: 2021-12-02 03:32:55

## Overview 

Since [April 2019](https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2019-feature-summary/), Python has been a part of Power BI. This is great in theory. Python is a great language known for its prominence in the data science community. It has a wide array of tools to perform data wrangling, calculations, visualizations, and a whole lot more!

This is also great in practice. Analysts who have pre-existing Python code would have a relatively easy time onboarding to Power BI, not to mention the ease of development time that Python brings. However, putting together two different stack often has a lot of barriers. It could be technical, organizational, cultural, infrastructure-related, or whatever else you'd like to categorize. Point being, there is always going to be an inevitable scenario for users to pick up a software's native tools. <small>(Yes Microsoft, a lot of us still code in VBA).</small>

In this post, I will attempt to put together a *"cheat sheet"* of Power Query (M) features that Python programmers (e.g., me) would take interest in. (This serves as a disclaimer that I am also new at this).

## Power Query (M) and DAX

PowerBI comes with two built-in languages: Power Query and DAX (Data Analysis Expressions).

DAX is available in *Home > Calculations* ribbon. It is preferrable to do it on Data view (at the left side of the window, along with Report View and Model View) to unlock the New Column option. This is a sample DAX:

```
    Revenue = [Sales] * [PricePerUnit]
```

This DAX expression creates a new column called `Revenue` which is a product of the `Sales` column and the `ProductPerUnit` column. This operation should be fairly familiar to Excel users.

On the other hand,  Power Query (M) is available in the Power Query Editor which you can launch at *Home > Queries > Transform Data*. In the Power Query Editor, you may also launch the Advance Editor via *Home > Query > Advance Editor*.

`M` is the formula language behind Power Query (*i.e.,* `let` ... `in`). The actions you do in the Power Query GUI is translated to `M` behind the scenes. Fortunately, both can be used pretty much interchangeably with regards to search engine.

This is a sample `M` code:

```fsharp
let
    src = Table.FromList({
        "Foo", "Spam", "Bar", "Ham", "Baz"
    }),
    output = Table.SelectRows(
        src,
        each ([Column1] = "Spam") 
    )
in 
    output
```

The snippet shows two *steps*: step `src` to initiate a table and step `output` to filter a desired value. This step-by-step data transformation is something most programmers would be familiar with.

There's definitely going to be an overlap between what Power Query and DAX are capable of. Of course, one feature is going to be easier in the other and vice versa. But ulitimately, it will all boil down to preference.

These two videos are both wonderul resources for introduction to `M`. Both resource persons are from the Power BI Customer Advisory team. I urge every one to at least watch the first 5 or 10 minutes of either videos just so we can get some of the basics out of the way.


**Introduction to M in Power BI - Chris Webb**
{% youtube S_uLkBP4sYk %}


**Power BI Dev Camp March Intro to M Programming**
{% youtube BsgOU9eeCBg %}


Anyway, without further ado, let's jump into the content!

## Cheat Sheet: Common Types and Operations

 Python 3 | M | Remarks
---: | ---: | :---:
`# Comments` | `//single-line`, and `/* multi-line*/`
42 | 42 
-42 | -42
+42 | +42
--42 | --42 |
not 42 # False | | no truthy values for M
not 0 # True | | no falsy values for M
42 == 42 | 42 = 42
5 + 2 | 5 + 2
5 - 2 | 5 - 2 
5 \* 2 | 5 \* 2 
4 / 3 | 4 / 3 | both are 1.3333333333333333
2 // 3 | | no native floor division in `M`, but DAX has `FLOOR`
`'Hello World'` | `"Hello World"` | `M` string literal is strictly quotation mark `"`
`'Hello ' + 'World!'` | `"Hello " & "World!"` 
`'HeLLo wOrLd'.upper()` | `Text.Upper("HeLLo wOrLd")` | result is `HELLO WORLD`
`'HeLLo wOrLd'.lower()` | `Text.Lower("HeLLo wOrLd")` | result is `hello world`
`'Wo' in 'Hello World'` | `Text.Contains("Hello World", "Wo")`
`'len('Hello World')` | `Text.Length("Hello World")`
`['Foo', 'Bar']` | `{ "Foo", "Bar" }`
`['Foo', 'Bar'] + ['Spam', 'Ham']` | `{ "Foo", "Bar" }  & { "Spam", "Ham" }`
`['Foo', 'Bar', 'Baz'].pop()` | `List.RemoveLastN({ "Foo", "Bar", "Baz" })` | Python's `list.pop` modifies the list but returns the element removed. M's `List.RemoveLastN` returns the resulting list.
`range(10)` | `{ 0 .. 9}` | Python's `range` is a generator, not a list. 
`range(1,11)` | `{ 1 .. 10}`


This is not a complete list but hopefully, it should be enough for intermediate Python users to pick up the nuances between Python's data model vs M's. 

- [Docs: Text Functions in M](https://docs.microsoft.com/en-us/powerquery-m/text-functions)
- [Docs: List Functions in M](https://docs.microsoft.com/en-us/powerquery-m/list-functions)

## Cheat Sheet: is

Python's `is` operator evaluates if two objects refer to the same object.

Example Python code:

```python
a = {}
b = {}
c = a
a == b # True, both are empty dictionaries
a is b # False, both are separately initialized dictionary.
c is a # True, c refers to the same dictionary as a
```

There are nuances to it but this should illustrate the the point.

M's `is` operator is completely different. In M, `is` is used to check for type conformance.

Example M code:

```fsharp
42 is number //TRUE
```

## Cheat Sheet: Records and Tables

Python does not have a native implementation of a Record or a Table. 

`Record` is most likely inspired by the database term. It goes in the form of `[Name="Seinfeld", Age=33]`.

At first glance, `Record` looks like a Python `dict` (in that it has key-value pairs) but keys in a Python dict need to be something that's already defined. It also looks similar to `namedtuple` but a schema for a namedtuple has to be specified first (note though that defaults were added since Python 3.7); a `Record` does not have such restriction.

A Record implemented in Python would definitely look like this:

```python
class Record:
    def __init__(self, **kwargs):
        for key, value in kwargs.items():
            setattr(self, key, value)


jerry = Record(Name='Seinfeld', Age='33')
print(jerry.Name) # Seinfeld
print(jerry.Age) # 33

```

Thanks to libraries like `pandas`, Python users does have options for dealing with 2-Dimensional data.

Table is definitely the most prominent data type in Power Query. 

- [Docs: Table Functions in M](https://docs.microsoft.com/en-us/powerquery-m/table-functions)

We're not gonna go through all of the available methods but one good feature to note is that is has quite a number of class methods which can be used to instantiate a table. Here's an example.

```fsharp
let
    output = Table.FromRecords({
        [Name="Jeff Winger", Age=27],
        [Name="Britta Perry", Age=28]
    })
in
    output
```

## Cheat Sheet: Functions

Functions in Python can created (generally) with the `def` keyword, or anonymously with `lambda`.

For example:

```python
def add(addend1, addend2):
    return addend1 + addend2

print(add(4,2)) # 6

add_lambda = lambda addend1, addend2: addend1 + addend2
print(add_lambda(4,2)) # 6
```

Defining functions in `M` is similar to JS (welp, there's an arrowhead). Functions in `M` can be assigned in a separate query, or in a variable.

```fsharp
(addend1, addend2) => addend1 + addend2 
```

As far as I know, `M` does not have list comprehensions or `map` but native types should have sufficient methods for transformation.

Example: return a list containing the squares of numbers from 0 to 9.

Python:

```python
numbers = list(range(10))

def square(n):
    return n ** 2

# via comprehension
squares_comprehension = [ square(i) for i in numbers ]

# via map
squares_map = list(map(square, numbers))
```

In `M`, it is translated as:

```fsharp
let 
    numbers = { 0 .. 9 },
    square = (n) => Number.Power(n,2),
    squares_list = List.Transform(numbers, square)
in 
    squares_list
```


## Cheat Sheet: underscore (_) and each

The both underscore (_) and `each` are syntactic sugar for unary functions (functions that only has one arguments. Take a snippet the code example above.

```fsharp
square = (n) => Number.Power(n,2)
```

`square` is a function which takes a parameter `n` and returns the square if `n`. It is possible to substitute `n` with an underscore. This is usually common when functions are passed anonymously.

For example, the code above can be re-written as:

```fsharp
List.Transform(
    { 0 .. 9},
    (_) => Number.Power(_,2)
)
```

It is also further possible to simplify using the `each` keyword

```fsharp
List.Transform(
    { 0 .. 9},
    each Number.Power(_,2)
)
```

Do take note that underscore can represent any data type.

```fsharp
let
    src = Table.FromRecords({
        [Name="Foo"],
        [Name="Bar"],
        [Name="Baz"],
        [Name="Spam"],
        [Name="Ham"]
    }),
    out = Table.SelectRows(
        src,
        each Text.StartsWith([Name], "B") // (_) => Text.StartsWith(_[Name], "B")
    )
in
	out
```

## Cheat Sheet: typing in M

`M` can be strongly typed. Take below code for example.

```fsharp
let
    add = (addend1, addend2) => try addend1 + addend2 otherwise addend1 & addend2,
    output = add("1", "2")
in
    output
```

In this example, we passed Text-types `"1"` and `"2"` to our custom function `add`. The function `add` will try to add the two parameters if possible, or else, it will attempt to concatenate.. `M` will not type case `"1"` and `"2"` into number types. Hence, the result will be`"12"` (Text).

One can modify the function by specifying the types the function is expected to get.

```fsharp
let
    add = (addend1 as number, addend2 as number) => try addend1 + addend2 otherwise addend1 & addend2,
    output = add("1", "2")
in
    output
```

The code above will throw an Error.

It is also possible to define some sort of a schema when creating custom tables.

```fsharp
let
    schema = type [Name=text, Age=number],
    my_table = type table schema,
    output = #table(
        my_table,
        {
            {"Jeff Winger", 27},
            {"Britta Perry", 28}
        }
    )
in
    output
```

Unlike in the earlier example where we used `Table.FromRecords` to instantiate the table, this method does not require users to type the attributes of each item.

## Conclusion

Power BI is an awesome software that Python programmers can add to their arsenal. One of its tools, `M`, is a mature query language which can go toe to toe with Python packages like `pandas`.

---

**Edit**

**Community Contribution:**
Would like to thank u/MonkeyNin for contributing some topics [on r/PowerBI](https://www.reddit.com/r/PowerBI/comments/r8qyuz/power_bis_power_query_m_for_python_programmers/). You may refer to their [gist](https://gist.github.com/ninmonkey/389d2790181e4d09fb6aa5b1e74362de) where they cover additional topics such as Selection and Projection Operators and more!