PowerBI入门学习笔记

下载安装

Win10系统，在微软商店里直接下载PowerBI desktop，打开即可。
界面如下：

接下来导入后面要用到的数据，我目前用的都是Excel文件，“获取数据”，选中后选择要导入的若干个工作表：

点击“加载数据”就进入到power query编辑器，用于对数据进行清洗等操作。进入pq还有几种方式，比如当你直接确定导入工作表了，可以点击【主页】中的【转换数据】或右键某个你要清洗的表，点击【编辑查询】。

一、Power Query

（一） 横向追加数据

1. 添加列

在pq的【添加列】中，

• 重复列
  右键所要重复的列，点击【重复列】，即可得到列“列名-复制”

• 索引列
  为每行增加个序号，记录每一行所在的位置，可以从0或者1开始

• 条件列
  添加一列根据指定条件从其他列计算的数据，打开窗口看看，其实就是 if 函数。
  

• 自定义列
  自定义列就是使用M函数生成新的一列，例如这里，添加一列合计，为三大产业之和，得到：
  
  

• 合并查询
  相当于Excel的VLOOKUP函数。例如我要将订单表中每个客户购买的产品名称通过【客户姓名】匹配到客户工作表，新增一列：
  
  
  步骤：点击工作表【客户】，点击主页的【合并查询】，
  
  跳出窗口，在方框中选中sheet页，再点击两个表的匹配字段【客户姓名】使其呈灰色，就可以了，
  
  得到如下：
  
  订单列可选中你想匹配过去的东西，比如【产品名称】等，任选：
  
  这里我选了【产品名称】和【数量】，这样工作表【订单】的【产品名称】和【数量】就通过客户姓名匹配到工作表【客户】中了。

（二）纵向追加数据

• 追加查询
  在现有记录的基础上，在下边添加新的行数据，比如有两个表格式相同，需要合并为一个表，点击“追加查询”，两个表的结构应一致。

（三）提升标题

当Excel工作表导入进去后，将excel里第一行作为标题，可点击【转换】中的【将第一行用作标题】，但我这里导入就已经将第一行作为了标题了，

我点了一下【将第一行用作标题】，出现了将第一行数据作为标题了，要撤销这一步，ctrl+Z没用，而是分别删除【更改的类型】右边的步骤【提升的标题】。

（四）更改数据类型

点击字段名称左边那个东西，进行数据类型的更改。

（五）删除错误/空值

对有空值或错误的列，右键【删除错误】或【替换错误】；点击筛选，【删除空】

（六）删除重复项

右键【删除重复项】

（七）填充

从excel中导入的合并单元格的数据会出现这种情况，


可以通过向下填充的方式补齐null值，【转换】→【填充】→【向下】：

（八）合并列

在PQ中选择需要合并的列，然后在「转换」中找到”合并列“，弹出合并列窗口。现在合并列【客户姓名】和【客户代码】，用冒号连接，


不是新增列哦，合并了就取代了原本的两个列：

（九）拆分

拆分相当于是合并列的反动作，不过功能更丰富，可以选着按字符数，也可以选择按分隔符，如果列中包含多个分隔符，还可以选择按哪个位置的分隔符来拆分。把刚刚合并的列拆开：

有几种方式，这里用分隔符：

（十）分组

相当于Excel中的分类汇总功能，比如我要计算这些省第一产业的总和，点击分组依据：


【新列名】是自定义的，【柱】是要汇总的列

（十一）提取

PQ的提取功能可以按照长度、首字符、尾字符、范围等来提取，比如刚刚合并的这个列，提取冒号之前的文本，【转换】→【提取】→【分隔符之前的文本】：

（十二）行列转置

比如这个表，点击【转换】中的【转置】后，


发现【年度】和【第一产业】等没了，这是因为转置只转了数据区，字段标题不在数据区，要把它们降下来，【将第一行用作标题】→【将标题作为第一行】

再转置：

（十三）逆透视列

这是PQ非常便捷的逆天功能，由于数据分析的需要，我们经常要将二维表变为一维表，通过逆透视功能，可以一键降为一维表。例如二维列联表：

选中列【地区】和【年度】，通过【转换】→【逆透视列】→【逆透视其他列】

就转换为一维的：

（十四）透视列

做分析需要一维表，而为了展现的需要，常常还要把一维表变成二维表，也就是Excel中的数据透视，在PQ中同样可以一键透视，比如把刚才的一维表变成原样，聚合方式选择“不要聚合”。
对刚刚那个逆透视的表，将【值】进行透视：

（十五）M函数

略过……以后有时间再研究

二、PowerBI

（一）数据建模

即找寻各个工作表之间的关系，联结起来，我初步理解为SQL中的表联结。例如，这6个工作表：

观察每个表的外键，在【模型】中建立模型，将一个表的外键选中拖到另一个相同的外键，中间就有一条线连起来了。

双击那个三角形，

【基数】是指两个连接字段的对应关系，分为多对一、一对一和一对多，一对多和多对一其实是一样的，实际上就是两种关系：

• 多对一（*：1）：这是最常见的类型，代表一个表中的关系列有重复值，而在另一个表中是单一值
• 一对一（1：1）：两个表是一对一的关系，列中的每个值在两个表中都是唯一的
  具有唯一值的表通常称为“查找表”，而具有多个值的表称为“引用表”。
  【交叉筛选方向】表示数据筛选的流向，有两种类型：
• 双向：两个表可以互相筛选
• 单向：一个表只能对另一个表筛选，而不能反向
  这个有点抽象，目前我没懂。

