【PowerQuery】基础

课程地址：https://www.bilibili.com/video/BV1gk4y117yy
注意：该笔记大部分是在office 2019的操作记录，与office 365有所不同，例如工具的位置有些不同，例如office 365需要保存后才能刷新，office 2019有时不需要

01 添加自定义列

1.调出power query

选择表格区域（或选中表格区域某个单元格），右击，[从表格/区域获取数据]，确认数据来源和是否包含标题
多个数据源导入：数据-获取数据-来自文件（office 365)；数据-新建查询-自文件（office 2019)

2.常用设置

主要标签-主页、转换、添加列
属性-名称-可以修改表名
应用的步骤-可以撤销步骤

3.添加列

从标签【添加列】-【自定义列】
设置-新列名
自定义公式=_(代表一整行，详见M函数)
（【视图】-【编辑栏】查看公式）
= Table.AddColumn(更改的类型, “总分”, each )
更改的类型：这里是上一步的名称，计算机自动识别的字段类型，可以点击字段前图标手动修改
each:代表每一行
自定义公式=[语文]+[数学]+[英语]
= Table.AddColumn(更改的类型, “总分”, each [语文]+[数学]+[英语])

4.关闭并上载

【主页】-【关闭并上载至】
选择在新工作表还是现有工作表放置powerquery表
原来数据添加记录后，powerquery表右击-刷新，新列的数据自动更新

02 添加条件列

【添加列】-【条件列】
修改列明，填写if条件逻辑，多条件-添加子句
= Table.AddColumn(更改的类型, “称呼”, each if [性别] = “男” then “先生” else “女士”)
拖动列自由调整列序

03 索引列、重复列、拆分和提取

【添加列】-【索引列】
可以设置自定义索引，填写初始值和步长
双击字段名可以修改字段名

【添加列】-【重复列】
选中某列后，选择重复列，则会重复该列（作为备份）

【转换】-【拆分列】
按分隔符：逗号、冒号…自定义；按左侧、右侧、每一次
按字符数：字符数；一次靠左、一次靠右、重复
按位置：索引从0开始;“0,2”:01位置是一列，2及以后是一列，对于拆分出来的列，如果不需要，可以右击删除列

【添加列】/【转换】-【提取】
长度、首字符、结尾字符、范围（索引从0开始）、分隔符之前|之后|之间

04 标题、删除行（空与错）、替换、筛选

如果标题行在第一行，【主页】-【将第一行用作标题】
（逆操作-如果第一行不是标题-【将标题作为第一行】）

【主页】-【删除行】
前几行、后几行、间隔行（2,1,1：从第2行开始，每次删除一行，保留1行）、重复项（选中单列，删除重复值所在的行）、空行（全行空）、错误（选中有错误的列，删除错误所在行）
【主页】-【保留行】
前几行、后几行、范围、错误（选中有错误的列，保留错误所在行）

【主页】|【转换】-【替换值】
选中才替换，选中某列、某几列（ctrl)、全部列（shift)

筛选-字段由此小倒置三角

05 合并查询原理

多个工作表需要导入：新建一个空白文档-数据-新建查询-从文件-从工作簿-多项选择-都选中-转换数据-进入PowerQuery
在需要添加数值的表，【主页】-【合并查询】；或者【主页】-【将查询合并为新查询】不破坏原表
打开的合并界面中，第一个表是需要添加查询数据的表，第二表是有查询数据的表，分别选中查询链接的列，在联结种类中选择连接方式
连接方式

连接完毕后，每一行连接的是一个table（即vlookup了所有字段）,只需要展开选择需要展现的字（展开-列字段右侧对称箭头）

06 合并查询案例及填充与去重

单条件，见上节

多条件，选择连接列的时候需要多个条件列，使用ctrl键多选

一对多，
合并单元格的项，到PoewerQuery里会自动取消合并，可能需要填充
填充
选中要填充的列，【转换】-【填充】-向上/向下
仍旧选择连接条件，连接后形成一个笛卡尔积的结果

