Power Query

一、其他

追加查询

1. 数据导入

Excsl.Workbook(File.contents("C:\Temp\book1.xlsx",true,ture)

• 作用：引入数据
• 第一个参数：引用数据地址
  • 引用相对路径，在excel单元格写入公式
  • 同一文件下 =left(SUBSTITUTE(CELL("filename"),"[",""),Find("]",SUBSTITUTE(CELL("filename"),"[",""))-1)
  • 不同文件下 =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"数据.xlsx"
  • 定义名称，替换据对路径
  • Excel.CurrentWorkbook(){[Name="定义的名称"]}[Content]{0}[Column1]
• 第二个参数：第一行是否为标题，true是，null否
• 第三个参数：号称加快运行速度，实际效果不详

2. 导入文件夹

Folder.Files("C:\temp\数据")

• 作用：导入文件夹，获取二进制文件

3. 新增字段名

List.Distinct(List.Combine(List.Transform(删除的其他列[Data],each Table.ColumnNames(_))))

• 作用：使用动态字段名

4. 展开列

Table.ExpandTableColumn(删除的其他列,"Data",{"姓名","语文","数学","历史"},{"姓名","语文","地理"})

• 作用：展开列
• 第一个参数：展开哪一个表
• 第二个参数：展开表的什么字段
• 第三个参数：展开字段的哪些列。（结合3，做动态列名）
• 第四个参数：返回的结果数哪些列。可省略

乱七八糟的知识

尝试执行

• try 520 & 1314 otherwise "数字不能做链接"
  • 作用：当执行错误时，返回后面的指定字符串

二、M函数

Table

1. 【添加列】Table.AddColumn

• =Table.AddColumn(源,"称呼",each if [性别] = "男" then "先生" else "女士",type text)
  • 第一个参数：引用表
  • 第二个参数：添加列的字段名
  • 第三个参数：列数据，需要添加操作的列
  • 第四个参数：列类型，可以省略

2. 【排序字段】Table.RecorderColumns

• =Table.RecorderColumn(源,{"姓名","年龄","成绩"})
  • 第一个参数：引用表
  • 第二个参数：列的顺序

3. 【筛选】Table.SelectRows

• =Table.SelectRows(源,each [数学]>90 and [英语]>80)
  • 第一个参数：引用表
  • 第二个参数：筛选的条件

4. 【删除重复项】Table.Distinct

• =Table.Distinct(源)
  • 对表所有列重复的删除
• =Table.Distinct(源,"姓名")
  • 对一个列重复的删除
• =Table.Distinct(源,{"姓名","成绩"})
  • 对多个列重复的删除

5. 【计算行数】Table.RowCount

• =Table.RowCount(源)
  • 对整个表的行数计数

6. 【计算列数】Table.ColumnCount

• =Table.ColumnCount(源)
  • 对整个表的列数计数

List

7. 【列表求和】

• =List.Sum(列表)

8. 【列表计数】

• =List.Count({1,2,3,"","YI"})
  • 对列表所有项计数，返回结果4
• =List.NoNullCount({1,2,3,"","YI"})
  • 对列表非空项计数，返回结果3

9. 【判断列表为空】

• List.IsEmpty(列表)
  • 为空返回：true
  • 不为空返回：false

10.【判断列表是否没有重复元素】

• List.IsDistinct(列表)
  • 没有重复返回：true
  • 有重复返回：false

11. 【列表求平均】

• List.Acerage(列表)

12. 【列表最大值&最小值】

• List.Max({1,2,3}.521,2,false)
• List.Min({1,2,3}.521,2,false)
  • 第一个参数：列表
  • 第二个参数：当列表为空时。返回第二个参数（可省略）
  • 第三个参数：当不是0时，求最小值（可省略）
  • 第四个参数：当时true时，列表为空也返回第二参数（可省略）
  • 数字 < 文本 ， 英文 < 中文