M Code

Question 1

Text.split

Answer 1

text.split([Column Name],” “) -> return a list

Question 2

Comment

Answer 2

//

/*
multi-line
*/

Question 3

manual input a list

Answer 3

= {“A”,”B”}

Question 4

pick a column and turn it to a list

Answer 4

click on
last_step[Column name]

or
select column -> right click -> drill down / add as new query”

Question 5

distinct list

Answer 5

list.distinct

or just click remove duplicates”

Question 6

Filter list

Answer 6

List.Contains

= Table.SelectRows(#”Last Step”, each List.Contains(Filter_list, [Field in table])= true )

normall add as new custom column first, then change it to SelectRows

Question 7

List.Buffer

Answer 7

no need to reload the filter condition for every row but it should be outside of the “each” iteration, i.e. add a step to read in the buffered list before using it

List.Buffer ( List A)

Question 8

List Naming

Answer 8

list better not start with a number => otherwise need to use #”Field ABC” instead of just Field_ABC

Question 9

Table.Selectrows

Answer 9

Table.Selectrows(Table, each condition)

Question 10

get a list at source step (drill down)

Answer 10

using “Field Access Operator” [Field]

Question 11

List.IsEmpty and List.Intersect

Answer 11

List.IsEmpty(List.Intersect({Text.Split([Product_Name],” “),NOT_filter}))

Question 12

Query and Field convention

Answer 12

If the query/step is properly named (i.e. not start with a number and no space), just use it directly.
If it is not properly named, use #”Table A”
Field uses []
List use curly {}

Question 13

Extracting Current workbook Name range as string

Answer 13

Excel.CurrentWorkbook(){[Name=”Table_6_DyanmicPathInput”]}[Content][Column1]{0}

Question 14

and or condition

Answer 14

Table.SelectRows(#”Changed Type”, each ([Con1]= “A” and [Con2]=”B”) or [Con3]=”C”)