tip:

• 对于两列数（不是两张表），如果想通过连接的方式查看差异（左反右反），可以不需要拆成两个表。
• 方法1：选中一列，【插入】-【表】-选择范围，可以修改名称，另一列，同样操作。
  方法2：选中一列数据范围，【公式】-【定义名称】，可以修改名称，另一列，同样操作。
• 在空白处，【新建查询】-【从其他源】-【空白查询】。
• 编辑栏输入= Excel.CurrentWorkbook()，返回刚才建立的两个表格,左侧是“查询1”，有两个列“Content"和”Name"代表刚才两个表格的内容和表名。
• 在左侧查询导航栏，右击【新建查询】-【自其他源】-【空查询】，编辑栏输入= 查询1[Content]，获得了两个表的内容；编辑栏输入=查询1[Content]{0}，得到第一个表的内容；同样的方法（查询1[Content]{1}）得到第二个表的内容；都删除空行，删除重复项
• 然后使用合并查询，获得交集、并集、反集等

07 追加查询-汇总多个工作表【在同一工作簿下】

方法1
选择多张表，【主页】-【追加查询】-将查询追加为新查询，将多个表依次导入拼接（office 365和office 2019都需要保存后刷新）
该种方法会导致文件移动位置后，刷新无效
清除：除了删除上载的工作表，也要删除查询&连接，【数据】-【查询和连接】

方法2
只选择一张表，转换数据，步骤退到“源”
= Excel.Workbook(File.Contents(“D:\PowerQuery\练习文件\07.追加查询多个工作表\数据.xlsx”), null, true)
null代表该表是否提升第一行作为表头，null改成true，这里看到路径是写死的
仅保存Data和Name列，展开（遇到数据可能不完整，点击加载更多），上载至
但是，当更新数据源，刷新出很多东西
需要编辑此查询，回到“源”，【主页】-【刷新预览】，kind只选sheet类型，Item也剔除新建的sheet1，再回到最后一步，就没有乱的东西，关闭并上载
但是增不了列，仍旧回到编辑，在最后一步前插入一自定义步骤，点击最后一步，更改编辑栏公式，仍旧是“删除的其他列”，重命名插入的自定义步骤“字段名”，并在编辑栏输入
= List.Distinct(List.Combine(List.Transform(删除的其他列[Data],each Table.ColumnNames(_))))
这样会把所有的列名不重复地列出来
点击最后一步
= Table.ExpandTableColumn(删除的列, “Data”, {“语文”, “数学”, “英语”}, {“Data.语文”, “Data.数学”, “Data.英语”})
该成动态的
= Table.ExpandTableColumn(删除的列, “Data”, 字段名)
上载后刷新，就有刚才添加的新的列了，之后再去添加新的列，也能刷新出来

但是同样有方法1的问题，移动到其他路径，仍旧失效

新建一个工作表，起名“路径”，在A1单元格通过函数获取本文件的路径
=left(SUBSTITUTE(CELL(“filename”),"[",""),Find("]",SUBSTITUTE(CELL(“filename”),"[",""))-1)
将A1单元格转为表（方法详见06 tip 方法1和方法1，定义名称cmx）

再次进入编辑，点击源，将写死的路径替换掉
Excel.CurrentWorkbook(){[Name=“cmx”]}[Content]{0}[Column1]
变成
= Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name=“cmx”]}[Content]{0}[Column1]), true, true)
仍旧筛选Kind和Item，保存上载

08 追加查询-汇总多个工作表【不同工作簿下】

如果源文件很大，不想打开文件
新建一个空工作簿，在这个空工作簿里使用powerquery追加查询
追加多个表的时候可以上下移动表的顺序，追加查询到新查询
这样，更改源文件保存后，刷新新文件，会随之更新，但是源文件换一个位置存放后，刷新失效

