PowerQuery学习总结

PowerQuery知识点总结

一、Power Query介绍

1.1 Power Query是什么

Power Query是实现数据获取和数据清理的一个工具

例如：在有很多种格式数据源的情况下，需要一个一个数据源（数据库、json、Hdfs、 文本、csv 等）去处理，让其格式统一并合并分析处理， 但是Power Query 可以解决这个问题

Excel中Power Query的位置(365版本)：数据菜单下的获取和转换数据、查询和连接功能区

1.2 Power Query的功能

• 数据获取：从不同来源、不同结构、不同形式获取数据，并按统一格式进行横向合并、纵向（追加）合并、条件合并等

• 数据转换：将原始数据转换成数据处理所需的结构或格式

• 数据处理：为后续分析做准备，例如：加入行、列；处理某些单元格值等

• 数据共享：共享到Excel或者Power Pivot进行下一步分析

• 实现一键自动刷新：基于数据共享，源数据集更新，加载到Excel和Power Query中的数据也会更新

二、Power Query数据获取

2.1 数据获取方式

2.1.1 如何导入数据

Power Query有强大数据处理功能，几乎可以从任何来源、任何结构、任何形式上获取数据，如下图：

2.1.2 导入数据后相关操作

加载：数据会被加载到excel中，并在excel显示出来

加载到：会出现选项进行选择

• 表：加载到现有工作表/新工作表

• 数据透视表：会在现有工作表/新工作表创建一个数据透视表

• 数据透视图：会在现有工作表/新工作表创建一个数据透视图

• 仅创建连接：数据不会加载到excel中，但是Query编辑器有（只是在excel界面中看不到数据）

  好处：一些辅助表和辅助列，不用返回到excel中

数据加载流程：源数据→Power Query(数据处理)→Excel

转换数据：进入Power Query编辑器

关闭并上载 = 导入数据时的加载

关闭并上载至 = 导入数据时的加载到

进入Power Query编辑器后，无法对excel进行操作

如何从编辑器返回到excel，点击关闭并上载或关闭并上载至

如果关闭并上载或关闭并上载至为灰色，代表之前已经执行过相关操作

导入多份数据：

• 方式一：导入一份数据后，若其他数据和已导入数据不在一个工作簿，点击新建源→文件→Excel工作簿（或其他）

  

• 方式二：将已导入数据关闭并上载至仅创建连接，再从数据菜单下导入数据

  

  

2.1.3 一些窗口和功能的说明

查询&连接窗口的显示：

• 方式一

Query编辑器的进入：

• 方式一：导入数据时点击转换数据。

• 方式二：导入数据后，双击查询&连接窗口的任意一个连接。

• 方式三：导入数据后，右键查询&连接窗口的任意一个连接，选择编辑，。

• 方式四：导入数据后，点击上方查询→编辑。

  

excel中导入的某些数据不需要显示：

方式：右键查询&连接窗口的任意一个连接，选择加载到里的仅创建连接。

2.2 网页数据抓取

从web中加载数据 http://www.boc.cn/sourcedb/whpj/

更加复杂的网页数据爬取在Power BI 中进行

三、了解Power Query编辑器

3.1 Power Query编辑器认识

Power Query编辑器没有单元格的概念

Power Query对数据类型很严格，如果出现报错优先看数据类型

Power Query中没有ctrl+z的撤销快捷的，故若想返回上一步，要么取消上一步，或修改上一步的操作，均从历史步骤中进行（一定要注意步骤，从上到下依次执行）。

在数据处理过程中，经常会需要进行筛选检查等等，但只是为了检查，故检查完成后，需要将该检查的历史步骤删除，再做正式的处理操作，因为所有的步骤都是基于上一个步骤呈现的结果表进行的。

主页：

转换：

添加列：

视图：

Power Query编辑器实时刷新逻辑⭐⭐⭐

3.2 一个案例了解Power Query编辑器

1. 标题的上升和下降：，进行标题处理，以便观察、处理和分析数据。

   效果：

2. 删除行：，根据需求删除相应的行数，然后可以将第一行作为标题。

   效果：

   

3. 数据源

   把文件发给其他人时，要保证数据源一致、路径一致，否则会报错。

   如果在数据源进行数据的增加，增加后的数据会按照历史应用步骤从上到下依次进行操作。

   数据源路径发生改变时，要在Power Query编辑器中进行修改。

   • 方式一：在源直接修改

     

   • 方式二：点击源右侧的设置按钮

     

   • 方式三：

     

