DEV Community: Marvin

Is Python interpreted or compiled? On Doubling Down and Semantics.

Marvin — Sun, 05 Jun 2022 17:13:15 +0000

This is a personal blog than a technical one.

It was a lovely Sunday night. Someone on a programming Discord posted an article about a Python to C++ transpiler. I knew this sentiment would come up.

"Still, Python is way slower because of the massive overhead of being an interpreted language..."

Anyone in the tech space has probably heard and read that Python is slow. It is. This picture below should paint a good enough picture of that.

In the picture, also look at Node JS. JavaScript isn't compiled, but can toe-to-toe with speeds compared to compiled languages. This picture is a slide from Anthony Shaw's Restarting Pyjion, a general purpose JIT for Python- is it worth it?

Anthony Shaw is a Microsoft employee and author of CPython Internals. He has also given another talk Why is Python Slow?. I would think he's someone who knows what he's talking about.

On both talks, Anthony has given a good introduction to how Python runs code under the hood. tl;dw: Your code gets converted through multiple steps all the way down to bytecode, which is then continually evaluated by the Python interpreter.

The compilation step to bytecode, which is done ahead-of-time (AOT), could be made significantly faster if it would have been done just-in-time (JIT) instead. In fact, projects such as PyPy (with JIT) and Pyjion use JIT compilation.

Python's performance problems come from it being AOT compiled and less from it being interpreted.

- me

That's what I said. Verbatim. And it looks like I rattled some nest.

"Compiled?"

"Compiled???"

And especially this guy.

Compiled?

Python is compiled?

If Python is compiled, then boy... do I have a bridge for you.

That was indeed, an invitation for engagement. So I replied,

Language -> AST -> CFG -> bytecode -> ceval.c

.pyc files are basically compiled bytecode

But go ahead and sell me that bridge.

I don't think he has sold me that bridge, especially if his reply consist of him saying that his "personal definition" of compilation refers to compilation to machine-level compilation. Arguing with a stranger's "personal definitions" over the internetz would be a really productive way to spend a Sunday night, wouldn't it? Improving Python's performance was the topic I really hoped to have after all.

I would just leave here a snippet from the sidebar of the Wikipedia article on Ahead-of-Time Compilation.

Notable Runtimes

Android Runtime (ART)

Common Language Runtime (CLR) and Mono

crt0

Java virtual machine (JVM)

Objective-C and Swift

V8 and Node.js

CPython and PyPy

Zend Engine (PHP)

LuaJIT

If anything, I believe now more than ever that finding a specialist on a programming language do have value.

Quick Peek Under the Hood: Indexing and Slicing in Python

Marvin — Sun, 13 Mar 2022 17:36:20 +0000

Overview

In Python, indexing and slicing are ways to get elements from iterables (like lists, tuples, and even string).

ingredients = ['beef', 'onions', 'tomato', 'potato', 'carrot']
print(ingredients[1])    # onions
print(ingredients[1:4])  # ['onions', 'tomato', 'potato']

This is something I have to look at recently. I am personally not a fan of deep dives in the C-level source code since things can always change (for example, ROT_TWO is set to be replaced in Python 3.11). Nevertheless, I think reading C code as a Python developer is a fun experience I want to share.

Below codes are performed on Python 3.10.

AST

In CPython, actual Python syntax go through parsing and compiling.

Python code gets parsed and converted into an abstract syntax tree. The ast module can help us illustrate this step.

import ast

print(ast.dump(ast.parse('[][0]', mode='eval'), indent=4))
# Result:
# Expression(
#     body=Subscript(
#         value=List(elts=[], ctx=Load()),
#         slice=Constant(value=0),
#         ctx=Load()))

print(ast.dump(ast.parse('[][::-1]', mode='eval'), indent=4))
# Result: 
# Expression(
#     body=Subscript(
#         value=List(elts=[], ctx=Load()),
#         slice=Slice(
#             step=UnaryOp(
#                 op=USub(),
#                 operand=Constant(value=1))),
#         ctx=Load()))

What we see in the two code snippets above is that both indexing and slicing are essentially the same operation, Subscript.

Subscript takes a value, a slice, and a ctx. We can learn from here that the difference between indexing and slicing is that:

