1.1 PowerBI数据准备-复制粘贴PowerQuery代码，生成多功能日期表

已于 2025-02-10 08:02:49 修改
阅读量548 收藏 4
点赞数 5
分类专栏： PowerBI入门100例 文章标签： 数据可视化 powerbi 数据分析
于 2025-02-03 21:31:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/pbi_zijixue/article/details/145433585
版权

加入 PowerBI自己学 知识星球：下载源文件，边学边练；遇到问题，还可以提问交流。

几乎每个模型都会用到日期表。模型中用不用日期表、如何生成日期表、日期表的列是否具备多功能，都在一定程度上体现了PowerBI报告开发者的专业水平。

首先，用不用日期表。PowerBI具备自动日期/时间功能，能够在后台自动生成一个隐藏的日期表和相应的日期层次结构，但此功能只能满足基本的日期相关分析。

建议在选项菜单中关闭自动日期/时间功能，如果有涉及日期相关计算，用自己生成的多功能日期表。

其次，如何生成日期表。多种方法均可生成日期表，通过源文件导入、PowerQuery、DAX等方法都可以生成日期表。考虑到源文件相对缺少灵活性，DAX不能满足数据加载前调用日期表且DAX计算表占用运行内存可能会影响报表性能，更重要的是生成日期表的过程本身就属于数据获取和清洗的范畴，所以建议在PowerQuery中生成日期表。只需要通过复制粘贴现成的PowerQuery代码，就可以快速生成一个多功能日期表。

最后，日期表中的列是不是多功能，直接影响后期分析需求的实现。本文中的日期表代码支持按年份区间等参数创建日期表，代码生成的日期表包含财年、中英文月名称/星期名称、按刷新时的北京时间生成的年/财年/年季/年月/年周/周/日期的定位序号、当前时点标记（当前年、当前财年、当前季、当前月、当前年周、当前天）等日期表常用列，能够满足大部分日期相关分析需求。

操作步骤   

STEP 1 点击菜单栏主页下的获取数据，选择空白查询。

STEP 2 进入PowerQuery后，将复制好的日期表代码（见文末）粘贴到fx公式栏中，然后点击fx前面的对号按钮。

STEP 3 按需输入参数，包括：开始年份、结束年份，建议使用较大的范围满足当前和未来数据的需要，毕竟日期表行数有限，占用的空间很小；财年开始月份，比如2023年10月是新财年的开始，录入10以后，财年列会在2023年10月开始变成FY23/24；每周第一天是星期几，国内通常选择周一，不选择默认就是周一。

STEP 4 点击调用按钮，并为生成的日期表命名，此处命名为Dim_Date。如果后期需要对日期表进行调整，可以选中日期表在fx公式栏中修改参数。

拓展

如果有更多的个性化需求，可以继续修改代码或者在生成的日期表中添加列，比如可以尝试将财年格式FY23/24改为FY2024。日期表代码使用的是M语言，相对简单易读。通过对日期表代码的调校，可以学习很多M语言的基础知识，比如日期函数、格式转换、查询步骤运行机制等。

日期表代码

