PBI 之 Query数据导入、处理

PBI梳理数据流程

首先通过Excel 插件 Power Query、Power Pivot处理数据，然后使用Power Desktop导入处理好的数据进行展示。最后发布到在线网站。

一、导入数据

 

注意：导入数据后如果第一行为标题，那么需要选择将第一行作为标题这个功能，默认第一行不是标题而是数据。 

二、处理数据

如下，进入到Power Query编辑器界面 数据--查询&连接  界面区  界面区右键

向下填充 会将null、空格填上面第一个非空数据值向下填充null、空格

行列操作 

查询编辑器中常会有多个辅助表，用来作为最终表的中间参考表。 

 

 

数据格式的转换

数据的拆分、合并、提取

删除重复项、保留重复项

删除重复项：默认保留多行重复项的第一行；可以选中多列，多列每列都重复才算做重复。

对金额进行降序，删除重复项，就会保留金额最大的重复项。 

 

 排序+删除重复项容易出错

排序如果立马进行删除重复项，需要在排序后进行一些不改变排序结果的操作，这样才会按照最新的排序进行删除重复项。如果过程中有多个排序，如果不进行一些不改变排序结果的操作，就可能会按照先前的排序进行去重。  不改变排序结果的操作，比如排序后，进行检测数据类型。

删除错误 

query编辑器 每列都有一个字段类型，如果有数值不符合此列类型，就会报错。可以通过设定列的类型，将不符合类型的报错，然后通过删除错误删掉那些行。

转置和反转 

转置：行列互换

反转行：行顺序反转

 

 

第一种方法

（1）按照字符拆分列   主页--拆分行--按字符

（2）转置

（3）行反转

（4） 转置

 （5）合并列  依次选中合并的列  转换--合并列

第二种方法

根据合并列特点：会根据选择列的先后顺序 进行顺序合并作为输出结果。 上面的第一个拆分后，直接按照倒序选择列，然后合并列即可完成倒序。

 透视和逆透视

二维：有行标题、列标题

一维：只有行标题

通常逆透视常用，因为处理数据常会处理成只有行标题。 透视就是分类汇总，逆透视就是数据拆分

 

分组依据

分组：将数据按照某一列或者某几列进行分组 

就是分类汇总功能。 柱表示对哪列进行操作；分组依据表示对哪列进行分组。

单列分组

源数据会被替代，所以先在查询栏复制一个表。 

 

多列分组 

操作为所有行，可以只显示某些字段，也可以对分组进行聚合操作

此时计数列显示Table，点击计数标题旁边的箭头可以选择聚合操作为计数，或者展开显示选择的列

 

 添加列

索引列：增加序数列 

重复列：重复指定列作为新列，这样可以保留原始数据

自定义列：可以对已存在的多列进行处理 作为新列的输出；可以使用if 语句，对于不同的输入输出不同的内容。

if语句也可以使用条件列实现同样功能。

PBI大小写敏感，英文字符。

时间日期整理

 分析：将文本格式的日期转换成日期格式

数学运算

选中列数据类型为数字的某个单元格，就可以进行此列的计算。转换、添加列下都有数学运算功能。

追加查询

多张表追加时如果列名和顺序一致，直接进行追加；列名一致，顺序不一致，也会根据主表的顺序进行匹配追加；但是如果列名不一致，那么不同列单独作为一列出现。

多文件汇总

 合并查询

合并查询：对表格的横向合并，类似于excel中的vloopup。就是将其他表的数据查询后添加新列到当前表；追加查询为纵向合并。

导入多个表，然后选择合并查询，选定需要两张表比对列【可以ctrl选定比对多个列】。然后选择合并方式。

合并查询的连接种类

左边集合

右边集合

全部集合

交集

左边集合除去交集

右边集合除去交集

三、练习题

分组排名

如下数据，各部门内部根据金额多少进行排序。注意相同金额排序相同；不同部门都是从1开始排序。

（1）查询编辑器中，复制原始数据成一个名为‘去重、排序、索引’的新表。删除重复行功能 按照部门和金额 两列进行去重，  ;然后进行部门升序、金额降序进行排序，添加索引列。

（2）复制上述表，命名为‘最小值’的新表。分组依据 求得每个部门序号最小值

（3）合并查询。将上述的索引、最小值合并到原始数据表。然后添加列，根据 索引-最小值+1 公式即可得到各部门内部排序。

 

 

生成笛卡尔积表

合并查询  两个表的比对列  一表的1行的值和另一表的多行，就可以生成笛卡尔积表。

 

 

多行属性合并

 

分组依据，使用M函数合并文本，使用求和计算数值。