another great video by ChrisWeb:
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 numbermeans the user can pass any number, or,nulloptionalmeans parameter is optional. Without that, the user would have to explicitly pass anull??is theNull coalescing operatordocs: ?? 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
not have a native implementation of a Record or a Table.
-
in PQ there are primitive types for
table,record, etc. -
the
tabletype is abstract, with no literala = #table(), b = [], c = {},
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}
PQ
isis 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
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"
As far as I know, M does not have list comprehensions or map but native types should have sufficient methods for transformation.
formap()` see List.Transform()- List comprehension is kind of like:
List.Transform,List.TransformMany,List.GenerateandTable.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:
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
]
)
Other places it makes sense to return null instead of throwing an error.
The caller could use
maybeList = getListOfUsersPerCompany(),
users = maybeList ?? {}
But, sometimes
I recommend anything by Chris Web and Ben Gribido