PowerBI及PowerQuery初识

前言

• 数据分析的原理

  ​ 用分类(维度)和比较的方法来回答问题

• 实现的步骤

  • 分类
    • 即维度，结合不同的维度做分析
  • 比较
    • 度量值(指标)
      • 从数据源中利用DAX公式创建一个虚拟字段的数据值
      • 如销售量、门店数量、环比增长率等
    • 比较的方法
      • 不同类别的同一度量值的比较(较为常见)
      • 同一列别的不同度量值的比较
  • 回答问题
    • what
      • 查看现状及预测未来
    • why
      • 为什么出现what中的问题
    • how
      • 基于现状及原因的分析，得出结论

PowerBI

• PowerBI

  一款数据分析和可视化的工具;可以从各种数据源中提取数据,并对数据进行整理分析,然后生成精美的图表,并且可以在电脑端和移动端与他人共享.
  
  PowerBI包含：
  	桌面版(PowerBI Desktop)、在线PoweBI服务和移动端PowerBI应用
  	PowerBI Desktop是其精髓
  

• PowerBI、PowerQuery、PowerPivot、PowerView

  • Power Query
    • 用于数据处理，对各种数据转换、处理；提炼出我们需要的数据
  • Power Pivot
    • 用于数据建模，即创建数据模型、建立关系及创建计算；使用的语言为DAX
  • PowerView
    • 数据可视化
  • PowerBI
    • Power Query、Power Pivot、Power view等全部功能聚集到一起，呈现了现在的PowerBI

• PowerBI的核心:度量值

  • 度量值

    是用DAX公式创建一个虚拟字段的值，它不改变源数据，也不改变数据模型;一般用于报表交互
    

  • 特点

    - 随着上下文环境的不同而展示不同的数据
    	- 上下文分为外部上下文和内部上下文
    	- 上下文即为度量值所处的环境：筛选表的行列标签、切片器的选中等
    - 不浪费内存，只有被拖拽到图表上才执行运算
    - 可以循环使用
    

• PowerBI的数据分析语言：DAX

  • DAX

    DAX主要用来查询和运算,即通过查询函数负责筛选出有用的数据集合，然后利用DAX的聚合函数执行计算
    

  • DAX的使用范围

    在PowerBI Desktop的数据建模和PowerPivot中使用
    可以进行度量值的创建和新建列(不建议使用)：
    月份=format('日期表'[日期],"MM")
    

  • DAX参数的基本格式

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

  • DAX常用函数

    • 聚合函数

      SUM
      AVERAGE
      MIN
      MAX
      

    • 迭代函数：可以循环访问表的每一行并执行计算

      SUMX
      AVERAGEX
      MINX   
      MAXX
      RANKX
      

    • 其他

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

    • 时间智能函数

      - PREVIOUSYEAR/Q/M/D:上一年/季/月/日
      - NEXTYEAR/Q/M/D:下一年/季/月/日
      - TOTALYTD/QTD/MTD:年/季/月初至今
      - SAMEPERIODLASTYEAR:上年同期
      - PARALLELPERIOD:上一期
      - DATESINPERIOD:指定期间的日期
      

    • 筛选函数

      FILTER 筛选
      ALL 所有值
      ALLEXCEPT 保留指定列
      VALUES 返回不重复值
      

• PowerBI中最重要的函数:CALCULATE

  • 语法

    CALCULATE(<expression>,<filter1>,<filter2>…)
    
    - 参数一：计算表达式，可以执行各种聚合运算
    - 参数二：筛选条件，可以为空，多个条件时，用逗号隔开
    - 所有筛选条件的交集形成最终的筛选数据集合
    - 根据筛选出的数据集合执行第一个参数的聚合运算并返回运算结构
    

  • 示例

    产品数量2 = CALCULATE([产品数量],'产品明细'[品牌]="苹果")//由于参数二的限制，只筛选品牌为'苹果'的
    
    产品数量3 = CALCULATE([产品数量],ALL('产品明细')) //ALL('产品明细')的意思是清除产品明细表里的所有筛选，外部筛选不起作用
    

• CALCULATE函数的最佳搭档：FILTER

  • 语法

    FILTER(<table>,<filter>)
    
    - 第一个参数：<table>是要筛选的表
    - 第二个参数：<filter>是筛选条件
    - 返回一张表，不能单独使用，需要与其他函数结合使用
    
    CALCULATE(...,FILTER(ALL(...),....)) //经常用到的组合
    

  • 示例

    找出年销售额超过2000万的城市的销售金额
    	- 销售总额=SUM('销售明细'[销售额])
    	- CALCULATE([销售总额],FILTER(ALL('门店城市'),[销售总额]>20000000))
    	- 先过滤出销售额大于2000万的城市，再计算总额
    

PowerQuery

• 支持从多种源导入数据

  几乎可以从任何来源、任何结构、任何形式上获取数据
  不仅能从本地获取数据，还能从网页抓取数据
  