indexing is when a Constant tree is passed into the Subscript's slice; whereas
slicing is when a Slice tree is passed otherwise.

Bytecode

The abstract syntax tree as shown above will eventually get compiled into bytecode. The dis module help us see it.

from dis import dis

dis('[][0]')
# Result: 
#   1           0 BUILD_LIST               0
#               2 LOAD_CONST               0 (0)
#               4 BINARY_SUBSCR
#               6 RETURN_VALUE

dis('[][::-1]')
# Result: 
#   1           0 BUILD_LIST               0
#               2 LOAD_CONST               0 (None)
#               4 LOAD_CONST               0 (None)
#               6 LOAD_CONST               1 (-1)
#               8 BUILD_SLICE              3
#              10 BINARY_SUBSCR
#              12 RETURN_VALUE

What we're seeing in these snippets is that Subscript is referred to as BINARY_SUBSCR at bytecode level. We can also see that if any of start, stop, or step is not supplied, None is put in place.

From the docs: the Slice Object

Part of the Python's builtins is the slice function which generates the

From the docs:

Slice objects are also generated when extended indexing syntax is used. For example: a[start:stop:step] or a[start:stop, i].

We can infer that when slicing ingredients[1:4], a slice(1,4) is generated along the way.

Under the C

The bytecodes such as BUILD_SLICE and BINARY_SUBSCR can be found at Python's ceval.c

Below are snippets:

TARGET(BINARY_SUBSCR) {
    PREDICTED(BINARY_SUBSCR);
    PyObject *sub = POP();
    PyObject *container = TOP();
    PyObject *res = PyObject_GetItem(container, sub);
    Py_DECREF(container);
    Py_DECREF(sub);
    SET_TOP(res);
    if (res == NULL)
        goto error;
    JUMPBY(INLINE_CACHE_ENTRIES_BINARY_SUBSCR);
    DISPATCH();
}

TARGET(BUILD_SLICE) {
    PyObject *start, *stop, *step, *slice;
    if (oparg == 3)
        step = POP();
    else
        step = NULL;
    stop = POP();
    start = TOP();
    slice = PySlice_New(start, stop, step);
    Py_DECREF(start);
    Py_DECREF(stop);
    Py_XDECREF(step);
    SET_TOP(slice);
    if (slice == NULL)
        goto error;
    DISPATCH();
}

Some Stuff

As stated above, it's indexing if a Contstant (i.e., int) is passed; whereas it's slicing if a Slice is passed.

Can we pass a slice() in __getitem__?

# Turns out, yes
# 
print(ingredients[slice(1,4)])
# Result:
# ['onions', 'tomato', 'potato']
# 
print(ingredients.__getitem__(slice(1,4)))
# Same result

From this line from the C code:

PyObject *res = PyObject_GetItem(container, sub);

we can infer that PyObject_GetItem is the C equivalent of __getitem__ from the data model. We now know that indexing, slicing and getting a value from a dictionary are all just __getitem__. This should not be a surprise considering the syntax, but I am.

