知识来源：
知网：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 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 的工作顺序（数据分析前提）：查询获取收据——清洗转换合并数据——加载将经过处理的数据加载到模型中以供后续使用

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表类型

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

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

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

Record 记录类型

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

=[姓名="李四",年龄=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 : 将多张工作表进行汇总

这种分表通常不利于统计分析，通常将若干表格合并成一张表，然后再进行统计分析。

通过UI界面

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

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

新建空白查询，输入

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

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

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

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 : 二维表分类求和

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

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

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

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

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

通过 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(表,{"地区","城市"},"属性","值")

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

需求是对比哪些客户是 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 : 通过条件判断根据销售额划分等级

需求是根据不同的销售额数量来划分等级

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

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

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

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

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

通过 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 文件的构建步骤：创建查询——压缩数据，自动建立关系——添加计算列，度量值，连接缺少的关系——计算度量值创建可视效果

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 计算产品销售折扣总金额

需求是计算得到产品销售总金额，平均折扣金额以及产品销售折扣总金额

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

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

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

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

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

门店表，省份表，原始的订单明细表。

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

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

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

19、DAX : 计算上下文

20、DAX : 深入理解 Calculate 函数

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

22、DAX : 动态计算占比

23、DAX : 动态计算排名

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