Power Query

SEO optimize for question: When does Table.TransformRows return a table

TODO: Write article about loadin files from folder and the secons parameter in the function. Nothin on internet describes the second parameter! Folder.Files(path as text, optional options as nullable record) as table

Many function tak an optional input parameter of type optional "options as nullable record". what is "options as nullable record" and where can we view and read more about them)

`Null	null
Logical	true    false
Number	0    1    -1    1.5    2.3e-5
Time	#time(09,15,00)
Date	#date(2013,02,26)
DateTime	#datetime(2013,02,26, 09,15,00)
DateTimeZone	#datetimezone(2013,02,26, 09,15,00, 09,00)
Duration	#duration(0,1,30,0)
Text	"hello"
Binary	#binary("AQID")
List	{1, 2, 3}
Record	[ A = 1, B = 2 ]
Table	#table({"X","Y"},{{0,1},{1,0}})
Function	(x) => x + 1
Type	type { number }    type table [ A = any, B = text ]`

The Not-a-Number value (#nan), often abbreviated NaN. NaNs are produced by invalid floating-point operations, such as dividing zero by zero.

THINGS to add: Character ? - seems to when used with each and columns seems to ignore error if column do not exist

Table.SelectRows(TABLE, each [Attributes]?[Hidden]? <> true)

If [Attributes] do not exist this function seems to return true so called optional-field-selection as follows, which returns null, if the selected field doesn’t exist

Very useful about functions. Call back function can be defined as step: Example

let
Table = ... AddOne = (x as number) as number => x + 1,
//additional expression steps
Result = Table.TransformTableRows(Table, each AddOne([Age]?+5))
in
Result

How to End a Query if Condition is Met I am looking for a solution that says: if <condition> then <perform action> else <end query> Is this possible in M?

Yes, it is not very straight forward to understand nor clear, but the way M is build is that a given step is only executed if it is needed in last return statement. So based on the condition in last step "result" either “then” block or the “else” block will be executed.

let
    A = 2,
    Abort = if (A<>5) then error "A is not 5!" else false,

    // Something really time consuming
    Files = Folder.Files("C:\"),
    LongRun = List.Transform(Files[Content],each Excel.Workbook(_)),

    Result = if Abort then Abort else LongRun
in
    Result

Query above executes very fast because Abort throws an error and LongRun is skipped. Change A to 5 and run it query again. You may wait forever to finish!

FUNCTION There are build in native M functions that shall be used extinsvly. some are used often.

Functions that use records as input: functions that include a record as a function parameter. Very fiew.

Web.Contents(url as text, optional options as nullable record) as binary Folder.Files(path as text, optional options as nullable record) as table

LOOPING:

Write about list .enerate List.Generate

List.Numbers(1, 10) 1..10

Adding and multiplying cells:

Pros and cons Res = 3 * null * 2 Res = T[A]{0} * T[B]{0} * T[C]{0} List.Product({3, null, 2})

EXPALIN THIS BETTER like here List.Transform vs List.Accumulate Both Accumulate and Transform functions loops over a list! Difference is the parameters provided to callback function and that list only returns a list while accumulate iterates over a list as well but can return any object i.e a table.

Transform provides Current -> value of current element of iterated list. -> Returns list Accumulate provides Current & Result previous iteration. -> Returns any object (of same type as "seed" it started with)

Transform call the callback for EACH value in list and finally returns a new list

{11,12} = List.Transform({1, 2}, (current)=> current + 10)
{11,12} = List.Transform({1, 2}, each _ + 10)
{11...110} = List.Transform(List.Numbers(1, 100), each _ + 10)

Accumulate seeds the callback with "first result", note that this "seed" can be any object not limited to list! Then for each iteration callback receives both current list value and result from previous run. Returns last result. Note! This function

{11,12} = List.Accumulate({1,2}, {}, (l, i) => List.InsertRange(l, List.Count(l), {i+10}))
T= #table({"A","B"},{}),
Res = List.Accumulate({1,2},T, (t,i)=> Table.InsertRows(t,Table.RowCount(t),{[A=i,B=i+10]}))
R = #table({"A","B"},{1,11},{2,12})

Power Query - Iterate over unknown number of solumns

tblTest:
+------+------+------  
| Col1 | Col2 |  Col3 ...
+------+------+------
| 1    | 2    |
| null | null | ...
| null | 3    |
+------+------+


let
    tblTest = Excel.CurrentWorkbook(){[Name="tblTest"]}[Content],    
    tblNew = Table.FromColumns(
	{List.RemoveNulls(tblTest[Col1]),
    List.RemoveNulls(tblTest[Col2])},
	Table.ColumnNames(tblTest))
in
    tblNew

let
	tblTest = Excel.CurrentWorkbook(){[Name="tblTest"]}[Content],   
	ListOfColumns = Table.ToColumns(tblTest),
	tblNew = Table.FromColumns(List.Transform(ListOfColumns, each List.RemoveNulls(_)))
in
	tblNew


tblNew:
+------+------+
| Col1 | Col2 |
+------+------+
| 1    |  2   |
| null |  3   |
+------+------+

Power Query M language

By Martin Czerwinski CMQ Nordic AB® March 2020

M is the powerful language behind the scene of Power Query. For many M looks like a scary language, hidden behind its auto-generating graphical interface. As Power Query M developers we highly recommend you to spend little time to understand the M syntax and how it works. There are many powerfully operations that you can do with M that you are not be able to do through the provided graphical interface. Additionally often the auto generated steps can easily be modified to suit your needs.

This is compact tutorial and a reference document that explains the basics of M, its syntax that provides solutions and deep understanding to some of the problems that every power query developer at some point get stuck with.

Bookmark this page, share it and feel free to reach out to us with questions, comments or request for assignments!

Prerequisites: Some excel & programing skills.

https://docs.microsoft.com/en-us/powerquery-m/comments

TABLE OF CONTENT

Core areas and M syntax
- Why M? ◦ Power Query Editor ◦ The Basics
- Get data into query ◦ Data sources
- Syntax & Expressions ◦ Access values ◦ Operators
- Functions ◦ Each & __
- Lists ◦ Records ◦ Tables
- Errors ◦ What does ? ◦ Things to avoid
Learn from examples
- Example 1 - Description
- Example 2 - Description
- Example 3 - Description
Deeper into some topics
- Reshape a table - changing values and reshaping a table
- Example 2 - Description
- Example 3 - Description

Core areas and M syntax

Let's go though the main areas of Power Query M language, some commonly used expressions and dig into few areas that we thing will be very beneficial for you to understand. Each area is followed by a descriptive example. Staring with basics moving toward more advanced stuff.

Equal → =
Tables	`tableA = tableA`	`true`
Lists	`listA = listB`	`false`
Not equal → <>
Tables	`tableA <> tableB`	`true`
Lists	`listA = listB`	`false`
Concatenation → &
Strings	`"A" & "BC"`	`"ABC"`
Lists	`{1} & {2,3}`	`{1,2,3}`
Records	`[a=1] & [b=2,c=3]`	`[a=1,b=2,c=3]`
Tables	`#table({"A"},{{1}}) & #table({"B"},{{2}})`	`#table({"A","B"},{{1},{2}})`


&	Concatenation	"A" & "BC" → "ABC" {1} & {2,3} → {1,2,3} [a=1] & [b=2] → [a=1, b=2] #date(2020,3,20) & #time(12,30,20) → 20/03/2020 12:30:20
x or y	Cond. OR	evaluates right operand only if necessary
x and y	Cond. AND	evaluates right operand only if necessary
x <> y	Not equal	→ `true` or `false`
not x	Logical NOT	→ `true` or `false`
x >= y	Greater or equal	→ `true` or `false`
x <= y	Less or equal	→ `true` or `false`
x = y	Equal	→ `true` or `false`
#duration	Duration	#duration(days, hours, minutes, seconds)
#date	Date	#date(year, month, day) #date(2020,3,20) - #duration(10,0,0,0) → 20020/03/10
#time	Time	#time(hour, minute, second) #time(24,0,0) + #duration(0,14,90,0) → 15:30:00
1+2	Sum	→ 3
1+"a"	Error	→ We cannot apply operator + to types Number and Text
"a"+"b"	Error	→ We cannot apply operator + to types Number and Text


number	Type	1 or 1,2
text	Type	"a" or "ewa"
logical	Type	true or false
function	Type	List.Transform
binary	Type	a file
list	Type	{1,2}
record	Type	[A=1, B=2]
table	Type	#table() TODO
time	Type	time values
date	Type	date values
datetime	Type	datetime values
duration	Type	duration values

Table	T= #table({"Col One","Col Two"}, {{1,2},{3,4}})
Column in T	T[Col One] → a list {1, 2}
Row in T	T{0} → a record [A=1, B=2]
Cell in T	T[A]{0} → a single value 1
All headers names in T	Table.ColumnNames(T) → a list of texts {"Col One", "Col Two"}
One Header name in T	Table.ColumnNames(T){0} → a text "Col One"
Record	R = [A=1,B=2,C=3]
Value in R	R[A] → a single value 1
All field names in R	Record.FieldNames(R) → a list of texts {"A", "B", "C"}
One field name in R	Record.FieldNames(R){0} → a text "A"
List	L = {1, 2, 3, 4}
Lists value	L{0} → 1

Table: T
Drill down	T{0}[Content] → expands ALL column from tblSource1 to new table
Drill down	T{[Name="tblSource1"]}[Content] → expands ALL column from tblSource1 to new table
Expand with function	*Table.ExpandTableColumn( Table.SelectRows(Source, each([Name] = "tblSource1")* ), "Content", {"Id","Name","Age"})** → expands KNOWN columns from tblSource1 to new table

CMQNordic/Power Query M languae.md

Power Query M language

TABLE OF CONTENT