(As a side note: if I had just read the Data Model docs, this readings done to make this post wouldn't have been necessary.)

Some implementations for the slice object is in Objects/sliceobject.c. There's a lot going in here.

In one of the snippets above, it is PySlice_New which creates a new slice object (it's been around since it was first introduced).

Looking at this commit, it seems that PySlice_New works like a def __init__ which simply returns a PyObject struct with attributes start, stop, and step. Calculations happen elsewhere. For example, the pointer *start is calculated on five occasions.

if (r->start == Py_None) {
    *start = *step < 0 ? length-1 : 0;
} else {
    if (!PyLong_Check(r->start)) return -1;
    *start = PyLong_AsSsize_t(r->start);
    if (*start < 0) *start += length;
}

if (r->start == Py_None) {
    *start = *step < 0 ? PY_SSIZE_T_MAX : 0;
}

if (*start < 0) {
    *start += length;
    if (*start < 0) {
        *start = (step < 0) ? -1 : 0;
    }
}
else if (*start >= length) {
    *start = (step < 0) ? length - 1 : length;
}

I don't think I need to understand this yet. Moreso, there's a lot of pointers at play in order to really read it, you need to stop thinking in Python and start thinking in C.

Another cool thing to note is a factory function at the bottom called slice_new, which, looking at the pull request, seem to be the slice builtin.

Notes on Excel Dynamic Arrays

Marvin — Wed, 23 Feb 2022 14:30:50 +0000

Overview

Dynamic arrays are formulas that can return arrays of variable size. Dynamic arrays have been made available in Excel 2021 (and hence Excel 365).

Prior to dynamic arrays, there were static arrays, aka CSE arrays ( because you have to press Ctrl+Shift+Enter).

One of the benefits of dynamic arrays is that it is calculated at runtime.

References:

TRANSPOSE

	A	B	D
1	Apple	10	`=TRANSPOSE($A$1:$B$6)`
2	Bottom	20
3	Jeans	30
4	Jeans	40
5	Boots	50
6	Fur	60

Result:

	D	E	F	G	H	I
1	Apple	Bottom	Jeans	Jeans	Boots	Fur
2	10	20	30	40	50	60

UNIQUE

	A	C
1	Apple	`=UNIQUE($A$1:$B$6)`
2	Bottom
3	Jeans
4	Jeans
5	Boots
6	Fur

Result:

	C
1	Apple
2	Bottom
3	Jeans
4	Boots
5	Fur

SORT

	A	C
1	Apple	`=SORT(UNIQUE($A$1:$B$6))`
2	Bottom
3	Jeans
4	Jeans
5	Boots
6	Fur

Result:

	C
1	Apple
2	Boots
3	Bottom
4	Fur
5	Jeans

FILTER

	A	C
1	Apple	`=FILTER($A$1:$B$6, LEFT($A$1:$B$6, 1)="B")`
2	Bottom
3	Jeans
4	Jeans
5	Boots
6	Fur

Result:

	C
1	Bottom
2	Boots

Matrix Multiplication

Scalar Multiplication

	A	B	C	D
1	1	2		`=$A$1:$B$2*2`
2	3	4

Result:

	D	E
1	2	4
2	6	8

Matrix x Matrix

	A	B	C	D	F	G	I
1		AUD	GBP	EUR	FX Rates		Into USD
2	Product 1	1,000	0	0	AUD	0.7	`=MMULT($B$2:$D$4, $G$2:$G$4)`
3	Product 2	0	1,000	0	GBP	1.3
4	Product 3	0	0	1,000	EUR	1.1

Result:

	I
1	Into USD
2	700
3	1300
4	1100

Case Study

Grouping non-contagious cells with FILTER

	A	B	C	D	E	F	G	H
1	Student	English Grade	Units	Science Grade	Units	Math Grade	Units	Count of >95
2	Adam	85	1.0	97	1.8	95	1.0	???

Problem: Count all of Adam's grade which are at least 95.

Solution

=LET(grades, FILTER($B2:$G2, {1,0,1,0,1,0}), greater, FILTER(grades, (grades>=95)), COUNT(greater))

Breakdown

LET
name1: grades
value   = FILTER($B2:$G2, {1,0,1,0,1,0})
        = {85, 97, 95}

name2: greater
value   = FILTER(grades, (grades>=95))
        = FILTER({85, 97, 95}, (grades>=95))
        = FILTER({85, 97, 95}, {FALSE, TRUE, TRUE})
        = {97, 95}

calculation: COUNT(LEN(greater))
= COUNT({97, 95})
= 2

Calculating differences between columns

	A	B	C	P
1		2022-02-17	2022-02-18	Day on Day
2	Sales	925,000	1,050,000	`=LET(currentColRef, COUNTA($A$2:$N$2), currentRevenues, INDIRECT(CONCAT("R2C", currentColRef,":R3C", currentColRef), FALSE), currentRevenues - OFFSET(currentRevenues,0,-1))`
3	Other Revenues	-	5,000

Breakdown

LET
currentColRef = number of non-empty cells in $A$2:$N$2
        = 3

currentRevenues = values at cells in currentColRef
        = {1 050 000, 50 000}

calculation:
        currentRevenues - column right before currentRevenues
        = {1 050 000, 50 000} - {925 000, 0}
        = {125 000, 5 000}

Result

	P
1	Day on Day
2	125,000
3	5,000

How to Put Keyword Arguments in your Python Class Definitions

Marvin — Sat, 15 Jan 2022 12:40:03 +0000

I have recently looked at using a library called SQLModel. SQLModel is an ORM being developed by the same guy behind FastAPI, Sebastián Ramírez. This library combines pydantic and SQLAlchemy to give users a new and hopefully better way to define their Models.

This is an example model that inherits from SQLModel.

from sqlmodel import SQLModel, Field

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True) 
    name: str 
    secret_name: str 
    age: int | None

Not gonna lie, defining database models the same way you would a dataclass sounds cool. Now, take a closer look at this syntax table=True beside the class definition. This technique is new to me (and off the record guys, I had to read through some SQLALchemy codes for a few hours simply because I explicitly forgot to put it in my model).

When you think about it, ORM models are definitely one of the places where it makes sense to put keyword arguments in the class definition itself. But how would one do it? How would someone prompt users to put a keyword argument in their class definitions?

Using a Metaclass

As the saying goes, if you have to ask why you would need a metaclass, then you probably don't need it. But now that we have a use case, let's have a discussion.

Refer to the code below:

class StudentMeta(type):
    def __new__(cls, *args, **kwargs):
        print(f"{args = }")
        print(f"{kwargs = }")
        return super().__new__(cls, *args)

class Student(metaclass=StudentMeta, table=True):
    ...

troy = Student()

StudentMeta is our metaclass, Student is our class, and troy is a Student object.

Below is the output of the code:

args = ('Student', (), {'__module__': '__main__', '__qualname__': 'Student'})
kwargs = {'table': True}

Note that args is a tuple with 3 elements. Traditionally, these are called name, bases, and dict (not to be confused with the dictionary built-in).

You may go further and run the following:

print(f"{troy.__class__.__name__ = }")
print(f"{troy.__class__.__bases__ = }")
print(f"{troy.__class__.__dict__ = }")

# Output should be:
# troy.__class__.__name__ = 'Student'
# troy.__class__.__bases__ = (<class 'object'>,)
# troy.__class__.__dict__ = mappingproxy({'__module__': '__main__', '__dict__': <attribute '__dict__' of 'Student' objects>, '__weakref__': <attribute '__weakref__' of 'Student' objects>,'__doc__': None})

These args are the required arguments for when constructing a new type.

What's happening here is that similar to how 42 is an object of type int, and int is a class of type...

troy is an object of type Student;
Student is a class of type StudentMeta;
StudentMeta is a class of type.

StudentMeta is undergoing a normal type creation, except that we have it modified to print all args and kwargs used in the instantiation. Take note that the return value of __new__ is return super().__new__(cls, *args). This is because type(...) require only the name, bases, and dict to create a new type.

That leaves us with kwargs. Do note that print(f"{kwargs = }") was triggered when Student was defined.

Using initsubclass__

__init__subclass__ use subclassing to modify the behavior of a Parent's subclass.

class Base:
    def __init_subclass__(cls, *args, **kwargs) -> None:
        print(f"{args = }")
        print(f"{kwargs = }")
        super().__init_subclass__()

class Student(Base, table=True):
    ...

abed = Student()

In the example above,

abed is an object of type Student;
Student is a class of type;
Base is a class of type.

And the output is below:

args = ()
kwargs = {'table': True}

There are no args because we did not have to create a new type; __init__subclass__ only modifies the creation of a new child class (which is what we did in the earlier example). Note that __init__subclass__ does not have a return statement.

Not only that but ...

print(f"{abed.__class__.__name__ = }")
print(f"{abed.__class__.__bases__ = }")
print(f"{abed.__class__.__dict__ = }")

# Output should be:
# abed.__class__.__name__ = 'Student'
# abed.__class__.__bases__ = (<class '__main__.Base'>,)
# abed.__class__.__dict__ = mappingproxy({'__module__': '__main__', '__doc__': None})

Putting it all together

In this article, we covered two ways to use keyword arguments in your class definitions. Metaclasses offer a way to modify the type creation of classes. A simpler way would be to use __init__subclass__ which modifies only the behavior of the child class' creation.

Regardless of the method, these keyword arguments can only be used during the creation of a class.

First-timing DAX

Marvin — Sun, 26 Dec 2021 10:20:08 +0000

A few weeks ago, I wrote a blog about my first time encounter with Power Query.

In this article, I will outline my blind experience with DAX as a first-time user. We will be solving a simple problem and walk through how I got to the solution.

Is Power BI user-friendly? Is DAX beginner-friendly? How far can you go without reading the docs? Continue reading to find out

Case Study

Input

We will be using the following data:

Date	Ticker	Price
2021-12-23	AAPL	275.0
2021-12-23	GOOG	2940
2021-12-24	AAPL	276.5
2021-12-24	GOOG	2941

You may enter below code on Power Query to load it in Power BI. I will name the table Stonks.

Table.FromRecords({
    [Date="2021-12-23", Ticker="AAPL", Price=275.0],
    [Date="2021-12-23", Ticker="GOOG", Price=2940],
    [Date="2021-12-24", Ticker="AAPL", Price=276.50],
    [Date="2021-12-24", Ticker="GOOG", Price=2941]
})

Output

Create a new measure that calculates the day-on-day change. For bonus points, we would like to display an indicator on whether that change is positive (▲) or negative (▼).

I'll be using both Text Box and Table do display these measures on a Power BI page.

Step Zero: What I know

In my previous blog, I wrote a one-liner code depicting DAX.

    Revenue = [Sales] * [PricePerUnit]

I mentioned that

This operation should be fairly familiar to Excel users.

This is what I mean by that.

	A	B	C
1	Sales	PricePerUnit	Revenue
2	100,000	2	=A2*B2
3	50,000	2.1	=A3*B3

I also mentioned that

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 ultimately, it will all boil down to preference.

One Redditor pointed out that actually, one does not want to use Power Query for running analytics. I agree; even though I haven't used DAX, I have already gotten a feel on how Power Query handles a large amount of data.

I have also skimmed DAX Overview Official Docs. I saw something about DAX Studio so I downloaded it.

Breaking the problem into smaller parts

Our problem statement is: calculate the day-on-day change of AAPL's price.

To do that we need to:

get AAPL's current price (276.50)
get AAPL's previous price (275.0)
subtract the previous price from the current price

Converting these steps into some technical tasks will simplify it even further.

get a value from the table
perform arithmetic operation

But then how do we get 275 from the table?

Should we Filter the table so that only AAPL will remain?

Problem Solving

Feel free to skip this section if you want to go straight ahead to the solution.

The UI

First things first: the User Interface (UI). Creating a new table based on Stonks and filtering it does NOT work.

Right-clicking 275 and selecting New Measure... also does NOT work.

Filter

If only we can filter Stonks[Date] and Stonks[Ticker], and then maybe we can get the value from Stonks[Price]

ALAS! There's a FILTER function.

The FILTER function which goes like this: FILTER(Table, FilterExpression)

MyNewTable =
FILTER(
    Stonks,
    AND(
        Stonks[Ticker]=="AAPL",
        Stonks[Date]=="2021-12-23"
    )
)

The result is the first row of Stonks

Date	Ticker	Price
2021-12-23	AAPL	275.0

This is nice. Now, all we have to do is to reference the MyNewTable[Price], right? Right???

DAX Studio

DAX Studio is cool and intuitive. It features a Query Builder which enables users to drag-and-drop fields from the Power BI dataset and do the Query Building for the user. DAX Studio also links you to a site called dax.guide.

The Query Builder is very similar to Excel's Pivot Table Field List.

I dragged Price to the Columns/Measures pane and on Filters, Date is 2021-12-23 and Ticker is AAPL.

The Run Query button returned the following table:

Price
275

and the Edit Query button returned the following query:

/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
    Stonks[Price],
    KEEPFILTERS( TREATAS( {"2021-12-23"}, Stonks[Date] )),
    KEEPFILTERS( TREATAS( {"AAPL"}, Stonks[Ticker] ))
)
ORDER BY 
    Stonks[Price] ASC
