Skip to content

Instantly share code, notes, and snippets.

@ninmonkey
Last active Dec 5, 2021
Embed
What would you like to do?
few notes.md

another great video by ChrisWeb:

truthty

42 = 42

You can add that unlike Python, PowerQuery uses the operator = for both [1] assignment and [2] equality, like

No truthy values

Think maybe sounds like comparing logical vs boolean, I would emphasize that types don't coerce as implicitly as languages like JavaScript or Python

x = 10 = 10

python

x = if f None or "default"

powerquery

source = null,
x = source ?? "default",

python

def query(source = None)
    if source is None:
        source = "default"

powerquery

query = source ?? "default"

allowing

encode_text = (source as text, optional encoding as nullable number) => 
    let 
        encoding = encoding ?? TextEncoding.Utf8,
        byte_str = Text.ToBinary( source, encoding )
    in 
        byte_str,

In Python terms, the value encoding is shadowing the outer scopes's encoding. Notice that

  • nullable number means the user can pass any number, or, null
  • optional means parameter is optional. Without that, the user would have to explicitly pass a null
  • ?? is the Null coalescing operator docs: ?? operator

Python's range is a generator, not a list

That's an important point,You could give the example

PQ

{0..10}

is python

list(range(11))

A similar concept to generators in Power Query is lazy* evaluation. Imagine a was something hugely expensive to calculate. In this query, a is never executed.

let
    a = {0..10000000},
    b = "blue"
in 
    b

native types

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

records

but keys in a Python dict need to be something that's already defined

I'm not sure what direction you're going, it kind of sounds like there might be a misunderstanding, or I could be reading it wrong. Is it that you can't add new values in PQ? Or that you can't mix key types in Python?

>>> d = {}
>>> d["x"] = 10
>>> d[23] = 20
>>> d[None] 30    
# {'x': 10, 23: 20, None: 30}

is

PQ is is used to check for type compatibility

Also note that it checks if the primitive datatype is compatible. type checking in the language only requires primitive types to be compatible, it doesn't check the ascribed types like Int64 or Currency

misc

this

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

can also be written with an ascribed type / schema

let
    Source = Table.FromRecords(
        {    
            [ Name = "Jeff",   Age = 27 ] ,
            [ Name = "Britta", Age = 28 ]
        },
        type table[Name=text, Age=Int64.Type],
        MissingField.Error
    )
in
    Source

Warning that's ascribed, this would still run

let
    Source = Table.FromRecords(
        {    
            [ Name = "Jeff",   Age = 27 ] ,
            [ Name = "Jeff", Age = "stuff" ]
        },
        type table[Name=text, Age=Int64.Type],
        MissingField.Error
    )
in
    Source

compared to this, which will error

    let
        Source = Table.FromRecords(
            {    
                [ Name = "Jeff",   Age = 27 ] ,
                [ Name = "Jeff", Age = "stuff" ]
            },
            type table[Name=text, Age=Int64.Type],
            MissingField.Error
        ),
        #"Changed Type" = Table.TransformColumnTypes(
            Source,
            {
                {"Name", type text},
                {"Age", Int64.Type}
            }
        )
    in
        #"Changed Type"

More

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

  • for map()` see List.Transform()
  • List comprehension is kind of like: List.Transform, List.TransformMany, List.Generate and Table.AddColumn

Check out Ninmonkey.PowerQueryLib/Readme.For-Loops-in-PowerQuery.md

The both underscore (_) and each are syntactic sugar for unary functions

Perfect. Specifically it's implicitly the function

each [Y]


(_ as any) as any =>
    _[Y]

ie

(x as any) as any =>
    x[Y]

Table.AddColumn in the UI uses each, and ignores the final arg, so columns become type any. Maybe that's intentional, to direct the user to use Table.TransformColumnTypes

That's why people can get ex: a number in boolean columns, or all blanks or text in a number column -- it looks fine until it's loaded into the data model. If it's supposed to be an integer, I'd use

let
    source = #table({"EmployeeId"}, {{40}}),
    s = Table.AddColumn(
        source, "Some Integer",
        (row) as number => 
            row[EmployeeId],
        Int64.Type
    )
in 
    s

note, as asserts primitive type number, but not Int64. For the language specs/grammar on each, see:

try catch otherwise

usually I use don't use the try -> otherwise, instead try, because it's like a catch-all.

try:
    # web request here
    1 / 0
    # more using web request
except:
    print("bad stuff")        

Now I don't know if it's a web error, or my transforms. This example does use otherwise, but turns it into an Error.Record with the cause. Then the user can see what values broke, and act on that.

// originally based on: <https://xxlbi.com/blog/converting-hexadecimal-to-decimal-numbers-in-power-query/>
conversion = try List.Sum(DigitConversion) otherwise error Error.Record(
    "TypeConversionFailed", "Unable to convert",
    [
        Input = Input,
        Base = Base,
        DigitConversion = DigitConversion
    ]
)

functions

Other places it makes sense to return null instead of throwing an error. The caller could use

maybeList = getListOfUsersPerCompany(),
users = maybeList ?? {}

But, sometimes

resources

I recommend anything by Chris Web and Ben Gribido

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment