- Power Platform概况
- Power BI
- 1、数据建模
- 2、Power Query
- 3、Power Query M 语言
- 4、Power Query 中的三大数据结构
- 5、Power Query : 将多张工作表进行汇总
- 6、Power Query : 二维表分类求和
- 7、Power Query : 多层结构二维表转换一维表
- 8、Power Query : 通过合并查询实现客户变化情况对比
- 9、Power Query : 通过条件判断根据销售额划分等级
- 10、Power Query : 通过单元格拆分,统计审批间隔
- 11、Power Query : 通过参数解决文件路径修改问题
- 12、Power BI Desktop 的内部知识
- 13、DAX : 计算列和度量值
- 14、DAX : 初识 Calculate 函数和计算上下文
- 15、DAX : 常用函数和数据类型
- 16、DAX : 通过基础函数 SUMX 计算产品销售折扣总金额
- 17、DAX : 通过 FILTER 函数计算某类产品的销售总额
- 18、DAX : 通过 ALL 函数计算占比
- 19、DAX : 计算上下文
- 20、DAX : 深入理解 Calculate 函数
- 21、DAX : 通过变量避免重复书写表达式
- 22、DAX : 动态计算占比
- 23、DAX : 动态计算排名
- 24、DAX : 时间智能函数 - 计算时间累计
知识来源:
知网: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——点击左侧的关系视图,刷新下