/* END QUERY BUILDER */

Knowing SQL, I know it's safe to remove the ORDER BY "clause".

My Solution

My problem with FILTER and SUMMARIZECOLUMNS is that they create new tables, which clutters up my namespaces. The reason I specified that I need a "new measure" is because I want my values to be associated with the Stonks table (and rightfully so).

FILTER and SUMMARIZECOLUMNS gives us tables, hence why those cannot be used for measures, or at least blanketly. For some reason, I cannot reference the price in the text box when using the FILTER solution.

The query as returned by the Query Builder also cannot be completely used right off-the-bat (Power BI throws errors). It has something to do with EVALUATE. I've yet to fully understand it but the way it works is that the above query can be used as follows:

AnotherTableFromEvalute = 
SUMMARIZECOLUMNS(
    Stonks[Price],
    KEEPFILTERS( TREATAS( {"2021-12-23"}, Stonks[Date] )),
    KEEPFILTERS( TREATAS( {"AAPL"}, Stonks[Ticker] ))
)

Or like this:

MyTableFromAssignedVar = 
VAR
 ThisIsTheVariable =
    SUMMARIZECOLUMNS(
        Stonks[Price],
        KEEPFILTERS( TREATAS( {"2021-12-23"}, Stonks[Date] )),
        KEEPFILTERS( TREATAS( {"AAPL"}, Stonks[Ticker] ))
    )
