一、导入数据及切换存储位置

1.导入数据

数据>获取数据>启动Power Query编辑器>主页>新建源

例如：导入CSV文件

数据>获取数据>启动Power Query编辑器>主页>新建源>文件>文本/CSV>选择文件>导入>确定>主页>关闭并上载至>现有工作表 或者 新工作表>确定

2.回到Power Query编辑器

1）方法一：数据>获取数据>启动Power Query编辑器

2）方法二：数据>查询和链接>双击需要的链接

3.切换存储位置

加载到Power Query编辑器的不可以直接在进行关闭并上载至，即不能直接对获得的数据表的位置进行修改

1）解决方法一：数据>查询和链接>右键目标链接>加载到>仅创建链接>确定>在右键选择加载到>表>现有工作表 或 新工作表>确定

2）解决方法二：剪切数据表到需要的位置

 

二、行列管理及筛选

1.删除空白行

1）将需要处理的表上传到Power Query中：数据>来自表格/区域

2）删除空白行：主页>删除行>删除最前面几行(可以选择删除前面的多少行)；删除空行

3）提升标题：主页>将第一行用作标题

4）筛选掉中间的标题：点击标题字段的筛选按钮>将需要去除的内容取消勾选

5）修改数据类型：点击标题字段左边的数据类型按钮>选择合适的数据类型

6）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

2.删除间隔行

1）将需要处理的表上传到Power Query中：数据>来自表格/区域

2）提升标题：主页>将第一行用作标题

3）删除间隔行：主页>删除行>删除间隔行>要删除的第一行(填写第一个要删除的行的行号，例如：5)>要删除的行数(填写需要删除几行，例如：1)>要保留的行数(例如：前面删除的数据在第5行，删除一行，则保留的行数为4)

4）过滤掉其他不需要的行：点击标题字段的筛选按钮>将需要过滤的内容取消勾选

5）填充控制：转换>填充>向下 或者 向上

6）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

 

三、数据清洗

1.清除空白字符

选中字段>转换>格式>修正>格式>清除

2.将多个连续的空白字符替换为一个

1）将该字段按照空白字符分为两个字段：选中字段>转换>拆分列>按分隔符>选择或输入分隔符：空格>拆分位置：最左侧的分隔符 或者 最右侧的分隔符>确定

2）清除空白字符：选中拆分得来的两个字段>转换>格式>修正>格式>清除

3）转换为首字母大写：选中字段>转换>格式>每个字词首字母大写

4）将处理好的两个字段拼接会一个字段：选中两个字段>转换>合并列>分隔符：自定义为空格(按需求选择)>新列明：为合并后的列起一个列名>确定

 

四、拆分&合并&提取

1）将需要处理的表上传到Power Query中：数据>来自表格/区域

2）提取姓：选中姓名字段>添加列>提取>首字符>计数：1>确定>双击字段名称以修改

3）提取名：选中姓名字段>添加列>提取>范围>起始索引(起始下标为0)：1，字符数：10(按需求填写)>确定>双击字段名称以修改

4）提取出生年份：选中身份证号字段>添加列>提取>范围>起始位置：6，字符数：4>确定>双击字段名称以修改

5）提取首要兴趣：选中兴趣爱好字段>添加列>提取>分隔符之前的文本>分隔符：\（按需求填写）>确定>双击字段名称以修改

6）判断性别：选中身份证号字段>添加列>提取>范围>起始位置：16，字符数：1>确定>双击字段名称以修改>将数据类型转换为整型>添加列>信息>奇数 或者 偶数(返回逻辑值)>将数据类型转换为文本>右键>替换值>按需求填写，要替换的值 和 替换为>确定

7）添加尊称列：

（1）生成尊称：添加列>条件列>新列名：尊称，判断条件：列名为性别，如果为男则输出先生，否则输出女士>确定

（2）拼接尊称和姓：先选中姓，再选中尊称>添加列>合并列>分隔符不需要>新列名：尊称>确定>将原来的尊称删掉

8）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

 

五、转置&反转行

1.转置

1）将数据加载到Power Query中：数据>来自表格/区域

2）转换>将第一行用作标题 下拉选项>将标题作为第一行>转置>将第一行用作标题>将月份转换为文本

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

2.反转行

1）将数据加载到Power Query中：数据>来自表格/区域

2）将字段拆分为四列：转换>拆分列>按字符数>字符数：1，拆分：重复>确定

3）处理：转置>反转行>转置>选中所有字段>转换>合并列>确定

4）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

 

六、分组依据

1.分组依据

1）将数据加载到Power Query中：数据>来自表格/区域

2）主页 或者 转换>分组依据>高级>不够可以添加分组：客户省份；产品名称>聚合字段：列名1为销售数量，操作为求和，柱为数量；列名2为销售金额，操作为求和，柱为金额>确定

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

