let // تاریخ شروع به میلادی StartDate = #date( 2024, 8, 8) , // تاریخ پایان تقویم به میلادی // #date( Date.Year(DateTime.LocalNow()) + 1, 1, 1) EndDate = #date( Date.Year(DateTime.LocalNow()) + 1, 1, 1) , // تولید لیست تاریخ‌ها // منبع: // gorilla.bi/power-query/date-table/ DateList = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) + 1, #duration( 1, 0, 0, 0 ) ), #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), type table[ Date = Date.Type ]), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Id", 1, 1, Int64.Type), #"Added Custom (Persian Date Serial)" = Table.AddColumn(#"Added Index", "P.Date Serial", each Number.FromText( Date.ToText( [Date] , [Format="yyyMMdd", Culture="fa-IR"]) ), Int64.Type), #"Added Custom (Persian Date)" = Table.AddColumn(#"Added Custom (Persian Date Serial)", "P.Date", each Date.ToText( [Date] , [Format="yyy/MM/dd", Culture="fa-IR"]), Text.Type), #"Added Custom (Persian Short Date)" = Table.AddColumn(#"Added Custom (Persian Date)", "P.Short Date", each Date.ToText( [Date] , [Format="yyy/M/d", Culture="fa-IR"]), Text.Type), #"Added Custom (Persian YearMonth)" = Table.AddColumn(#"Added Custom (Persian Short Date)", "P.YearMonth", each Number.FromText( Date.ToText( [Date] , [Format="yyyMM", Culture="fa-IR"]) ), Int64.Type), #"Added Custom (Persian Year)" = Table.AddColumn(#"Added Custom (Persian YearMonth)", "P.Year", each Number.FromText( Date.ToText( [Date] , [Format="yyy", Culture="fa-IR"]) ), Int64.Type), #"Added Custom (Persian Month)" = Table.AddColumn(#"Added Custom (Persian Year)", "P.Month", each Number.FromText( Date.ToText( [Date] , [Format="MM", Culture="fa-IR"]) ), Int64.Type), #"Added Custom (Persian Day of Week)" = Table.AddColumn(#"Added Custom (Persian Month)", "P.Day of Week", each Date.DayOfWeek( [Date], Day.Saturday) + 1, Int64.Type), #"Added Custom (Persian Day of Month)" = Table.AddColumn(#"Added Custom (Persian Day of Week)", "P.Day of Month", each Number.FromText( Date.ToText( [Date] , [Format="dd", Culture="fa-IR"])), Int64.Type), #"Added Custom (Persian Day of Year)" = Table.AddColumn(#"Added Custom (Persian Day of Month)", "P.Day of Year", each if [P.Month] < 7 then ([P.Month] - 1) * 31 + [P.Day of Month] else if [P.Month] < 12 then 186 + ([P.Month] - 7) * 30 + [P.Day of Month] else 336 + [P.Day of Month], Int64.Type), #"Added Custom (Persian Month Name)" = Table.AddColumn(#"Added Custom (Persian Day of Year)", "P.Month Name", each Date.ToText( [Date] , [Format="MMM", Culture="fa-IR"]), Text.Type), #"Added Custom (Persian Day Name)" = Table.AddColumn(#"Added Custom (Persian Month Name)", "P.Day Name", each Date.ToText( [Date] , [Format="ddd", Culture="fa-IR"]), Text.Type), #"Added Custom (Persian Quarter)" = Table.AddColumn(#"Added Custom (Persian Day Name)", "P.Quarter", each Number.RoundUp( Number.From([P.Month]) / 3, 0), Int64.Type), #"Added Custom (Persian Quarter Name)" = Table.AddColumn(#"Added Custom (Persian Quarter)", "P.Quarter Name", each let m = [P.Quarter] in if m = 1 then "بهار" else if m = 2 then "تابستان" else if m = 3 then "پاییز" else "زمستان", Text.Type), #"Added Custom (Persian Semester)" = Table.AddColumn(#"Added Custom (Persian Quarter Name)", "P.Semester", each Number.RoundUp( Number.From([P.Month]) / 6), Int64.Type), #"Added Custom (Persian Semester Name)" = Table.AddColumn(#"Added Custom (Persian Semester)", "P.Semester Name", each if [P.Semester] = 1 then "نیم سال اول" else "نیم سال دوم", Text.Type), // پنجشنبه و جمعه روزهای تعطیل هفته در نظر گرفته شده است #"Added Custom (Is Persian Weekend)" = Table.AddColumn(#"Added Custom (Persian Semester Name)", "P.Is Weekend", each if [P.Day of Week] = 6 or [P.Day of Week] = 7 then true else false, type logical), // چندین هفته سال شمسی #"Added Custom (Persian Week)" = Table.AddColumn(#"Added Custom (Is Persian Weekend)", "P.Week", each Number.RoundDown( ([P.Day of Year] - [P.Day of Week] + 10) / 7 , 0), Int64.Type), #"Added Custom (Persian Next YearMonth)" = Table.AddColumn(#"Added Custom (Persian Week)", "P.Next YearMonth", each if [P.Month] = 12 then ([P.Year] + 1) * 100 + 1 else [P.YearMonth] + 1, Int64.Type), #"Added Custom (Persian Previous YearMonth)" = Table.AddColumn(#"Added Custom (Persian Next YearMonth)", "P.Previous YearMonth", each if [P.Month] = 1 then ([P.Year] - 1) * 100 + 12 else [P.YearMonth] - 1, Int64.Type), Stage_1 = #"Added Custom (Persian Previous YearMonth)", // تاریخ میلادی شروع نوروز - اولین روز سال #"Added Custom (Geregorian Date of Nuwruz)" = let tbl1 = List.Buffer(Stage_1[P.Date Serial]) in Table.AddColumn(Stage_1, "P.Geregorian Date of Nowruz", each let first_date = Number.FromText(Text.From( [P.Year] ) & "0101"), i = List.PositionOf(tbl1, first_date) in if i > -1 then Stage_1[Date]{i} else null, type date) in #"Added Custom (Geregorian Date of Nuwruz)"