知识来源:
知网:https://zhuanlan.zhihu.com/p/489053896
CSDN:https://blog.csdn.net/b9567/article/details/123062662
微软官方:https://learn.microsoft.com/zh-cn/power-bi/create-reports/desktop-excel-stunning-report

Power Platform概况

Power Platform 是微软四个产品的统称:Power BI、Power Apps、Power Automate、Power Virtual Agent。可用于操作、显示、自动化和分析数据的方法,可以与Office 365 和 Dynamics 365(以及其他第三方应用程序和其他 Microsoft 服务)一起使用。



power.png




  • Power Apps:提供自定义应用程序开发的低代码开发平台
  • Power Automate:在 Microsoft 服务或其他第三方应用程序之间创建自动化工作流,可以避免执行重复性任务并节省宝贵的时间
  • Power BI:商业分析工具
  • Power Virtual Agent:构建的智能对话机器人

Power BI

基本流程:数据获取——整理——数据建模——可视化分析

1、数据建模

数据建模:将需要分析的各种表建立关系。

数据建模组成部分:

  • 事实表:包含需要进行分析的数据,通常是业务表,其中用来度量计算的值在可视化分析中一般称为度量值
  • 维度表:对某类事务颗粒的抽离及细化(用来做筛选的表)
  • 关系:默认情况下当表和表之间具有相同字段名的时候,系统就会自动建立关系。1对多,1对1和多对多三种关系。(其实跟数据库关联表类似)

数据模型结构:

  • 扁平化结构:解释excel那种表
  • 星型结构:将模型分成事实表和维度表,维度表通过一对多的关系与事实表相关联,事实表通过多个维度表进行过滤。一般事实表在中间,四周被多个维度表包围
  • 雪花结构:星型结构的进一步扩展,在星型结构的基础上,为每一个维度再扩散出更多的维度就是雪花结构
    (ps:理解中,事实表类似数据库中存在的基表,而维度表类似视图)

2、Power Query

Power Query用来做数据获取和转换合并的工具。

  • 数据获取:大部分文本,数据库和 Web Server 都可以直接用Power Query 获取数据
  • 转换数据:对数据格式的修改从而让数据能够更符合我们使用的要求,数据转换时并不会影响到数据源。
  • 合并:把多个不同途径的数据源合并在一起
  • 加载:把经过查找和转换后的数据加载到我们的数据模型中

Power Query 的工作顺序(数据分析前提):查询获取收据——清洗转换合并数据——加载将经过处理的数据加载到模型中以供后续使用

微信图片_20230304210720.png

1、链接:点击获取数据实际上是在执行Power Query的第一个操作:查询数据源。

2、转换/加载:如果数据源不需要转换,可以直接进行加载,如果点击的是转换,将会进入Power Query 的编辑器中。

3、更改源:【转换数据】——【数据源设置】——【更改源】

4、M语言:在Power Query 的编辑器公示栏里的就是M语言。

(在当前 Power Query 编辑器当中所作的各种操作,或是通过写公式,或是直接用界面操作。实际上都是在对数据进行转换。)

5、加载:数据转换后在Power Query 编辑器点击【关闭并应用】,Power Query 就会把最后的数据全部加载到Power BI 文件中

3、Power Query M 语言

每一个 Power Query 的操作步骤,底层实际上都是通过一段公式来实现的,这个公式就叫做 M 语言,M语言是一个介于函数和编程之间的语言,基本是针对列来执行的。

M 公式是严格区分大小写

查看完整M公式:主页——【高级编辑器】

M函数类型:

  • 文件解析类:比如【Excel.Workbook】
  • Text文本类型:比如切割文字【Text.Split】函数
  • Table表类型:比如添加列操作时就会用到 【Table.AddColumn】
  • Record 记录类型:针对记录类型执行的操作
  • List 列表类型:List 是针对列表执行的操作
  • Number 数字类型
  • 日期时间类型
  • 其他类型

4、Power Query 中的三大数据结构

Table表类型

微信图片_20230304223948.png

= #table({"姓名","年龄","性别"},{{"张三",16,"男"},{"李四",23,"女"}})



想要获取表格中指定位置的数据时,可以使用 {} 和 [] 搭配使用

=表[姓名] //获取姓名列
=表{1} //获取第二行
=表[姓名]{1}
=表{[姓名="李四"]} //直接指定条件来获取
=表{[姓名="李四"]}[年龄]

(可以看做[]为定位列,{}为索引行)


Record 记录类型

Record 就表示一行数据,需要通过[]来定义一条记录

record.png


=[姓名="李四",年龄=18][姓名]


List列表类型

={1,2,3,4,5}

={1..100} //从1到100 

={"A".."Z"} //从 A 到 Z 