2.分组依据所有行

新增字段：订单总额，对应订单编号的金额合计

Power Query中的每个单元格可以放一个表

1）将数据加载到Power Query中：数据>来自表格/区域

2）主页 或者 转换>分组依据>高级>根据 订单编号 分组>订单总额，求和，金额；data，所有行>确定

3）点击data字段右边的展开按钮>将订单编号、使用原始列名作为前缀 都取消勾选>确定

4）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

注：每个单元格都可以存储一张表

3.非重复值计数

针对客户id进行计数

1）将数据加载到Power Query中：数据>来自表格/区域

2）主页>选择列>勾选客户省份，客户ID>确定>分组依据>基本>客户省份>客户数量，非重复行计数>确定

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

 

 

七、透视&逆透视

• 透视: 将字段的各项值转换为新的字段, 项: 字段的各项非重复值
• 逆透视: 将字段转换为项

1.透视——聚合

1）将数据加载到Power Query中：数据>来自表格/区域

2）选择要透视的列>转换>透视列>值列(按需求选择)>确定

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

2.透视——不要聚合

1）将数据加载到Power Query中：数据>来自表格/区域

2）选中区域字段>转换>透视列>值列选择 是否完成>高级选项>聚合值函数选择 不要聚合>确定

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

3.逆透视

1）将数据加载到Power Query中：数据>来自表格/区域

2）逆透视操作：

（1）需要逆透视的列数量较少：选中所有需要逆透视的列>转换>逆透视列>修改字段名称

（2）需要逆透视的列数量较多：选中所有不需要逆透视的列>转换>逆透视其他列>修改字段名称

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

 

八、分组依据——文本聚合

1）将数据加载到Power Query中：数据>来自表格/区域

2）分组依据：主页>分组依据>高级>区域>销售代表，求和，销售代表；销售金额，求和，销售金额>确定

3）修改公式：

（1）视图>勾选编辑栏