= let

    CalendarType =  type function (

        optional CalendarYearStart as (type number meta [

            Documentation.FieldCaption = "请输入日期表的开始年份",

            Documentation.SampleValues = { Date.Year( Date.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8)) ) - 1 }

        ]),

        optional CalendarYearEnd as (type number meta [

            Documentation.FieldCaption = "请输入日期表的结束年份",

            Documentation.SampleValues = { Date.Year( Date.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8)) ) }

        ]),

        optional CalendarFiscalYearStart as (type number  meta [

            Documentation.FieldCaption = "请输入财年开始月份，1-12之间的数字，默认为1。",

            Documentation.SampleValues = { 1 }

        ]),

        optional CalendarFirstDayOfWeek as (type text meta [

            Documentation.FieldCaption = "请输入每周第一天，Monday/Tuesday/Wednesday/Thursday/Friday/Saturday/Sunday，默认为Monday。",

            Documentation.SampleValues = { "Monday" }

        ])

    )

    as table meta [

        Documentation.Name = "使用PowerQuery创建日期表",

        Documentation.LongDescription = "按年份区间等参数创建日期表，包含财年、中英文月名称/星期名称、按北京时间刷新生成的年/财年/年季/年月/年周/周/日期的定位序号、当前年、当前财年、当前季、当前月、当前年周、当前天等日期表常用列，可按需要更改此查询中的代码。"

    ],

    CreateCalendar = ( optional CalendarYearStart as number, optional CalendarYearEnd as number, optional CalendarFiscalYearStart as number, optional CalendarFirstDayOfWeek as text) => let

        begin_year = CalendarYearStart,

        end_year = CalendarYearEnd,

        begin_fiscalyear = if CalendarFiscalYearStart<>null then CalendarFiscalYearStart else 1,

        first_day_of_week = if Text.Lower( CalendarFirstDayOfWeek ) = "monday" then Day.Monday

                            else if Text.Lower( CalendarFirstDayOfWeek ) = "tuesday" then Day.Tuesday

                            else if Text.Lower( CalendarFirstDayOfWeek ) = "wednesday" then Day.Wednesday

                            else if Text.Lower( CalendarFirstDayOfWeek ) = "thursday" then Day.Thursday

                            else if Text.Lower( CalendarFirstDayOfWeek ) = "friday" then Day.Friday

                            else if Text.Lower( CalendarFirstDayOfWeek ) = "saturday" then Day.Saturday

                            else if Text.Lower( CalendarFirstDayOfWeek ) = "sunday" then Day.Sunday

                            else if CalendarFirstDayOfWeek <> null then error "参数错误：每周第一天必须是Monday，Tuesday，Wednesday，Thursday，Friday，Saturday，Sunday中的一个。"

                            else Day.Monday,

        today = Date.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8)),

        y1 = if begin_year <> null then begin_year else if end_year <> null then end_year else Date.Year( today ) ,

        y2 = if end_year <> null then end_year else if begin_year <> null then begin_year else Date.Year( today ) ,

        calendar_list = { Number.From ( #date( Number.From( y1 ) , 1 , 1 ) ) .. Number.From( #date( Number.From( y2 ) , 12, 31 ) ) },

        calendar_list_table = Table.FromList(calendar_list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        #"Changed Type" = Table.TransformColumnTypes(calendar_list_table,{ {"Column1", type date}}),

        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{ {"Column1", "Date"}}),

        #"Added Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),

        #"Added Quarter" = Table.AddColumn(#"Added Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),

        #"Added Month" = Table.AddColumn(#"Added Quarter", "Month", each Date.Month([Date]), Int64.Type),

        #"Added FiscalYear" = Table.AddColumn(#"Added Month", "FiscalYear", each if [Month] < begin_fiscalyear then "FY" & Text.End(Text.From([Year]-1),2) & "/" & Text.End(Text.From([Year]),2) else "FY" & Text.End(Text.From([Year]),2) & "/" & Text.End(Text.From([Year]+1),2), type text),

        #"Added Week of Year" = Table.AddColumn(#"Added FiscalYear", "WeekOfYear", each Date.WeekOfYear( [Date] , first_day_of_week ), Int64.Type),

        #"Added Week of Month" = Table.AddColumn(#"Added Week of Year", "WeekOfMonth", each Date.WeekOfMonth( [Date] ), Int64.Type),

        #"Added Start of Week" = Table.AddColumn(#"Added Week of Month", "DateOfWeekStart", each Date.StartOfWeek( [Date], first_day_of_week ), type date),

        #"Added End of Week" = Table.AddColumn(#"Added Start of Week", "DateOfWeekEnd", each Date.EndOfWeek([Date], first_day_of_week), type date),

        #"Added Day" = Table.AddColumn(#"Added End of Week", "DayOfMonth", each Date.Day([Date]), Int64.Type),

        #"Added Day of Week" = Table.AddColumn(#"Added Day", "DayOfWeek", each Date.DayOfWeek( [Date] , first_day_of_week )+1, Int64.Type),

        #"Added Day of Year" = Table.AddColumn(#"Added Day of Week", "DayOfYear", each Date.DayOfYear([Date]), Int64.Type),

        #"Added Year Quarter" = Table.AddColumn(#"Added Day of Year", "YearQuarter", each [Year] * 100 + [Quarter] , Int64.Type),

        #"Added Year Month" = Table.AddColumn(#"Added Year Quarter", "YearMonth", each [Year] * 100 + [Month] , Int64.Type),

        #"Added Year Week" = Table.AddColumn(#"Added Year Month", "YearWeek", each [Year] * 100 + [WeekOfYear] , Int64.Type),

        #"Added Date Code" = Table.AddColumn(#"Added Year Week", "DateCode", each [Year] * 10000 + [Month] * 100 + [DayOfMonth] , Int64.Type),       

        #"Added DayEN Name" = Table.AddColumn(#"Added Date Code", "DayOfWeekName_EN", each Text.Start(Date.DayOfWeekName( [Date] , "en" ),3), type text),

        #"Added DayCN Name" = Table.AddColumn(#"Added DayEN Name", "DayOfWeekName_CN", each Text.Start(Date.DayOfWeekName( [Date] , "zh" ),3), type text),

        #"Added Year Name" = Table.AddColumn(#"Added DayCN Name", "YearName", each "Y" & Text.From( [Year] )  , type text),

        #"Added Quarter Name" = Table.AddColumn(#"Added Year Name", "QuarterName", each "Q" & Text.From( [Quarter] ) , type text),

        #"Added YearQuarter Name" = Table.AddColumn(#"Added Quarter Name", "YearQuarterName", each Text.End(Text.From( [Year] ),2) & [QuarterName] , type text),

        #"Added MonthEN Name" = Table.AddColumn(#"Added YearQuarter Name", "MonthName_EN", each Text.Start(Date.MonthName( [Date] , "en" ),3), type text),

        #"Added MonthCN Name" = Table.AddColumn(#"Added MonthEN Name", "MonthName_CN", each Text.Start(Date.MonthName( [Date] , "zh" ),3), type text),

        #"Added YearMonth Name" = Table.AddColumn(#"Added MonthCN Name", "YearMonthName", each Text.End(Text.From( [Year] ),2) & [MonthName_EN] , type text),

        #"Added Week Name" = Table.AddColumn(#"Added YearMonth Name", "WeekName", each "W" & Text.From( [WeekOfYear] ) , type text),

        #"Added YearWeek Name" = Table.AddColumn(#"Added Week Name", "YearWeekName", each Text.End(Text.From( [Year] ),2) & [WeekName] , type text),

        #"Added Year Offset" = Table.AddColumn(#"Added YearWeek Name", "Year_Offset", each [Year] - Date.Year(today) , Int64.Type),

        #"Added FisacalYear Offset" = Table.AddColumn(#"Added Year Offset", "FiscalYear_Offset", each if [Month]<begin_fiscalyear then [Year_Offset]-1 else [Year_Offset], Int64.Type),

        #"Added YearQuarter Order" = Table.AddColumn(#"Added FisacalYear Offset", "YearQuarter_Order", each [Quarter]+4*([Year]-List.Min(#"Added Year" [Year])) , Int64.Type),

        #"Added YearQuarter Offset" = Table.AddColumn(#"Added YearQuarter Order", "YearQuarter_Offset", each [YearQuarter_Order] - (Date.QuarterOfYear(today) + 4*(Date.Year(today)-List.Min(#"Added Year" [Year]))), Int64.Type),            

        #"Added YearMonth Order" = Table.AddColumn(#"Added YearQuarter Offset", "YearMonth_Order", each [Month]+12*([Year]-List.Min(#"Added Year" [Year])) , Int64.Type),

        #"Added YearMonth Offset" = Table.AddColumn(#"Added YearMonth Order", "YearMonth_Offset", each [YearMonth_Order] - (Date.Month(today)+12*(Date.Year(today)-List.Min(#"Added Year" [Year]))) , Int64.Type),

        #"Added YearWeek Order" = Table.AddColumn(#"Added YearMonth Offset", "YearWeek_Order", each [WeekOfYear]+53*([Year]-List.Min(#"Added Year" [Year])) , Int64.Type),

        #"Added YearWeek Offset" = Table.AddColumn(#"Added YearWeek Order", "YearWeek_Offset", each [YearWeek_Order]-53*(Date.Year(today)-List.Min(#"Added Year" [Year]))-Date.WeekOfYear( today , first_day_of_week ), Int64.Type),

        #"Added Week Order" = Table.AddColumn(#"Added YearWeek Offset", "Week_Order", each Duration.TotalDays([DateOfWeekStart]-List.Min(#"Added Start of Week" [DateOfWeekStart]))/7+1, Int64.Type),

        #"Added Week Offset" = Table.AddColumn(#"Added Week Order", "Week_Offset", each [Week_Order]-(Duration.TotalDays(Date.StartOfWeek( today, first_day_of_week )-List.Min(#"Added Start of Week" [DateOfWeekStart]))/7+1), Int64.Type),

       #"Removed Order Columns" = Table.RemoveColumns(#"Added Week Offset",{"YearMonth_Order","YearQuarter_Order","YearWeek_Order","Week_Order"}),

       #"Added Date Offset" = Table.AddColumn(#"Removed Order Columns", "Date_Offset", each Number.From([Date] - today), Int64.Type),

       #"Added Year_CurrentYearMark" = Table.AddColumn(#"Added Date Offset", "Year_CYMark", each if [Year_Offset]=0 then "当前年" else Text.From([Year]), type text),

       #"Added Year_CurrentFicalYearMark" = Table.AddColumn(#"Added Year_CurrentYearMark", "FiscalYear_CYMark", each if [FiscalYear_Offset]=0 then "当前财年" else [FiscalYear], type text),

       #"Added YearQuarter_CurrentQuarterMark" = Table.AddColumn(#"Added Year_CurrentFicalYearMark", "YearQuarter_CQMark", each if [YearQuarter_Offset]=0 then "当前季" else Text.From([YearQuarter]), type text),

       #"Added YearMonth_CurrentMonthMark" = Table.AddColumn(#"Added YearQuarter_CurrentQuarterMark", "YearMonth_CMMark", each if [YearMonth_Offset]=0 then "当前月" else Text.From([YearMonth]), type text),

       #"Added YearWeek_CurrentYearWeekMark" = Table.AddColumn(#"Added YearMonth_CurrentMonthMark", "YearWeek_CWMark", each if [YearWeek_Offset]=0 then "当前年周" else Text.From([YearWeek]), type text),

       #"Added Date_CurrentDateMark" = Table.AddColumn(#"Added YearWeek_CurrentYearWeekMark", "Date_CDMark", each if [Date_Offset]=0 then "当前天" else Text.From([Date]), type text)

    in

        #"Added Date_CurrentDateMark"

    in

    Value.ReplaceType( CreateCalendar , CalendarType )

Power BI创建日期表--超详细--小白向
qq_56545728的博客
11-21 6102
Power BI 创建日期表 小白向
Power BI日期表创建方式汇总
HIMOJITO的博客
03-04 8523
在日常使用PBI做数据分析时，最长用到的就是日期维表了额，这里把日期表的生成方法做一下汇总，分别使用Excel公式、Power Query的M函数以及DAX生成日期表 下图为我们要生成的日期表样式： Excel公式法 在A1：H1中输入日期表的表头，然后在A2：H2中分别输入以下公式： A2=2020-01-01 B2=YEAR(A2) C2="Q"&LEN(2^MONTH(A2)) D2=RIGHT("0"&MONTH(A2),2) E2=DAY(A2) F2=B2&C2.
快速掌握Power BI日期表技巧
Sharewinfo_BJ的博客
03-10 1071
数据分析领跑者
powerBI生成日期表
qq_40602449的博客
04-12 2185
日期表 = ADDCOLUMNS ( CALENDAR ( date(2018,1,1),date(2021,12,31) ), "年", YEAR ( [Date] ), "季度", ROUNDUP( MONTH ( [Date] )/3,0 ), "月", MONTH ( [Date] ), "周", WEEKNUM([Date]), "年季度", YEAR ( [Date] ) & "Q" & ROUNDUP( MONTH ( [Date] )/3,0 ) , "年月", YEAR .
在Power Query中生成一个不断更新的日期表
Good_Hope的博客
03-28 3524
在Power Query中生成一个不断更新的日期表
PowerBI创建基础的日期表
啥技术没有，又爱折腾的强迫症晚期患者！
11-19 4071
PowerBi快速创建日期表的方法。
Power Query 再谈日期表
焦棚子的博客
02-22 832
后台回复日期表三个字即可领取。
使用Power Query进行数据清洗与整合
# 1. 介绍Power Query ## 1.1 什么是Power Query Power Query是一种用于数据清洗和...- 数据提取：Power Query可以方便地从多个数据源中提取数据，包括数据库、文件、Web等，减少了手动复制粘贴的麻烦。 - 数据转换
【Excel与PowerBI协同】：打造数据处理的双重奏
![【Excel与PowerBI协同】：打造数据处理的双重奏]... # 摘要 在数据处理领域，选择合适的工具对于提高效率和准确性至关重要。本文概述了数据处理的基础知识和常用工具，并详细介绍了Excel和Power BI在数据输入、整理...
VLOOKUP与Power Query：Excel大数据处理新境界
!... # 摘要 随着大数据时代的到来，...从VLOOKUP函数的基础使用和进阶技巧，到Power Query的入门、高级功能和实际应用案例分析，本文详细阐述了这些工具在处理大数据时的强大功能和潜在局限性。此外，本文还探讨了VLOO
A002-185-2502-李林
m0_50973829的博客
01-03 3120
作业A001-185-2502-李林 课程名称 软件需求分析与建模 班级 18软件工程5班 教导教师 董瑞生 李林 1814080902502 日期 2020.12.15 目录 作业内容目录一、个人查找档总结Requirements baseline（需求基线） 一、个人查找档总结 Requirements baseline（需求基线） ...
DaxDateTemplate:Power BI模板在DAX中创建日期表
04-29
DaxDateTemplate Power BI模板在DAX中创建日期表 阅读位于文档
【数据分析的艺术】：从Excel到PPT课件的转换技巧及注意事项
数据分析是理解和挖掘数据价值的关键过程，本文从基础知识出发，详细介绍了Excel和PPT在数据分析和可视化中的应用技巧。通过深入探讨Excel的基础操作、数据可视化技术以及数据处理与分析工具，文章展示了如何将数据...
PowerBI-l5-CALENDAR创建日期表
禅：戒定慧
09-20 585
CALENDAR创建日期表。
PowerQuery 建立跨年月日期维度表（增加折叠：满一周折叠一周）
sq75987126的博客
05-05 656
最近正在学习PowerQuery，为了避免忘记就在此记录一下怎么创建以周期为主跨年月的日期维度表吧。新增满一周折叠一周，折叠一月满一月，否则显示天数。
DAX和Power BI中的参考日期表
weixin_30266885的博客
01-02 438
本文使用Power BI模板描述DAX中的引用Date表，可以在Analysis Services模型中使用相同的技术。在Dax Date Template页面下载最新版本的模板。 为什么引用Date表 Power BI中提供的自动日期/时间功能存在以下几个限制： 它有一组固定的行。 它没有处理财政年度。 它不包括周。 ...
PowerBI 制作日历表、计算每月实际天数
Bianca427的博客
05-11 3807
PowerBI 制作日历表、计算每月实际天数
PowerBi中CALENDAR怎么使用？
最新发布
kwydxm的博客
04-12 514
CALENDAR()用来创建一个从开始日期到结束日期的连续日期表，常用于建立时间维度！
1.21 PowerBI数据准备-日期表按事实表最新日期增加当前年/月等相关列
PowerBI自己学
02-04 525
日期表按照当前日期动态标记了当前年、当前财年、当前季、当前月、当前周、当前天，但是实际工作中，有可能交易数据并不是随着时间及时更新到当前的年/月/日，而是有一定的滞后性，比如从外部购买的市场数据、人工按月提报的数据等都会比当前月迟到1个月或几个月。那么如何让报告页面展示到交易数据的最新月份呢？本质上还是在日期表中新增定位列，只是这次和日期表的年/月/日对比的不是当前的年/月/日了，而是交易数据中的最大日期，把对应最大交易日期的年/月标记为当前年和当前月，并定位为0以及基于此生成年和年月的定位序号。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值