={[姓名="小红",年龄=18],[姓名="小明",年龄=23]}  // List 中的每一条记录也可以是 Record 类型

= Table.FromRecords({[姓名="小红",年龄=18],[姓名="小明",年龄=23]}) //上一条是需要点击进去的列表包含Record,这一条是以二维形式呈现


={"A".."Z"}{1}


5、Power Query : 将多张工作表进行汇总

微信图片_20230305000953.png
这种分表通常不利于统计分析,通常将若干表格合并成一张表,然后再进行统计分析。

通过UI界面

Power Query新建源——选择合并表格——全部加载(Power Query编辑页面)——【主页】——【追加查询】

通过 M 公式实现【Table.Combine】

新建空白查询,输入

= Table.Combine({产品A, 产品B, 产品C})

这个函数的语法要求是提供 List 类型的参数,List中的每一条记录都是我们要进行合并的表格

用 UI 方式将每个工作表的名字作为新列追加到合并表中

微信图片_20230305002754.png
Name 表示工作表的名字, Data 表示对应工作表中的数据

步骤:
1、当前 Table 中只有 Name 和 Data 是我们需要的数据,所以可以选中这两列,然后右键【删除其他列】
2、将【Data】进行展开,点击Data 列右上角的扩展图标,选择展开
3、取消勾选【使用原始列名作为前缀】,点击确定
4、在【转换】中选择【将第一行用作标题】
5、做一下数据的筛选,可以点击工号列右上角箭头,在筛选其中取消勾选【工号】

用 M 公式将每个工作表的名字作为新列追加到合并表中

//1、取数据
= Excel.Workbook(File.Contents("D:\Power\Book1.xlsx"),null,true)
//Excel.Workbook主要作用是获取 Excel 工作簿中的数据,以 Table 格式返回。
//3个参数分别是 Excel 的文件地址,UseHeaders 是否用数据的第一行作为标题,DelayTypes 是返回的列是否要保留未设置类型的状态

//2、将其他列进行删除
let
    源 = Excel.Workbook(File.Contents("D:\Power\Book1.xlsx"),null,true),
    删除多余列 = Table.SelectColumns(源,{"Name","Data"})
in
    删除多余列

//SelectColumns 是保留指定列

//3、展开
let
    源 = Excel.Workbook(File.Contents("D:\Power\Book1.xlsx"),null,true),
    删除多余列 = Table.SelectColumns(源,{"Name","Data"}),
    展开列 = Table.ExpandTableColumn(删除多余列,"Data",{"Column1","Column2","Column3","Column4","Column5"},{"工号","姓名","日期","销售额","状态"})
in
    展开列


//删除无用信息
let
    源 = Excel.Workbook(File.Contents("D:\Power\Book1.xlsx"),null,true),
    删除多余列 = Table.SelectColumns(源,{"Name","Data"}),
    展开列 = Table.ExpandTableColumn(删除多余列,"Data",{"Column1","Column2","Column3","Column4","Column5"},{"工号","姓名","日期","销售额","状态"}),
    筛选数据 = Table.SelectRows(展开列,each([工号]<>"工号"))
in
    筛选数据
/*
- 【each】 表示每个记录都需要执行
- [] 表示读取某一列字段的数据
- <> 表示不等于
- 这个公式的意思是 每一条记录中的工号列都不等于文字 "工号"时才会返回 true ,表示符合筛选条件
- SelectRows 中的筛选条件可以任意填写,只要对应的函数输出结果返回的是布尔值就可以, each 的后面写上括号只是为了更好的划分结果,可以不用括号
*/

6、Power Query : 二维表分类求和

微信图片_20230305102143.png

通过 UI 界面完成二维表求和

1、 Power Query 编辑器中,选中表面处理,贴合以及模切三个工序,然后点击【转换】中的【逆透视】
2、按照交货日期和工序进行分类,所以这里选中我们要进行分组的维度列,点击【分组依据】

通过 M 公式实现二维表求和

//1、逆透视
=Table.UnpivotOtherColumns(更改的类型,{"序号","工单编号","订单数","交货日期"},"属性","值")
//2、分组
=Table.Group(透视表,{"交货日期","值"},{{"订单总数", each List.Sum([订单数]), type nullable number}})

微信图片_20230305103306.png

7、Power Query : 多层结构二维表转换一维表

微信图片_20230305110411.png

通过 UI 功能区将二维表转化为一维表

1、Power Query 的【转换】功能区,选择【转置】
2、填充空值,【转换】功能去中的【填充】【向下】
3、【将第一行用作标题】
4、点击【逆透视其他列】

通过 M 语言将二维表转化为一维表

//1、转置
=Table.Transpose(更改的类型)

//2、向下填充
= Table.FillDown(转置表,{"Column1"})