• 横向/纵向追加数据

  • 添加列

    • 添加重复列

      重复列即把选中的列复制一列，以便对该列的数据进行处理而不损坏原有列的数据
      
      步骤：
      	添加列选项卡--重复列
      

    • 添加索引列

      索引列就是为每行增加个序号，记录每一行所在的位置，可以从0或者1开始
      
      步骤：
      	添加列选项卡--索引列
      

    • 添加条件列

      添加一列根据指定条件从其他列计算的数据,即if函数
      
      步骤：
      	添加列选项卡--条件列
      

    • 添加自定义列

      自定义列即使用M函数生成新的一列
      
      步骤：
      	添加列选项卡--自定义列
      

  • 追加查询

    追加查询是在现有记录的基础上，在下边添加新的行数据，是一种纵向合并
    例如：当两个表格式相同，需要合并为一张表时，点击'追加查询'
    
    步骤：
    	主页--追加查询
    

  • 合并查询

    合并查询可以理解为横向合并
    选择两个表需要匹配的字段，选择联接种类，合并完成后展开新的列，勾选需要的字段
    
    步骤：
    	主页--合并查询
    

• PowerQuery中常用的13个功能

  • 提升标题

    在PowerQuery中从第一行开始就是数据，标题在数据之上，而excel中第一行为标题，第二行开始才是数据，因此从excel导入的数据需要提升标题
    
    步骤：
    	转换选项卡--标题提升
    

  • 更改数据类型

    设置数据类型很简单但也很重要,数据类型不正确会导致后期的数据建模和可视化过程中，会出现一些错误
    
    设置数据类型的两种方式：
    	- 转换选项卡下--数据类型
    	- 点击列标题最左侧按钮进行设置
    

  • 删除错误/空值

    数据导入后可能会出现错误或者空值
    
    实现：
    	点击列标题--右键--选择删除错误\替换错误
    	点击列标题向下箭头--将null勾选掉
    

  • 删除重复项

    选中需要删重的列，右键选择[删除重复项]
    

  • 填充

    在excel数据中会遇见合并单元格的情况，导入后就变为空值,在PowerQuery中进行如下设置即可：
    	转换选项卡--填充--向下
    

  • 合并列

    选择需要合并的列，点击[转换]选项卡，找到'合并列',在合并窗口内进行'分隔符'和'新列名'的设置
    

  • 拆分

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

  • 分组

    转换选项卡--分组依据
    
    在合并窗口内将对应的参数进行填写后即可
    

  • 提取

    提取功能可以按照长度、首字符、尾字符、范围等来提取，比如下面这个例子，提取前2个字符
    

  • 行列转置

    数据处理有的时候需要行列互相转换一下，比如把各个城市变成列标签，月份变成行标签，点击行列转置
    
    选中要转置的列--点击转换选项卡--点击转置
    
    问题：
    	由于转置只转数据的部分,点击完转置后会发现除了转置的列以外其他的列都不见了
    解决：
    	转置之前先进行：转换--将标题作为第一行后在进行转置就可以了
    

  • 行列操作

    行列操作可进行：
    	选择列：
    		选择列
    		转到列
    	删除列：
    		删除列
    		删除其他列
    	保留行：
    		保留最前面几行
    		保留最后几行
    		保留行的范围
    		保留重复项
    		保留错误
    	删除行：
    		删除最前面几行
    		删除最后几行
    		删除间隔行
    		删除重复项
    		删除空行
    		删除错误
    

  • 逆透视列

    由于数据分析的需要，我们需要将二维表转换为一维表，因此需要用到此功能
    
    步骤：
    	转换选项卡--逆透视列--逆透视其他列
    

  • 透视列

    为了展现数据的需要，需要把一维表变成二维表，因此需要此功能
    
    步骤：
    	转换选项卡--透视列--弹筐内值列内选择'值',聚合值函数选择'不要聚合'
    

• M函数

  • 作用

    • 有些复杂的操作必须借助M函数
    • M函数更加灵活、简洁高效

  • 基本规范

    - M函数对大小写敏感，每一个字母必须按函数规范书写，第一个字母都是大写
    - 表被称为Table,每行的内容是一个Record,每列的内容是一个List
    - 行标用大括号{}，比如取第一行的内容：=表{0} //PowerQuery的第一行从0开始
    - 列标用中括号[],比如取自定义列的内容：=表[自定义]，取第一行自定义列的内容：=表{0}[自定义]
    

  • 常用的M函数

    • 聚合函数

      求和：List.Sum()
      求最小值：List.Min()
      求最大值：List.Max()
      求平均值：List.Average()
      

    • 文本函数

      文本长度：Text.Length()
      文本空格：Text.Trim()
      取前n个字符：Text.Start(文本，n)
      取后n个字符：Text.End(文本，n)
      

    • 提取数据

      从Excel表中提取数据：Excel.Workbook()
      从Csv/Txt中提取数据：Csv.Document()
      

    • 条件函数

      if else then （相当于Excel中的IF)