数据处理和Excel中的power query

一、Excel中的power query

数据处理（power query），也可以使用Excel打开，与powerBI查询组中转换数据打开功能完全一样。

 

在excel中选中数据，然后选择数据，来自表格/区域，即可打开。

 

以下分别是Excel中和powerBI中的power query编辑器，可以看到左上角图标不一样，其他差的不多。

 

 

 

 

二、Excel中的power query操作

对数据进行操作一，原始数据如下：【在Excel中的power query中操作】

 

举例1：点击数学左边的123，选择更改，更改数学为文本显示ABC。见下图。

 

举例2：选中姓名列，在开始选项卡，点击转换组中，将标题作为第一行。见下图。

 

举例3：删除某一列，并且可以多操作重新取名。这里删除英语列，并且重取名为：删除英语成绩。在开始选项卡，选中英语列，点击删除列，在应用的步骤中点击删除的列操作，重命名为：删除英语成绩。见下图。

 

 

 

举例4：对数据进行拆分。在开始选项卡，在转换组中，选择拆分列，可以选择按分隔符，也可以选择按字符数。以下是按字符数拆分，设置字符数为1，拆分：一次，尽可能靠左。见下图。

 

 

 

举例5：分组进行统计。在开始选项卡，在转换组中，选择分组依据，这里是根据性别统计语文的平均值。设置分组依据为性别，新列名为语文的平均值，操作为平均值，柱表示操作对象，这里设置操作对象为语文。见下图。

 

 

 

举例6：对数据进行转置。在转换选项卡，在表格组中，选中全部数据或者某一数据，点击转置，则会得到整体的转置，再次点击转置，则会得到原始数据，但是此时列名没有了。见下图。

 

 

  

举例7：对数据进行反转行。点击需要反转的列，在转换选项卡，在表格组中，点击反转行，则会得到反转的排列。见下图。

 

举例8：关闭并上载。当对表格完成了一系列的处理，需要关闭并且保存时，在开始选项卡，左上角，点击关闭并上载，此时在Excel中会出现另外一张处理完成的表，并且在右侧出现查询&连接，此时可以看到表格名称等信息。见下图（对表格做了转置）。

 

当对sheet1进行修改，sheet2刷新之后（在表格工具中，点击查询，在加载选项卡中点击刷新），也会做出相应的改变。这里对sheet1进行添加信息。见下图。

 

对数据进行操作二，原始数据如下：【在Excel中的power query中操作】

举例1：索引列。选中某一单元格，在添加列选项卡中，选择索引列，此时表格会增加一列索引，可以选择索引从0开始，或者从1开始，默认添加的索引列在最后一列，可以通过鼠标拖动到想要放置的位置。见下图。

 

举例2：重复列。选中某一列，在添加列选项卡中，选择重复列，此时会出现一个重复的列，默认添加的重复列也在最后一列。见下图。

 

举例3：拆分列。选中想要拆分的列，在转换选项卡中，选择拆分列，这里选择按分隔符拆分，并且可以对拆分的列进行重命名。见下图。

 

 

 

举例4：提取。选中想要提取的列，在转换选项卡中，选择提取，可以按很多种方法进行提取，这里我们按照分隔符之间的文本进行提取。见下图。

 

 

对数据进行操作三，原始数据如下：【在Excel中的power query中操作】

举例1：新建列。在添加列选项卡中，点击条件列，这里设置条件为，当语文大于等于60的时候，输出及格，否则不及格，并且设置新列名为：语文等级。见下图。

 

 

举例2：自定义列。在添加列选项卡中，点击自定义列，这里求语文、数学和英语的平均值。见下图。

 

 

 

这里求数学的log值，需要自己添加函数Number.Log10()，见下图。【power puery中视图中可以勾选编辑栏，此时操作中会显示M语言代码，注意：M语言大小写敏感。M语言函数引用：Power Query M 函数参考 - PowerQuery M | Microsoft Docs】

 

 

 

这里展示文本函数的一个例子，求文本的长度，函数为：Text.Length()，

 

 

对数据进行操作四，原始数据如下：【在Excel中的power query中操作】

 

举例1：透视【在Excel中操作】。选中表格中所有数据，在插入选项卡中选择数据透视表，在界面右侧拖入行、列和值，此时会出现如下的透视表。那么如何让这张透视表恢复之前的样子呢？（答案是需要逆透视，逆透视如何做呢？）

 

举例2：透视。在转换选项卡，任意列组中，选择透视列，这里要透视科目，因此选择科目这一列中的名称创建列，值列选择为分数，在高级选项的聚合函数中，选择平均值。见下图。

 

 

 

