近段时间比较忙,也没有看到很好的DAX素材,很久没有更新文章了,刚好有时间就来凑个热闹。
今天主题是[power bi]建模分析的一些小技巧。
1、[power query]之数据表字段名称管理
2、Power BI建模必备要素
前提:标准数据库里面的数据用sql来管理,在这里不做讨论。
在诸多CSV格式的数据源中,经常会有很多字段的数据,如图:
多字段数据
在这么多的字段中,我们经常会只需要一部分,如上图中绿色框线中的数据。
默认操作
如果表特别多的时候,重复操作就会把脑壳整炸,
又如果有的字段需要增加或者删除,那又是一通操作,非常不利于管理。
我们经过思考,从 数据获取 => 选择字段 => 重命名 => 更改类型 都封装在一个pq函数中,通过外部的Excel表格来管理数据源。这样就方便多了(数据的筛选等其他清洗动作同理)。
封装的函数操作,方便快捷。
如下在EXCEL中管理表格(演示)
自定义函数Fxtable。
/* 必备条件 1、csv格式数据源,(同理可以封装excel格式数据); 2、《01名称管理》表格的设计如示例; 3、建立自己的文件管理路径 4、参数说明 folders:对应的Path下的子文件夹管理路径, tablename:csv文件的名称, removerows:csv文件顶端移除行数,必有标题的,根据需要移除。 */ (folders as text,tablename as text,removerows as number ) as table => let 名称管理表=Excel.Workbook(File.Contents(Path&"\01辅助表\01名称管理.xlsx"), null, true){[Item="名称管理",Kind="Table"]}[Data], 类型源 = {type text,type number,Int32.Type,type date}, 列 = Table.RowCount(Table.SelectRows(名称管理表, each ([表名] = tablename))), 名称表 = Table.SelectRows(名称管理表, each ([表名] = tablename) and ([统一名称] <> null)), 需求列 = List.Transform(名称表[ID],each "Column"&Text.From(_)), 新名称 = List.Zip({需求列,名称表[统一名称]}), 新类型 = List.Zip({名称表[统一名称],List.Transform(名称表[类型],each 类型源{_})}), 数据源=Csv.Document(File.Contents(Path&"\"&folders&"\"&tablename&".csv"),[Delimiter=",", Columns=列, Encoding=936, QuoteStyle=QuoteStyle.None]), 结果 = Table.TransformColumnTypes(Table.RenameColumns(Table.Skip(Table.SelectColumns(数据源,需求列),removerows),新名称),新类型) in 结果
建立Power BI模型时,除业务本身数据外必备的几个要素。
必备要素
(1)Path
Path参数,作为整个模型文件管理的根目录设置,对模型移动管理很重要。
(2)Measure
Mersure表,作为度量管理的基础要素,笔者更习惯pq来写这个表,更简洁舒服。
注意:需要先用temp把Measure表加载到模型,写入度量后,把temp去掉。
当然也可以用dax来写的 Mersur= {BLANK()} 。
Mersure表
(3)Calendar
Calendar日期表,不用多说建模必备的。
let date_start=#date(2018, 1, 1),//开始日期 date_end=#date(2020, 12, 31),//结束日期 count=Duration.Days(date_end-date_start),//间隔天数 calendar = #table ( type table [ dates=date ,year=Int64.Type ,month=Int64.Type ,day=Int64.Type ,year_m=Int64.Type ,quarter=Int64.Type ,year_q=Int64.Type ,weeknumber=Int64.Type ,weekday=Int64.Type ,weekus=text ,weekcn=text ,weekcna=text ,M=text ,YM=text ,Q=text ,YQ=text ] ,List.Transform({0..count},(n)=> let d=Date.AddDays(date_start,n) in { d//日期 ,Date.Year(d)//年 ,Date.Month(d)//月 ,Date.Day(d)//天 ,Date.Year(d)*100+Date.Month(d)//年月组合 ,Date.QuarterOfYear(d)//季度 ,Date.Year(d)*100+Date.QuarterOfYear(d)//年季度组合 ,Date.WeekOfYear(d,1)//周一开始的一年第几周 ,Date.DayOfWeek(d,1)//星期几 ,Date.DayOfWeekName(d, "en-US")//星期几英文 ,Date.DayOfWeekName(d, "zh-CN")//星期几中文 ,Text.End(Date.DayOfWeekName(d, "zh-CN"),1)//星期几简写 ,"M"&Date.ToText(d,"MM") ,Date.ToText(d,"Yyy")&"M"&Date.ToText(d,"MM") ,"Q"&Number.ToText(Date.QuarterOfYear(d)) ,Date.ToText(d,"Yyy")&"Q"&Number.ToText(Date.QuarterOfYear(d)) } ) ) in calendar
Calendar
(4)Unit
Unit表,根据业务需求,提前预设单位,为动态使用单位准备,符合中国式报表的单位设置。
Unit
动态管理单位
(5)RefreshTime
RefreshTime刷新时间,动态记录Power BI项目中数据刷新的时间节点。不会有人再问你数据是什么时候的了。
小技巧:↻ 刷新符号
本地刷新可以去掉"**+#duration(0,8,0,0)"**这一串,如果微软云端刷新,就要根据业务本身所在时区来调整。
刷新时间