（二）度量值

度量值是用DAX公式创建一个虚拟字段的数据值。仍然以上面6个工作表为例，新加度量值【销售总额】、【本年累计销售额】、【上年累计销售额】、【同比增长率】。
在【数据】中点击【新建度量值】，在编辑栏输入销售总额 = sum('销售明细'[销售额])

本年累计销售额 = TOTALYTD([销售总额],'日期表'[日期])

上年累计销售额 = TOTALYTD([销售总额],SAMEPERIODLASTYEAR('日期表'[日期]))

同比增长率 = DIVIDE([本年累计销售额],[上年累计销售额])-1

这里的DAX函数会在后面深入DAX的文章说明。

现在来可视化，点击【矩阵】，将【月】拖至【行】，将度量值【销售总额】拖至【值】

得到：

显然这是不分2016年2017年的，因此做一个【切片器】。点击【切片器】，将【年度】拖至【字段】：


再点击【矩阵】，将【月】拖至【行】，将【本年累计销售额】、【上年累计销售额】、【同比增长率】拖至【值】：

得到：

整个可视化结果如下：

点击不同【年份】结果跟着变化。

（三）DAX函数

1. DAX参数的基本格式

• 表名用单引号’ '括着 如 ‘日期表’
• 字段用中括号[ ]括着 如[日期]
• 度量值也是用中括号[ ]
• 引用字段始终要包含表名，以和度量值区分开

2. DAX常用函数

（1）聚合函数

• SUM
• AVERAGE
• MIN
• MAX
  这几个函数跟Excel中一样。
• SUMX
• AVERAGEX
• MINX
• MAXX
• RANKX
  这几个函数可以循环访问表的每一行，并执行计算，所以也被称为迭代函数。

其他的聚合函数：

• COUNT:计数
• COUNTROWS：计算行数
• DISTINCTCOUNT:计算不重复值的个数

（2）时间智能函数

• PREVIOUSYEAR/Q/M/D:上一年/季/月/日
• NEXTYEAR/Q/M/D:下一年/季/月/日
• TOTALYTD/QTD/MTD:年/季/月初至今
• SAMEPERIODLASTYEAR:上年同期
• PARALLELPERIOD:上一期
• DATESINPERIOD:指定期间的日期
  它们可以灵活的筛选出一段我们需要的时间区间，做同比、环比、滚动预测、移动平均等数据分析时，都会用到这类函数。

（3）筛选函数

• FILTER:筛选
• ALL:所有值，可以清除筛选
• ALLEXCEPT:保留指定列
• VALUES:返回不重复值
• CALCULATE
  这几个函数是典型的DAX查询函数，通过筛选来操纵上下文的范围。
  微软DAX文档：https://docs.microsoft.com/en-us/dax/
  https://docs.microsoft.com/zh-cn/dax/new-dax-functions

CALCULATE：
语法结构：CALCULATE(<expression>,<filter1>,<filter2>…)

• 第一个参数是计算表达式，可以执行各种聚合运算
• 从第二个参数开始，是一系列筛选条件，可以为空；如果多个筛选条件，用逗号分隔
• 所有晒选条件的交集形成最终的筛选数据集合
• 根据筛选出的数据集合执行第一个参数的聚合运算并返回运算结果

案例，工作表【产品明细】：
先看countrows：产品数量 = COUNTROWS('产品明细')，其实就是计算了总行数，

往行里加点东西，


接下来看calculate：

• 筛选内容为空
  创建度量值【产品数量1】产品数量1 = CALCULATE([产品数量]) 也可以产品数量1 = CALCULATE(COUNTROWS('产品明细'))
  

• 添加限制条件
  添加度量值【产品数量2】产品数量2 = CALCULATE([产品数量],'产品明细'[品牌]="苹果")
  
  只有苹果的产品计数显示出来了。

• 结合ALL函数，扩大上下文
  创建度量值【产品数量3】产品数量3 = calculate([产品数量],ALL('产品明细'))
  

• 重置上下文
  创建度量值【产品数量4】产品数量4 = CALCULATE([产品数量],ALL('产品明细'[产品名称]),'产品明细'[类别]="手机")
  
  filter：
  语法：FILTER(< table>,< filter>)

• 第一个参数< table>是要筛选的表

• 第二个参数< filter>是筛选条件

• 返回的是一张表，不能单独使用，需要与其他函数结合使用
  案例（上面度量值的数据）：
  度量值[产品数量2] =CALCULATE([产品数量],'产品明细'[品牌]="苹果")
  与度量值[产品数量5]= CALCULATE([产品数量],FILTER(ALL('产品明细'[品牌]),'产品明细'[品牌]="苹果"))返回结果一样。先看度量值1 = FILTER(ALL('产品明细'[品牌]),'产品明细'[品牌]="苹果")的结果：
  
  
  这时看起来似乎filter比calculate要麻烦，但其实它功能更强大。
  现在想找出年销售额超过2000万的城市销售金额，这个利用简单的表达式已无法解决，来看FILTER如何实现。
  度量值1销售总额 = sum('销售明细'[销售额])
  度量值2大于2000万的城市销售总额 = CALCULATE([销售总额],FILTER(ALL('销售明细'[门店城市]),[销售总额]>20000000))
  
  尽管这里可以得到大于2000万的城市销售总额，但很明显其合计不等于总合计。

CALCULATE(…,FILTER(ALL(…),…))      做各种分析时会经常用到这个组合。