RETURN
    ThisIsTheVariable

After a few reading through some docs, I thought that instead of reducing my original table to smaller parts, maybe I could just crawl through the values of my original table, like a VLOOKUP! Fortunately enough, there's a LOOKUPVALUE function.

LOOKUPVALUE returns a value, so it could be used as a basis of a measure.

AAPL_DoD = 
VAR 
    prev = 
        LOOKUPVALUE(
            Stonks[Price],
            Stonks[Date], "2021-12-23",
            Stonks[Ticker], "AAPL"
        )
VAR
    curr = 
        LOOKUPVALUE(
            Stonks[Price],
            Stonks[Date], "2021-12-24",
            Stonks[Ticker], "AAPL"
        )
RETURN
    curr - prev

This solution could still use some refactoring to make it more generalizable but that would be a topic for another day.

BONUS: arrow indicators. I saw this thread solving this exact problem, actually.

AAPL_DoD_arrow = 
 IF(
     [AAPL_DoD]>0,
     UNICHAR(9650),
     UNICHAR(9660)
 )

Closing Thoughts

It's a holiday so I'll write these in a list.

You don't have to be a walking documentation. Knowing where to read and when, that is a skill.
~~Google~~ Search engines are your friend.
Still... expericence > Official docs and dax.guide > search. As with any other tools, building your experience would be the best way to go forward.
The official docs made a point that to use DAX queries, it's advisable to first have a solid foundation on the basics. I kind of agree but as a big picture guy, I'm glad to see things like EVALUATE and how different things can be between Query Builder and Power BI itself.
I used to think that DAX is just Excel++. Now, I think DAX requires you to adopt its own mental model. Instead of DAX tapping on Excel as its base, it's actually more like DAX and Excel shared a common ancestor and now, it is in a league of its own.