方法：源数据、新文件按和文件夹一起，不要该源文件名称，一起挪到其他地方仍然可以正常打开
导入一张表，加载数据，删除步骤，只有第一步“源”
点到Table提升表头,excel.workbook第二次参数null改成true,回车后都提升了表头
仅保留Name\Data，展开表

解决问题1：动态添加列
“删除的列”后面插入步骤，重命名“字段名”
回到“展开的Data",编辑栏公式里仍旧改成”删除的列“
到”字段名“，编辑公示栏，输入
= List.Distinct(List.Combine(List.Transform(删除的列[Data],each Table.ColumnNames(_))))
回到“展开的Data"，将大括号及后面替换成”字段名“
= Table.ExpandTableColumn(删除的列, “Data”, 字段名)
关闭并上载至

解决问题2：“源”里面写死的路径改成动态的
工作簿中插入新工作表“路径”，A1单元格插入公式
=LEFT(CELL(“filename”),FIND("[",CELL(“filename”))-1)&“数据.xlsx”
（“数据”是源文件的名称）
选中A1单元格，定义名称cmx
数据-查询和连接，打开powerquery
将据对路径（包含引号），替换成Excel.CurrentWorkbook(){[Name=“cmx”]}[Content]{0}[Column1]
= Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name=“cmx”]}[Content]{0}[Column1]), true, true)

09 追加查询-对文件夹下文件汇总

获取数据-从文件夹
大文件夹里面如果有子文件夹，可以嵌套把所有文件找出来
全部转换数据后，可以根据后缀选择文件类型、文件名选择文件
仅保留“Content",删除其他列，添加列，自定义列，用于使用二进制数据生成表
=Excel.Workbook([Content],true,true)
生成table后，删除二进制列
展开后，除了Data删除其他列，再展开

使用前两节知识，
1.动态修改列
”删除的其他列1“和”展开的Data"之间插入步骤，明明我i“字段名”，输入公式
= List.Distinct(List.Combine(List.Transform(删除的其他列1[Data],each Table.ColumnNames(_))))
“展开的Data",公式改成
= Table.ExpandTableColumn(删除的其他列1, “Data”,字段名)

2.动态路径名
新建工作表，改名路径
A1单元格输入公式取文件夹路径
=LEFT(CELL(“filename”),FIND("[",CELL(“filename”))-1)
选中A1单元格，定义名称cmx
回到powerquery,在”源“步骤，将绝对的路径（包括引号）改成Excel.CurrentWorkbook(){[Name=“cmx”]}[Content]{0}[Column1]
可以整个文件夹移动，但源文件名不要改

10 透视和逆透视

未透视的表，自表格区域进入power query，
【转换】-【透视列】
选定值列，高级选项：求和求平均

二维表，自表格进入powerquery
选择要逆透视的列（可能多列，反选-逆透视其他列）
逆透视成一维表

11 文本透视案例和数据类型转换

文本透视
高级选项：不要聚合

数据类型转换
除了字段前面的标志
【主页】-数据类型，也可以修改
小数位数舍入：【转换】-【舍入】

12 分组依据

sumif countif
【主页|转换】-【分组依据】
基本：选定分组依据，新列名，操作，柱（数据）
高级：可以添加多个分组依据

13 转置，合并列和多表头合并

如果项有合并的单元格：对于合并的单元格，导入后会null,可以使用之前的向下填充，再去逆透视
如果表头有合并的单元格（多级表头）：先在原表格上方插入一行空行，自表格区域进入powerquery的时候把表格上方空行区域也选中，表包含标题（或者不用插入空白行，表不包含标题），【转换】-【转置】，向下填充，提升标题，选中值列，逆透视
既有项的合并，也有表头的合并：表不包含标题，项向下填充，因为行列都是二级的，所以先把项的二级合并成一级，使用分隔符，转置后再填充，逆透视，再把合并的列用分隔符分成两列