数据处理和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的信息没有完全展示,需要一个个点进去查看,因此点击一对多-表右上角两个向外的箭头,此时出现以下选项,我们需要展示火力,勾选火力即可。见下图。

 

 

 

 

 

 

 

posted @ 2022-08-25 16:23  zhang-X  阅读(1687)  评论(0编辑  收藏  举报