Power BI's Power Query (M) for Python Programmers

Marvin — Thu, 02 Dec 2021 03:32:55 +0000

Overview

Since April 2019, 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. (Yes Microsoft, a lot of us still code in VBA).

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:

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

Power BI Dev Camp March Intro to M Programming

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.

Cheat Sheet: is

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

Example Python code:

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:

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:

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

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.

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:

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.

(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:

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:

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.

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:

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

It is also further possible to simplify using the each keyword

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

Do take note that underscore can represent any data type.

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.

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.

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.

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. You may refer to their gist where they cover additional topics such as Selection and Projection Operators and more!

Python's print and the flush parameter.

Marvin — Sat, 20 Nov 2021 15:10:13 +0000

Overview

If you've been in touch with the Python community relatively recently, you've probably heard of rich. rich brings colors to the terminal... without much of a hassle.

These two lines show rich.print overriding Python's built-in print function. One thing that caught my attention is rich.print's docstring, especially this line:

flush (bool, optional): Has no effect as Rich always flushes output. Defaults to False.

I seldomly use this parameter. But I do remember one place where I've seen it — pyautogui's mouse documentation where Al Sweigart gave this code example which shows the coordinates of your cursor:

    #! python3
    import pyautogui, sys
    print('Press Ctrl-C to quit.')
    try:
        while True:
            x, y = pyautogui.position()
            positionStr = 'X: ' + str(x).rjust(4) + ' Y: ' + str(y).rjust(4)
            print(positionStr, end='')
            print('\b' * len(positionStr), end='', flush=True)
        except KeyboardInterrupt:
            print('\n')

So, really. What does flush do?

What does flush do?

Without further circumlocutions, let's go ahead and run this script.

flush does not affect the output. What it changes is the manner in which it is printed.

Here are my findings:

flush=False (yes, the default) will wait for the line to complete before printing it.
flush=True will force our terminal to print it.
flush does not matter if end=\n.

Now this begs the question, why? If I have end='', why do I have to explicitly set flush=True?

print, sys.stdout, and PYTHONUNBUFFERED

Below is the first sentence from Python's print documentation:

print(*objects, sep=' ', end='\n', file=sys.stdout, flush=False)
Print objects to the text stream file, separated by sep and followed by end.

To be honest, when I first wrote my Hello World program in Python, I didn't go through the documentation to get awed by its implementation. Now that I've seen it, I can infer that the terminal output is a "text stream" which is sys.stdout by default.

From the docs:

stdout is used for the output of print() and expression statements and for the prompts of input()...

These streams are regular text files like those returned by the open() function...

When interactive, the stdout stream is line-buffered. Otherwise, it is block-buffered like regular text files. You can make both streams unbuffered by passing the -u command-line option or setting the PYTHONUNBUFFERED environment variable.

There's apparently a deep rabbit-hole to dig into this. More experienced programmers will probably recognize PYTHONUNBUFFERED in their Dockerfiles. We also probably want to know what's the difference between line-buffered and block-buffered. Personally, I was aware of sys.stdout.write existing.

But for now, we can be comfortable knowing that terminal output is pretty much like a regular text file being read from somewhere. Hence, it makes sense that print, by default, would prefer to complete a line first before giving an output.

But how does this affect flush?

Setting PYTHONUNBUFFERED via command line. I use Windows so in PowerShell, it's something like $env:PYTHONUNBUFFERED = "TRUE". This overrides flush=False. It also overrides os.unsetenv.
The os module (i.e., os.environ['PYTHONUNBUFFERED'] = "TRUE") does NOT override flush=False.
Via python -u flag. The -u flag overrides flush=False regardless of PYTHONUNBUFFERED value.

Now what?

Let us go back to Al Sweigart's mouse code. This line is really interesting:

print('\b' * len(positionStr), end='', flush=True)

Spoiler alert: you may delete the flush=True keyword argument. The character \b is backspace and already enough to delete positionStr. It's probably there only to show some parallelism between this and the Python 2 code right beneath it. Nevertheless, you may play with this line — change end, modify len(positionStr), you may even change the loop.

But what would flush do here? Hopefully, this article has given you the first step in the right direction.

DEV Community: Marvin

Is Python interpreted or compiled? On Doubling Down and Semantics.

Quick Peek Under the Hood: Indexing and Slicing in Python

Overview

AST

Bytecode

From the docs: the Slice Object

Under the C

Some Stuff

Notes on Excel Dynamic Arrays

Overview

TRANSPOSE

UNIQUE

SORT

FILTER

Matrix Multiplication

Scalar Multiplication

Matrix x Matrix

Case Study

Grouping non-contagious cells with FILTER

Calculating differences between columns

How to Put Keyword Arguments in your Python Class Definitions

Using a Metaclass

Using __init__subclass__

Putting it all together

First-timing DAX

Case Study

Input

Output

Step Zero: What I know

Breaking the problem into smaller parts

Problem Solving

The UI

Filter

DAX Studio

My Solution

Closing Thoughts

Links

Power BI's Power Query (M) for Python Programmers

Overview

Power Query (M) and DAX

Cheat Sheet: Common Types and Operations

Cheat Sheet: is

Cheat Sheet: Records and Tables

Cheat Sheet: Functions

Cheat Sheet: underscore (_) and each

Cheat Sheet: typing in M

Conclusion

Python's print and the flush parameter.

Overview

What does flush do?

print, sys.stdout, and PYTHONUNBUFFERED

Now what?

List of links

Using initsubclass__