4. 快速填充

   

​ 向上：将单元格中的值向上填充至当前所选列中的相邻空单元格内。

​ 向下：将单元格中的值向下填充至当前所选列中的相邻空单元格内。

四、Power Query最常用的清洗12招⭐⭐⭐

4.1 基础行列操作

• 列操作

​ 选择列：选择要保留的列，其余列不保留，相当于删除其他列。

​ 转到列：选择某一列会跳到某一列，高光显示。

​ 删除列：点击某列→删除列，该列删除。

​ 删除其他列：点击某列→删除其他列，除该列外其他列都被删除 = 选择列。

​ 重复列：复制某列，粘贴至新列。

• 行操作

  保留最前面几行：只保留此数据表的前N行。

  保留最后几行：只保留此数据表的后N行。

  保留行的范围：指定从特定行开始要保留的行数。

  保留重复项：保留当前所选列中重复值的行。

  保留错误：仅保留当前所选列中包含错误的行。

  删除最前面几行：从此表中删除前N行。

  删除最后几行：从此表中删除后N行。

  删除间隔行：指定从特殊行开始要重复取得和跳过的行数，针对源数据有规律。

  删除重复项：删除当前所选列中重复值的行，默认保留第一个数据。

  删除空行：删除所有空行（该空行指一整行都为空，如果一行中有任一字段不为空，该行无法删除，可以利用筛选）。

  删除错误：删除当前所选列中包含错误的行（一般都是通过数据类型进行更改而产生的错误）。

4.2 删除重复项

原理：删除重复项，默认保留第一条数据

排序+删除重复项容易出问题

原因：在Power Query编辑器排序是在前端操作的，不和后端进行交互，所有后端不知道前端进行了排序，不会更新数据，而删除重复项由后端操作的，后端是基于排序前的数据进行删除重复项的操作，所以会出错

解决：排序结束后→转换→检测数据类型→删除重复项，点击检测数据类型是由后端操作的，此时后端会更新数据，再进行删除重复项操作时不会出错。

4.3 数据格式的转换

Power Query对英文字母的大小写敏感，不能用大写字母去找对应的小写字母

• 格式

​ 小写：所有字母小写 = Excel中lower()函数

​ 大写：所有字母大写 = Excel中upper()函数

​ 每个字词首字母大写：将字符的首字母大写 = Excel中proper函数

​ 修整：从所选列中的每个单元格中删除前导空格和尾随空格

​ 清除：产出所选列中的非打印字符（例如：回车）

​ 添加前缀/后缀：类似于合并，但对位置所有规定（前或后）

4.4 数据的拆分、合并、提取、多分隔符拆分

• 合并

​ 选择要合并的列，点击合并

• 提取

​ 长度：返回所选列中的文本的长度

​ 首字符：从此列中每个值的开头返回指定数量的字符

​ 结尾字符：从此列中每个值的结尾返回指定数量的字符

​ 范围：从指定索引开始，从此列中返回指定数量的字符，索引下标从0开始

​

​ 分隔符之前的文本：返回分隔符出现前的文本

​ 分隔符之后的文本：返回分隔符出现后的文本

​ 分隔符之间的文本：返回两个分隔符之间的文本

​

• 拆分列

​ 按分隔符：基于指定的分隔符，拆分所选列中的值

​

​ 按字符数：将所选列的值拆分为具有指定长度的片段

​

​ 按位置：将所选列的值拆分为指定位置的片段

​

按照从小写到大写的转换、按照从大写到小写的转换、按照从数字到非数字的转换、按照从非数字到数字的转换

奇数和偶数的判断（注意数据类型要是整数），判断结果为布尔值。

---

多个分隔符的拆分（利用M函数）

先按照某一个分隔符拆分（得到分隔步骤的代码），然后在高级编辑器对M函数进行修改