（2）选中销售代表字段>将 = Table.Group(更改的类型, {"区域"}, {{"销售代表", each List.Sum([销售代表]), type nullable text}, {"销售金额", each List.Sum([销售金额]), type nullable number}}) 改为 = Table.Group(更改的类型, {"区域"}, {{"销售代表", each Text.Combine(List.Distinct([销售代表]), "\"), type nullable text}, {"销售金额", each List.Sum([销售金额]), type nullable number}})

4）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

 

九、日期和时间

将数据加载到Power Query中：数据>来自表格/区域

转换和添加列中都有日期和时间，前者是在原字段中操作，后者是新增字段进行操作

1.年的处理

选中开始时间>添加列>日期>年限

返回值为当前时间减去该时间字段中的时间，中间隔了多少天和时分秒

2.日期

选中开始时间>添加列>日期>仅日期

返回值为所选字段中的年月日

3.年&月&季度&周&天

1）年

（1）选中开始时间>添加列>日期>年

返回值为所选字段中的年

（2）选中开始时间>添加列>日期>年>年份开始值

返回值为所选字段中的年的开始值，如2018/1/1 0：00：00

（3）选中开始时间>添加列>日期>年>年份结束值

返回值为所选字段中的年的下一年的开始值，如原始值为2018/5/25 1:26:33，处理后2019/1/1 0：00：00

2）月

（1）选中时间字段>添加列>日期>月

提取时间字段中的月份

（2）选中时间字段>添加列>日期>月>月份开始值

选中字段中月份的开始值

（3）选中时间字段>添加列>日期>月>月份结束值

选中字段中月份的下个月的开始值

（4）选中时间字段>添加列>日期>月>一个月的某些日

获取选中字段中月份总共的天数

（5）选中时间字段>添加列>日期>月>月份名称

返回值为选中字段中月份的英文名称

3）季度

（1）选中时间字段>添加列>日期>季度>一年的某一季度

返回选中字段中月份所在的季度值

（2）选中时间字段>添加列>日期>季度>季度开始值

返回选中字段中月份所在季度的开始月份的开始值

（3）选中时间字段>添加列>日期>季度>季度结束值

返回选中字段中月份所在季度的下个季度的开始月份的开始值

4）周

（1）选中时间字段>添加列>日期>周>一年的某一周

返回选中字段中时间为一年中的第几周

注意：默认以周天作为一周的开始，需将公式中的对应值改为 each Date.WeekOfYear([开始时间], Day.Monday)

（2）选中时间字段>添加列>日期>周>一个月的某一周

返回值为字段中时间是字段中月份的第几周

注意：默认周天为一周的开始，需将公式中的对应值改为 each Date.WeekOfMonth([时间字段], Day.Monday) 实现从周一开始

（3）选中时间字段>添加列>日期>周>星期开始值

返回值为字段中时间所在周的开始那一天的开始值

注意：默认周天为一周的开始，需将公式中的对应值改为 each Date.StartOfWeek([时间字段], Day.Monday) 实现从周一开始

（4）选中时间字段>添加列>日期>周>星期结束值

返回值为字段中时间所在周的下一周的开始那一天的开始值

注意：默认周天为一周的开始，需将公式中的对应值改为 each Date.EndOfWeek([时间字段], Day.Monday) 实现从周一开始

5）天

（1）选中时间字段>添加列>日期>天

返回日期字段中 天 的部分

（2）选中时间字段>添加列>日期>天>每周的某一日

返回星期的索引号

注意：默认周天开始，索引从零开始，需将公式中的对应值改为 each Date.DayOfWeek([时间字段], Day.Monday) 实现从周一开始

（3）选中时间字段>添加列>日期>天>一年的某一日

该日期是一年中的第几天

（4）选中时间字段>添加列>日期>天>一天开始值

返回值与前面年、季度、周的描述相似

（5）选中时间字段>添加列>日期>天>一天结束值

返回值与前面年、季度、周的描述相似

（6）选中时间字段>添加列>日期>天>星期几

返回当前日期为星期几，返回值为英文形式

4.减去天数

需要选中两个时间字段

选中结束时间，再选中开始时间>添加列>日期>减去天数

返回值为两个时间的天数差

5.合并日期和时间

用于日期和时间的合并

选中日期字段，再选中时间字段>添加列>日期>合并日期和时间

返回值为两个字段的合并字段，数据类型为日期格式

6.最早&最新

1）最早

选择两个时间字段>添加列>日期>最早

返回值为两个时间中过去时间更长的

2）最新

选择两个时间字段>添加列>日期>最新

返回值为两个时间中过去时间更短的

7.时间

1）仅时间

选中时间字段>添加列>时间>仅时间

返回日期时间中的时间部分，即时分秒

2）小时

（1）小时

选中时间字段>添加列>时间>小时

返回字段中的小时部分

（2）小时开头

选中时间字段>添加列>时间>小时>小时开头

返回时间字段所在小时的开始值

（3）小时结尾

选中时间字段>添加列>时间>小时>小时结尾

返回时间字段所在小时的下个小时的开始值

3）分钟

选中时间字段>添加列>时间>分钟

返回日期时间中时间部分的分钟值

4）秒

选中时间字段>添加列>时间>秒

返回日期时间中时间部分的秒值

8.本地时间

选择日期时间时区字段>添加列>时间>本地时间

将原来时间的时区改为当前所在时区的时区值，如：修改前2018/5/25 1:26:00 +09:00，修改后2018/5/25 0:26:00 +08:00

9.持续时间

持续时间字段的格式：天.时:分:秒

添加列>持续时间>......

操作与前面的日期时间操作近似

注：只有持续时间格式的时间字段才可使用

10.分析

选中字段>转换>日期>分析

https://img-blog.csdnimg.cn/8e58056c17934e289b95f4f02df07905.png 黄色的为无法分析的日期格式

注意：部分日期形式解析不出来

 

十、数学运算

1.加&减&乘

加：选中字段>添加列>标准>添加>值：填写要给选中的字段统一添加多少，如：5

减、乘 操作与 加 操作近似

2.除

1）除

与加、减、乘操作相似

2）除(整数)

选中字段>添加列>标准>除(整数)>值：填写要给选中的字段统一除以多少，如：3

返回值为两个数相除的整数部分

3）取模

选中字段>添加列>标准>取模>值：填写要给选中的字段统一除以多少，如：3

返回值为两个数相除的余数部分

3.百分比

1）第一个百分比

选中字段>添加列>标准>第一个百分比>值

返回 字段中的值 * 值 / 100，即先将输入的值转换为百分比形式再乘字段中的值

2）第二个百分比

选中字段>添加列>标准>第二个百分比>值

返回 字段中的值 / 值 * 100，即先将字段中的值除以输入的值再乘100

4.科学计数

添加列>科学计数

绝对值：取绝对值、幂：幂运算、平方根：求平方根、阶乘：求阶乘

特殊：

1）求幂：以自然常数 e 作为底数来求指定的次幂，即e的 字段中值 的次方

2）对数：例如10的2次方等于100，即2为以10为底100的对数

（1）常用对数：就是以10为底进行计算，即求以10为底 字段中值 的对数

（2）自然对数：以e作为底数

 

5.统计信息

针对一行数据进行运算，必须选中所有列才能激活功能

1）求和

选中所有列>添加列>统计信息>求和

对每行数据进行求和

2）对非重复值进行计数

选中所有列>添加列>统计信息>对非重复值进行计数

返回一行中不同值的个数

3）标准偏差：(((一组数据中的每个值跟这组数据的均值的差)的平方和)的平均数)的平方根

4）其他操作与求和近似

 

十一、查询

1.追加查询

union: 上下合并

注意：合并的两个表的字段名称必须保持一致，但是顺序不必一致

（1）将两个表上再到PowerQuery中

（2）主页>追加查询>追加查询(在当前表内追加另一个表或多个表)；将查询追加为新查询(将想要连接的两个表或多个表合并到新的表中处理)>根据需求勾选 两个表 或者 三个或更多表>选择要追加的表>确定

（3）关闭并上载至

2.合并查询

左右合并，相当于join

首先将两个表加载到Power Query

合并两个表：主页>合并查询>将查询合并到新的表>选择两个表>分别点击选中两个表的关联字段>选择需要的连接种类>确定

展开另一个表的信息：点击另一个表名称命名的字段右侧的展开按钮>根据需要勾选字段名称>确定

注意：连接种类类似于数据库

• join: 左右合并,左连接,左连接,内连接,全连接,左反连接,右反连接等
• 左反: 返回左表存在而右表不存在的数据
• 右反: 返回右表存在而左表不存在的数据
• 模糊匹配：将使用模糊匹配执行合并勾选>模糊匹配选项>相似性阈值：当相似度达到多少时就算匹配的

 

十二、合并多个工作表

1.从工作簿合并多个工作表

前提：表的位置、结构和字段名称是一样的

数据>获取数据>来自文件>从工作簿>找到数据的路径>点击导入>先选择一个表>转换数据>将右侧查询设置中应用的步骤只保留源其他操作步骤都删除>只保留Data字段，其他字段全部删除>点击字段名称右侧的展开按钮>取消勾选使用原始列名作为前缀>确定>转换>将第一行用作标题>点击字段的筛选按钮将重复的标题过滤掉>确定>关闭并上载至

2.从文件夹合并多个Excel

1）工作表名称一致(每个文件内的工作表名一致)

数据>获取数据>来自文件>从文件夹>找到文件夹路径>打开>转换数据>只保留字段名为Content的字段其他字段全部删除>点击字段名右侧的合并文件按钮>选择名称一致的那个表，比如Sheet1>确定>关闭并上载至

2）工作表名称不一致(每个文件内的工作表名不一致)

数据>获取数据>来自文件>从文件夹>找到文件夹路径>打开>转换数据>保留字段名为Content的字段其他字段全部删除>添加列>自定义列>给新列起名：data>自定义列公式：=Excel.Workbook(Content) > 确定>点击data字段右边的展开按钮>只勾选Data字段>确定>对该字段再进行展开>确定>将Content字段删除>转换>将第一行用作标题>将重复的表头过滤掉>关闭并上载至

3.从文件夹合并多个CSV

数据>获取数据>来自文件>从文件夹>找到文件夹路径>打开>转换数据>保留字段名为Content的字段其他字段全部删除>添加列>自定义列>给新列起名：data>自定义列公式：=Csv.Document([Content], [Delimiter="分隔符", Encoding=加载数据时的编码格式]) > 确定>将Content字段删除>对data该字段进行展开>确定>转换>将第一行用作标题>将重复的表头过滤掉>关闭并上载至

 

十三、管理参数

类似于Excel创建名称: 创建变量, 变量指向某些值

数据>获取数据>来自文件>从工作簿>选择一个工作簿>导入>随便读取其中的任何一个表>删除右侧查询设置中的 更改的类型>主页>管理参数>新建参数>参数名称自定义>建议的值：将工作簿中的所有工作表名称依次写入>指定默认值>当前值>确定>回到查询表>选中右边 查询设置 里的 导航 >用我们创建的参数名称替换当前的名称，如：= 源{[Item="地区信息",Kind="Sheet"]}[Data] 改为 = 源{[Item=工作表,Kind="Sheet"]}[Data]

注：工作表为创建的参数名称，当我们修改工作表的值时，查询表中的数据也会改变

 

十四、Excel链接MySQL

1.下载并安装连接器

1）Excel专业加强版：MySQL :: Download Connector/NET

2）Excel为家庭学生版： MySQL :: Download Connector/ODBC

2.链接数据库

1）写SQL语句：数据>获取数据>来自数据库>从MySQL数据库>服务器：localhost，如果是服务器的数据库就输入ip地址>数据库：输入要连接的数据库库名>高级>SQL语句：根据需求书写SQL语句>确定>转换数据

2）不写SQL语句：数据>获取数据>来自数据库>从MySQL数据库>服务器：localhost，如果是服务器的数据库就输入ip地址>数据库：输入要连接的数据库库名>确定>选择需要导入的表>转换数据

注：第一次连接数据库需要输入账号密码