举例3：逆透视【宽表变窄表】。比如让学生（如吉吉国王等）的语文和数学成绩叠着放，英语单独放。这里要逆透视语文和数学，因此选择语文和数学列，在转换选项卡，任意列组中，点击逆透视列。见下图。

 

举例4：逆透视【宽表变窄表】。让刚刚透视的表恢复原样。这里要逆透视语文、数学和英语，因此选择语文、数学和英语列，在转换选项卡，任意列组中，点击逆透视列。见下图。

 

 

对数据进行操作五，原始数据如下：【列合并的透视，在Excel中的power query中操作】

 

举例1：填充。数据表格在power query中打开（表包含标题默认勾选），出现了null，因此需要填充。在转换选项卡，任意列组中，选择填充，可以向上或者向下填充，这里选择向下填充。见下图。

 

举例2：合并列。在转换选项卡，文本列组中，选择合并列，这里合并日期列和季度列，分隔符选择逗号，新列名为日期。见下图。

 

 

 

举例3：逆透视【宽表变窄表】。选中铠爹、吉吉国王和雷震子列，在转换选项卡，任意列组中，选择逆透视。见下图。

 

 

对数据进行操作六，原始数据如下：【行合并的透视，在Excel中的power query中操作】

 

举例1：转置、填充和逆透视。（1）数据表格在power query中打开（表包含标题取消勾选），出现了null，因此需要填充。见下图。

 

 

（2）数据表格打开之后，在转换选项卡，表格组中，选择转置，因为行不干净，进行转置之后，对列的操作比较多。

 

 

（3）然后在任意列组中先选择向下填充。

 

 

（4）然后在开始选项卡，转换组中，选择将第一列用作标题，此时表格变得很干净。

 

 

（5）最后可以对表格进行逆透视，选中2017、2018、2019和2020四列，在转换选项卡，任意列组中，选择逆透视（宽表变窄表）。

  

对数据进行操作七，原始数据如下：【列合并、行合并的透视，在Excel中的power query中操作】

 

举例1：填充、转置和逆透视。（1）读取数据。见下图。

 

 

（2）选中列1和列2，在转换选项卡，任意列组中选择向下填充。

 

 

 （3）对数据表进行转置，在转换选项卡，表格组中选择转置。

（4）选中column1和column2，在转换选项卡，任意列组中选择向下填充。 

 

 （5）对数据表进行转置，在转换选项卡，表格组中选择转置。

 

 （6）合并列，选中列1和列2，在转换选项卡，文本列组中选择合并列。

 

 （7）对数据表进行转置，在转换选项卡，表格组中选择转置。

 

 （8）选择第一行，在在转换选项卡，表格组中点击将第一行作用标题。

 

 （9）选中前两列，在转换选项卡，任意列组中选择逆透视，在下拉的箭头中，选择逆透视其他列。

 

对数据进行操作八，原始数据如下：【表的合并，在powerBI中操作】

 

举例1：对数据进行预处理，删除null值。在主页，减少行组中，选择删除行，在下拉箭头中中选择删除空值；选中带有null的列，在主页，管理列组中，选择删除列。对所有带null的表都进行此操作。见下图。

 

 

 

举例2：合并表。在主页选项卡，组合组中，选择合并查询，在下拉箭头中选择将查询合并为新查询，这样做的好处是，合并的新表会另外存放。

 

（1）单列合并查询，左外合并。选择需要合并的表和列名，在联接种类中，选择左外部，点击确定。此时合并的表中，单-表2的信息没有完全展示，需要一个个点进去查看，因此点击单-表2右上角两个向外的箭头，此时出现以下选项，我们需要展示数学和英语，勾选数学和英语即可。见下图。

 

 

 

 

 

 

 

（2）两列（多列）合并查询，右外合并。选择需要合并的表和列名，注意这里选择了两列，顺利不能错，在联接种类中，选择右外部，点击确定。此时合并的表中，多-表2的信息没有完全展示，需要一个个点进去查看，因此点击多-表2右上角两个向外的箭头，此时出现以下选项，我们需要展示全部，勾选全部即可。因为选择的是右外联接，所以需要删除英雄和角色前两列。见下图。

 

 

 

 

 

 

 

 

 

（3）一对多表格的合并查询，左外合并。选择需要合并的表和列名，注意这里选择了两列，顺利不能错，在联接种类中，选择右外部，点击确定。此时合并的表中，一对多-表1的信息没有完全展示，需要一个个点进去查看，因此点击一对多-表右上角两个向外的箭头，此时出现以下选项，我们需要展示火力，勾选火力即可。见下图。