//3、第一行做标题
=Table.PromoteHeaders(向下填充)

//4、重命名列
=Table.RenameColumns(表,{{"年度","地区"},{"Column2","城市"}})

//5、逆透视
=Table.UnpivotOtherColums(表,{"地区","城市"},"属性","值")

微信图片_20230305111506.png

8、Power Query : 通过合并查询实现客户变化情况对比

微信图片_20230305214426.png
需求是对比哪些客户是 2年都有接触的长期客户,哪些是新增客户,哪些是流失客户

通过 UI 功能区实现客户变化情况对比

1、任意选中一列,然后右键【作为新查询添加】
2、将列表转化为表格
3、筛选的方法将空值的记录删除掉
4、2022 年的数据我们重复执行一遍一样的操作
5、选中 2021年客户的表,然后在功能区选择【合并查询】【将查询合并为新查询】
联接种类选择:

  • 内部:同时在两张表中都有的数据才会保留
  • 左反:只保留第一个表中存在的行
  • 右反:只在第二章表中存在的记录

M 公式解析

//1、数据转化为表
=Table.FromList(#"2021年客户1",Splitter.SplitByNothing(),null,null,ExtraValues.Error)
//2、合并查询
=Table.NestedJoin(#"2021年客户",{"Column1"},#"2022年客户",{"Column1"},"2022年客户",JoinKind.Inner)
//Table.ExpandTableColumn将 Table 类型的列进行展开

9、Power Query : 通过条件判断根据销售额划分等级

微信图片_20230305230826.png
需求是根据不同的销售额数量来划分等级

通过 UI 功能区实现条件判断划分等级

点击【添加列】功能区的【条件列】

通过 M 公式实现条件判断划分等级

=Table.AddColumn(表,"等级",each if [销售量]>=2000 then "优秀" else if...)

10、Power Query : 通过单元格拆分,统计审批间隔

微信图片_20230305232956.png

通过 UI 功能区操作实现单元格拆分,统计审批间隔

1、主页中的【拆分列】【按分隔符】拆分
2、点击【转换】功能区下面的【格式】【修整】(把文本前后多余的空格给清除掉)
3、进行二次拆分,让每个审批记录按照 | 再次拆分
4、选择添加列,【自定义列】
5、用[第一次审批时间] - [审批开始时间],得到第一次审批的间隔时间
6、使用【转换】当中的【持续时间】功能(将时间根据我们自己的需要转化为天数,小时数或者分钟数)
7、使用【舍入】的功能,来将多余的小数进行取舍
8、利用【添加后缀】功能,给每个时间值后添加上 h 小时的单位

M 函数

=Table.SplitColumn(表,"审批记录",Splitter.SplitTextByDelimiter(";",QuoteSylte.csv),{"审批记录.1","审批记录.2"})

11、Power Query : 通过参数解决文件路径修改问题

文件路径改变时候会导致数据无法刷新

通过参数动态设置文件路径:
1、Power Query 编辑器中点击【管理参数】【新建参数】
2、此时在 Power Query 中就可以直接通过参数名称来引用对应的文件路径
3、将参数中【建议的值】设置为【值列表】,可以让用户选择当前值时,以列表方式让用户选择

如果 Power Query 中拥有很多查询或者参数。这个时候最好添加不同的文件夹来将查询进行分类管理

12、Power BI Desktop 的内部知识

Power Query复制:查询,转换
DAX:建模

Power BI 采取列存储数据库,内存数据库,使用词典编码和行程编码压缩数据

Power BI Desktop 文件的构建步骤:创建查询——压缩数据,自动建立关系——添加计算列,度量值,连接缺少的关系——计算度量值创建可视效果

12.png

13、DAX : 计算列和度量值

DAX 是 Data Analysis Experessions 的缩写,意思是数据分析表达式。

计算列

计算列:当前分析的数据没有包含我们需要的特定字段,这是需要用到计算列在表中添加新列,计算列是使用DAX格式来定义列的值

Price Band = If(ProductDim[Unit Price]<=25,"Low", if(ProductDim[Unit Price]<=50,"Medium","High"))

DAX中的计算列与Power Query中的自定义区别:
1、M语言创建的自定义列,这属于属于查询转换的一部分,而在报表或数据视图的计算列中使用的是 DAX 语言,这是已经加载到模型中的数据为基础的
2、自定义列是数据压缩后再写到内存里的

计算列是以当前表中的列为单位的计算公式,因此计算列中不能直接引用其他表中的列。

这时,需要用到相关函数来解决跨表引用。

Sales[COGS]=RELATED(ProductDim[Unit Cost]*Sales[Units])

RELATED函数的作用是返回与当前行相关的一个属性,也就是通过现有的多对一关系从相关表中的指定列中提取值。
(ProductDim 与 Sales 是 1对多关系。当前的添加计算列的操作是在sales 表中进行的,也就是1对多中多的那边。在这里 RELATED 会从 “多”的一端去查找对应 “一”的一端的值。也就是会找到 Sales 中每条交易ProductID 对应的 ProductDim 维度表中的 Unit Cost 的值。)

计算列只有在表刷新时才会进行计算,他占用的是数据模型的计算时间,并且添加的计算列只能依附于当前的表。所以 DAX 的最佳用法是尽可能少用,如果能用 Power Query 添加的列都用Power Query 添加。

度量值

逐行计算使用计算列
将一列进行聚合计算使用度量值

默认汇总的方式也称为内隐式度量值,与之相对的也就是明确式度量值。

[Total Sales] = SUM(Sales[Sales Amount])

这个销售总额实际上就是度量值。


在多个地方使用同一个度量值:

[Profit]=SUM(Sales [Sales Amount]-SUM(Sales[COGS]))//不能重复使用度量值

[Profit]=[Total Sales]-[Total COGS] //重复使用

度量值是可以重复使用的

计算列vs度量值

计算列往表格新增加内容,会影响模型运算速度。
度量值是以公式的形式被存储下来的,几乎不会占用电脑的内存,度量值很灵活,而且在运算速度上有很大的优势。

14、DAX : 初识 Calculate 函数和计算上下文

Calculate函数

CALCULATE(Expression, [Filter1],[Filter2]...)

[Desktop Sales]=CALCULATE([Total Sales], CampaignDim[Device]="Desktop")

在这里创建一个叫做 Desktop Sales 的度量值。
第一个参数是另一个度量值 Total Sales ,第二个参数是一个筛选条件

Total Sales 本身就是一个 DAX 函数运算。实际上 Total Sales 就等于 SUM(Sales[Sales Amount])

计算上下文

分为行上下文和筛选器上下文

DAX 计算的本质,可以理解为在建立了关系的多个表构成的数据模型上,DAX 通过筛选,找到需要进行计算的一个数据模型的子集,然后完成聚合型计算。

DAX本身是基于列计算,但也要兼顾按行运算,所以提供了两个特性:筛选、迭代

  • 筛选:按照一定的筛选规则选择出模型的一个子集
  • 迭代:进一步针对这个子集的每一行进行轮询处理

DAX 计算在本质上就是筛选出数据模型的子集,然后对行进行迭代处理后再进行聚合运算。

筛选的关键机制是【筛选上下文】,对行进行迭代的关键机制是【行上下文】

  • 行三下文:DAX 计算将遍历列的每行,并通过行上下文,提取出当前的行值然后进行计算
  • 筛选上下文:可以简单的将筛选上下文定义为限制 DAX 表达式计算(通常是度量值)的一组筛选器

15、DAX : 常用函数和数据类型

  • 聚合函数:SUM, MAX, MIN ,AVERAGE 等
  • 日期和时间函数:YEAR, MONTH ,DAY 等
  • 逻辑函数: IF , AND, OR, SWITCH
  • 信息函数:用来判断某个值的具体类型,比如 ISBLANK , ISNUMBER , ISTEXT 等
  • 文本函数:用于文本的运算,比如 FIND , SEARCH, REPLACE
  • 筛选器函数:CALCULATE
  • 关系函数:RELATED
  • 表操作函数:用于返回表或者操作表的函数,常见的有 FILTER , SELECTCOLUMNS等
  • 日期智能函数:生成和比较时间段的计算,比如 DATEADD , DATESMTED 等
  • 其他函数

DAX最小单位是列
引用列:表名[列名]

16、DAX : 通过基础函数 SUMX 计算产品销售折扣总金额

微信图片_20230307001933.png
需求是计算得到产品销售总金额,平均折扣金额以及产品销售折扣总金额

通过 SUM 求和计算产品销售总金额

加载数据——【新建度量值】——编写SUM公式——把度量值【产品销售总金额】用表放到画布

产品销售总金额=SUM(表1[产品销售金额])

平均折扣比例=AVERAGE(表1[折扣比例])

通过 Related 新建计算列,显示省份信息

微信图片_20230309010653.png
门店表,省份表,原始的订单明细表。

3 张表格都导入到 Power BI Desktop——点击左侧的关系视图,刷新下

微信图片_20230309011220.png

17、DAX : 通过 FILTER 函数计算某类产品的销售总额

18、DAX : 通过 ALL 函数计算占比

19、DAX : 计算上下文

20、DAX : 深入理解 Calculate 函数

21、DAX : 通过变量避免重复书写表达式

22、DAX : 动态计算占比

23、DAX : 动态计算排名

24、DAX : 时间智能函数 - 计算时间累计

最后编辑:2023年03月08日 ©著作权归作者所有