按分隔符拆分列 = Table.SplitColumn(更改的类型, "Column2", Splitter.SplitTextByDelimiter("，", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3"}),

修改为下述代码，估计一下拆分后有多少列，预留列的空间：
按分隔符拆分列 = Table.SplitColumn(更改的类型, "Column2", Splitter.SplitTextByAnyDelimiter({"，".","," ","；","*",":"}, QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3", "Column2.4", "Column2.5", "Column2.6", "Column2.7", "Column2.8", "Column2.8", "Column2.10"}),

4.5 转置和反转

转置不会改变维数（表的属性），标题不参与转置

转置：将行作为列，将列作为行

反转行：反转表的行，使最后一行显示为第一行

4.6 透视、逆透视

二维表特性：

• 必须要有三个字段
• 从数据透视表看行、列都有字段

一维表特性：

• 一行是一条完整的数据
• 从数据透视表看只有行或列有字段
• 一维表字段独立，可以看出意义

逆透视列：二维转一维

​ 把哪些标题变成值，就选择该标题所在的列进行逆透视

透视列：一维转二维，一维表必须有三个字段

​ 想把哪个列作为维度/标题

不要聚合：本来是什么值就显示什么值，用于透视值为文本的情况

4.7 分组依据

分组依据：将数据按照某一列或几列进行汇总

基本分组依据：单个分组依据，单个计算的值

• 所有行：把源数据按照分组依据拆成小表

  

  ​ 每一个表可以进行展开和聚合操作，聚合可以更改聚合的方式，展开可以选择要展开的字段

  

​ 高级分组依据：多个分组依据，多个计算的值

业务理解：订单明细表的订单编号为什么是重复值

订单明细表是将每一笔订单进行拆分，每一个订单中的每一个产品为一条数据，我们日常在购买时，可能一次买很多东西，此时订单编号是一个，但是购买的产品是多个，当把每一笔订单按产品拆分时，订单编号就变为重复值了，每一个产品的订单编号对应他所在订单的订单编号。

分组时遇到文本类型的值，如何用M函数解决

先进行分组依据的操作，然后点击高级编辑器，找到分组的行

分组的行 = Table.Group(更改的类型, {"区域"}, {{"销售代表", each List.Sum([销售代表]), type text}, {"金额", each List.Sum([汇总金额]), type number}})

改为（销售代表的List.sum改为Text.Combine）

分组的行 = Table.Group(更改的类型, {"区域"}, {{"销售代表", each Text.Combine([销售代表]), type text}, {"金额", each List.Sum([汇总金额]), type number}})

4.8 添加列

所有添加列下面的按钮都会新建一个列

索引列：可作为辅助列，避免透视时标题长度过长的问题

添加条件列：Power Query中条件语法是if…then…else，相当于excel中的if函数

自定义列：通过自定义公式进行列的增加

重复列：只能一列一列进行操作，不能多列操作

4.9 追加查询

行增加：追加，有可能导致列的增加

列增加：合并

追加查询：会把要追加的数据直接在原本数据基础上进行追加

将查询追加为新查询：新建一个表，追加后的数据在这个表中

纵向的追加，一般情况下是把格式一样的数据追加到同一张表格里面，追加原理：

（1）若两个表中的字段位置不同，以第一张表中字段位置为准

（2）若两个表中字段数量不同，例如表1有店名列，表2没有，在进行追加查询时，会新增店名列，表2没有，则表2店名列的数值为null

（3）追加时相应的列名称要 一致，否则会新增列

多文件汇总——从Excel工作簿汇总

（1）先加载一张表，然后点击源，保留name和data列，删除其他列

（2）点击table，可以看到数据

（3）展开table，选择所有列，则表的数据都已经加载出来

（4）对合并时产生的重复项进行处理

数据透视表的筛选有拆表的功能，如果拆完后要合起来，可以用追加的方式

多文件汇总——从文件夹汇总多个Excel文件

（1）获取数据，从文件夹导入，点击转换数据

（2）保留第一列，删除其他列

（3）自定义一个列，利用M函数

自定义列公式，点击确定

Excel.Workbook([Content])

（4）展开table，然后继续展开data，可得到数据（在调整标题时，注意数据类型）

多文件汇总——从文件夹汇总多个csv文件

方式一：

（1）点击导入数据，从文件夹导入

（2）点击合并并转换数据，进入后数据已经合并成功

方式二：

（1）获取数据，从文件夹导入，点击转换数据

（2）保留第一列，删除其他列

（3）点击任意一个城市的content列，然后点击历史应用步骤的Content

修改为

Binary.Combine(删除的其他列[Content])

（4）修改后点至最后一步，对合并后的重复项进行处理

4.10 合并查询（类似于VLOOKUP）

合并查询：对表格的横向合并

合并查询后选择要展开的字段：

合并时要配对的一定要是唯一值，可以选择两个及以上字段来保证唯一性

联接种类（类似于MySQL的连接查询）：

合并查询上面的表是左表，下边的表是右表

右外部可以得到左外部相同的效果，源于表的位置不同

模糊匹配：

相似性阈值：

如何计算阈值：两张表最长的值的字符长度进行相除，例如内蒙古和内蒙古自治区，相除为0.5，则相似性阈值为0.5，进行模糊匹配

4.11 时间和日期的整理及数据运算

4.11.1 时间和日期的整理

年限：现在(now)和所选日期之间的持续时间

仅日期：提取日期部分（年月日部分）

分析：从文本里提取日期

年：提取年

年开始、年结束：提取对应的年份开始日期或结束日期

季度开始、季度结束：提取对应的季度开始日期或结束日期

月开始、月结束：提取对应的月份开始日期或结束日期

一个月的某些日：此月份共多少天

每周的某一日：周几

一年的某一周：本年度的周数(WEEKNUMBER)

减去天数：两列日期相减

最早、最新：多列日期中保留最早、最晚的一天

扩展：

​ 计算天数时间间隔函数，即当前时间 - 表中日期：DateTime.LocalNow() - [日期]

4.11.2 数据运算

加、减、乘、除
除(整数)：商
取模：余数
绝对值
舍入：向上舍入、向下舍入、保留位数
奇数、偶数：奇数、偶数判断

4.12 新建参数

参数化

（1）自动展示最近N天：将N设置为动态参数

• 先进行年限的计算
• 筛选
• 新建参数

• 应用参数

（2）文件路径的改变

导入Power Query中进行数据处理的源数据如果一旦存储位置发生改变，会影响PQ中的操作步骤，如果数据源位置发生变化该如何解决？

方式一：高级编辑器或者fx编辑栏中更新文件路径，将文件的路径复制到高级编辑器或编辑栏中

方式二：从[源]中重新导入，找到要导入的文件的位置，点击导入

方式三：使用参数动态修改数据源路径，通过创建一个参数，让这个参数代替原始文件路径，使用参数将高级编辑器中的代码替换，注意原路径中的双引号不要保留，使用Power Query参数时，要选择开启参数化，当数据源的位置发生变化，只需要修改 一个参数，其他的则可自动修改。

五、M函数的简单介绍

M函数的基本规范

• M函数对大小写敏感，每一个字母必须按函数规范书写，第一个字母都是大写
• 表被称为Table，每行的内容是一个Record，每列的内容是一个List
• 行标用大括号{ }，比如取第一行的内容：= 表{0} //PQ的第一行从0开始
• 列标用中括号[ ]，比如取自定义列的内容：= 表[自定义]
• 取第一行自定义列的内容：= 表{0}[自定义]

常用的M函数

• 聚合函数:
  求和：List.Sum()
  求最小值：List.Min()
  求最大值：List.Max()
  求平均值：List.Average()
• 提取数据函数:
  从Excel表中提取数据：Excel.Workbook()
  从Csv/Txt中提取数据：Csv.Document()
• 文本函数:
  求文本长度：Text.Length()
  去文本空格：Text.Trim()
  取前n个字符：Text.Start(文本,n)
  取后n个字符：Text.End(文本,n)
• 条件函数:
  if…else…then…（相当于Excel中的IF函数）

案例：中国式排名

公式：每组排名 = 总占位 - 每组最小占位 + 1

总占位：去重后的唯一值
，当数据源的位置发生变化，只需要修改 一个参数，其他的则可自动修改。

五、M函数的简单介绍

M函数的基本规范

• M函数对大小写敏感，每一个字母必须按函数规范书写，第一个字母都是大写
• 表被称为Table，每行的内容是一个Record，每列的内容是一个List
• 行标用大括号{ }，比如取第一行的内容：= 表{0} //PQ的第一行从0开始
• 列标用中括号[ ]，比如取自定义列的内容：= 表[自定义]
• 取第一行自定义列的内容：= 表{0}[自定义]

常用的M函数

• 聚合函数:
  求和：List.Sum()
  求最小值：List.Min()
  求最大值：List.Max()
  求平均值：List.Average()
• 提取数据函数:
  从Excel表中提取数据：Excel.Workbook()
  从Csv/Txt中提取数据：Csv.Document()
• 文本函数:
  求文本长度：Text.Length()
  去文本空格：Text.Trim()
  取前n个字符：Text.Start(文本,n)
  取后n个字符：Text.End(文本,n)
• 条件函数:
  if…else…then…（相当于Excel中的IF函数）

案例：中国式排名

公式：每组排名 = 总占位 - 每组最小占位 + 1

总占位：去重后的唯一值