Power Query M函数_我常用的玩法

跟据日期字段，为表格增加年、 月、日3个字段

= Table.FromRows(
    List.Transform(Table.ToRows(ZCESRSET_Table),each _ & [a=_{0}, b=List.Transform({Date.Year,Date.Month,Date.Day},each _(a))][b])
    ,Table.ColumnNames(ZCESRSET_Table)&{"交易日期年","交易日期月","交易日期日"}
)

上述公式分解：

基础数据类型

种类	文本
Null	null
逻辑	true false
数字	0 1 -1 1.5 2.3e-5
时间	#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)
文本	“hello”
二进制	#binary(“AQID”)
列表	{1, 2, 3}
记录	[ A = 1, B = 2 ]
表格	#table({“X”,“Y”},{{0,1},{1,0}})
Function	(x) => x + 1
类型	type { number } type table [ A = any, B = text ]

List.Transform相当于For循环

• {Date.Year,Date.Month,Date.Day}是数组，”each _“中的”下划线“就形参，指向数组中的每个值，这里的值是Date.Year、Minth、Day函数。
• #date(2021,8,19)是日期格式的值。
• “_(#date(2021,8,19))”，是函数执行，函数的参数正是这个日期值。
• 注意：在List.Transform()的作用域中，只能用下划线做形参"_"，如果像下面这种情况，循环中有循环，那么内层循环中的下划线就是指向内层数组的值的，如果要引用外层的值，下面借助记录Record的方法来构造是可以的。

= List.Transform({Date.Year,Date.Month,Date.Day},each _(#date(2021,8,19)))

借助记录Record，返回待拼接的数组

• 相当于有个匿名字典{‘a’:‘2021-8-19’,‘b’:[2021,8,19]}[‘a’]，我们要获取中b的值，b的值是一个数组。
• a=_{0}是获取a的第一项。

[a=_{0}, b=List.Transform({Date.Year,Date.Month,Date.Day},each _(a))][b]

Table.ToRows()

• 表函数，将表的第1-n行数据，对应放进第1-n个数组里面，每个数组里就是各行每列的值，最后返回这n个数组拼起来的二维列表。

总结：增加3列的思路正是将表对象，转为二维列表，将每行的子列表，去增加年、月、日这3个元素，最后再转会表对象。

• 列表增加元素，用”&“符号连接，不过元素要放在数组里，实际是数组和数组的连接~

Table.ColumnNames()

• 表函数，返回表格对象的标题数据，放在一维列表里。

Table.FromRows(arr,list)

• 将二维列表转为表格对象，arr是二维列表，list是标题的一维数组。
• 注意：power query中的数组是用花括号"{"包裹的，而不是中括号”[“，中括号包裹的是Record，这个有点像字典，它必须是一维的，如果你想二维那就要把每个Record装在列表里。。

常用方法

方法	结果
Text.End(“Hello, World”, 5)	World
Text.From(110)	110（文本格式）

Left join

=Table.NestedJoin(
NextContractDate, {"交易日期", "NextContractDate"},
NextContractDate, {"交易日期", "CurContractDate"},
"自定义名称字段d", JoinKind.LeftOuter)

=Table.NestedJoin(
表a, {“字段a”, “字段b”},
表a, {“字段a”, “字段c”},
“自定义名称字段d”, JoinKind.LeftOuter)

• 但这只是将表第3参数的表的每行vlookup过来了，还要提取你想要的字段。

Table.ExpandTableColumn(表, "自定义名称字段d", {"今结算"}, {"NextContractDate.1.今结算"})

• Table.ExpandTableColumn(表, ”自定义名称字段d“, v过来的表里你想要的字段, 给他起个新的名字)

将表中的列展出来

= Table.ExpandTableColumn(#"Invoke Custom Function1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

• Table.ColumnNames()函数返回列名的数组
• #“Transform File”(#“Sample File”)，Transform是一个函数，Sample File是一个表，函数去执行这个表，这个函数由数据源来源于文件夹那时产生。

对空的列进行修改

两种方法，替换和直接拼接

= Table.ReplaceValue(#"Extracted Text Range","","USD",Replacer.ReplaceValue,{""})

= Table.TransformColumns(#"Extracted Text Range",{"",each "USD", Text.Type})

PowerQuery创建随机年份

= Table.TransformColumns(Table.FromList(
    List.Transform(List.Repeat({#date(2021,1,1)},3),each Text.From(Date.AddYears(_,Number.Round(Number.RandomBetween(-1,3),0)))),
    null,{"各年日期"}
),{"各年日期", Date.From})

• Table.FromList(list, null, {“字段名”})，这玩意居然只能转换元素是字符串类型的数组，逼我多家一步把日期转为字符串。
• 核心是List.Repeat({元素},复制n个)，配合List.Transform循环+随机年
• 日期的加减有专门的函数，Date.AddYears(日期，随机值）。

PowerQuery选择行

• Table.SelectRows(表2,(x)=> x[生效日期]<[日期] and [日期]<=x[截止日期]){0}，此处x就是指向表2
• 选择行的公式后面有个{0}，是因为table{0}是获取table的第一行，为 Record 格式。
• List.FirstN(数组,6)取数组中的前6个，返回数组。
• 再用Record.RemoveFields方法去掉 Record 的前6项。

= Table.AddColumn(
	源, "自定义", 
	each Record.RemoveFields(
		Table.SelectRows(表2,(x)=> x[生效日期]<[日期] and [日期]<=x[截止日期]){0},
		List.FirstN(Table.ColumnNames(表2),6)
		)
)

• 将Record展开来。

= Table.ExpandRecordColumn(已添加自定义, "自定义",List.Skip( Table.ColumnNames(表2),6))

加辅助列

let
    Source = Table.NestedJoin(
            Table.AddColumn(表1,"匹配列",each Date.StartOfMonth([日期])), List.FirstN(Table.ColumnNames(表1),4)&{"匹配列"},
            表2, List.FirstN(Table.ColumnNames(表2),4)&{"生效日期"},
            "ntb",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source,"ntb", List.LastN(Table.ColumnNames(表2),8), List.LastN(Table.ColumnNames(表2),8)),
    OK = Table.RemoveColumns(Expand,{"匹配列"})
in
    OK