Hola Comunidad - Tengo esta tabla de fechas que he importado del equipo en Enterprise DNA.
Es genial, pero me estoy dando cuenta de que está configurado muy específicamente para las normas ISO. Nuestra empresa no informa el uso de ISO. Por ejemplo, nuestra semana actual es la semana 25. ISO es 24.
Estaba usando este mcode antes para el número de semana, pero ahora con esta tabla de fechas no puedo agregarlo.
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Número de semana", cada Date.WeekOfYear([Date],1)),
Idealmente, solo quiero una tabla de fechas de calendario "normal" sin los detalles de las normas ISO. ¿Hay alguna manera de modificar esto para lograr esto?
let fnDateTable = (StartDate como fecha, EndDate como fecha, opcional FYStartMonthNum como número, opcional Holidays como lista, opcional WDStartNum como número ) como tabla =>
dejar
StartDate = #date(2017,1,1),
EndDate = #date(2024,12,31),
FYStartMonthNum = 1,
Días festivos = null,
WDStartNum = 1,
FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,
StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3)),
WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
CurrentDate = Date.From(DateTime.FixedLocalNow()),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Origen = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", escriba date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", cada Date.Year([Date]), número de tipo),
InsertYearOffset = Table.AddColumn(InsertYear, "YearOffset", cada Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), número de tipo),
InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", cada Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(CurrentDate)), escriba logical),
InsertQuarter = Table.AddColumn(InsertCompletedYear, "QuarterOfYear", cada Date.QuarterOfYear([Date]), número de tipo),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter & Year", cada "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), escriba text),
InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", cada [Año] * 10000 + [QuarterOfYear] * 100, número de tipo),
InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "QuarterOffset", cada uno ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(Date.From(CurrentDate))), número de tipo),
InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", cada Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), escriba logical),
InsertMonth = Table.AddColumn(InsertCompletedQuarter, "MonthOfYear", cada Date.Month([Date]), número de tipo),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", cada Date.Day([Date]), número de tipo),
InsertMonthName = Table.AddColumn(InsertDay, "Month Name", cada Text.Proper( Date.ToText([Date], "MMMM")), escriba text),
InsertMonthShort = Table.AddColumn( InsertMonthName, "MonthShortName", cada intento Text.Proper( Text.Start([Month Name], 3 )) otherwise Text.Proper( [Month Name] ), escriba text),
InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", cada Text.Proper(Text.Start([Month Name], 1)) & Text.Repeat( Character.FromNumber(8203), [MonthOfYear] ), escriba text),
InsertCalendarMonth = Table.AddColumn(InsertMonthInitial, "Mes & Año", cada [MonthShortName] & " " & Number.ToText([Year]), escriba texto),
InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", cada [Year] * 10000 + [MonthOfYear] * 100, número de tipo),
InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "MonthOffset", cada uno ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), número de tipo),
InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", cada Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), escriba logical),
InsertMonthEnding = Table.AddColumn(InsertCompletedMonth, "MonthEnding", cada Date.EndOfMonth([Date]), escriba date),
InsertDayInt = Table.AddColumn(InsertMonthEnding, "DateInt", cada [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], número de tipo),
InsertDayOfYear = Table.AddColumn(InsertDayInt, "Day of Year", cada Date.DayOfYear([Date]), Int64.Type),
InsertDayWeek = Table.AddColumn(InsertDayOfYear, "DayOfWeek", cada Date.DayOfWeek([Date]) + WDStart, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", cada Text.Proper( Date.ToText([Date], "dddd" )), escriba text),
InsertDayInitial = Table.AddColumn(InsertDayName, "Weekday Initial", cada Text.Proper(Text.Start([DayOfWeekName], 1)) & Text.Repeat( Character.FromNumber(8203), [DayOfWeek] ), escriba text),
InsertWeekNumber= Table.AddColumn(InsertDayInitial, "ISO Weeknumber", cada uno
if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 y (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", cada Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )), Int64.Type),
BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[Año ISO], [DateInt]])),
InsertISOqNum = Table.AddColumn(InsertISOyear, "ISO QuarterOfYear", cada uno si [ISO Weeknumber] >39 then 4 else if [ISO Weeknumber] >26 then 3 else if [ISO Weeknumber] >13 then 2 else 1, Int64.Type),
InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", cada "Q" & Number.ToText([ISO QuarterOfYear]), escriba texto),
InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", cada "Q" & Number.ToText([ISO QuarterOfYear]) & " " & Number.ToText([ISO Year]), escriba texto),
InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", cada [Año ISO] * 10000 + [ISO QuarterOfYear] * 100, número de tipo),
InsertISOday = Table.AddColumn(InsertISOqNy, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])), Int64.Type),
InsertCalendarWk = Table.AddColumn(InsertISOqNy, "Week & Year", each Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), escriba text ),
InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", cada [Año ISO] * 10000 + [ISO Weeknumber] * 100, Int64.Type),
InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "WeekOffset", cada uno (Number.From(Date.StartOfWeek([Date], Day.Monday)))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, número de tipo),
InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", cada Date.EndOfWeek( [Date], Day.Monday) < Date.From(Date.EndOfWeek(CurrentDate, Day.Monday)), escriba logical),
InsertWeekEnding = Table.AddColumn(InsertCompletedWeek, "WeekEnding", cada Date.EndOfWeek( [Date], Day.Monday), escriba date),
AddFY = Table.AddColumn(InsertWeekEnding, "Fiscal Year", cada "FY" & (if [MonthOfYear] >= FYStartMonth then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), escriba text),
AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", cada "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), escriba text),
AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", cada uno (si [MonthOfYear] >= FYStartMonth then [Year] +1 else [Year]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ) * 100, número de tipo),
AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period", cada uno si [MonthOfYear] >= FYStartMonth then [MonthOfYear] - (FYStartMonth-1) else [MonthOfYear] + (12-FYStartMonth+1), escriba texto),
AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", cada uno (si [MonthOfYear] >= FYStartMonth then [Year] +1 else [Year]) * 10000 + [Fiscal Period] * 100, número de tipo),
FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", cada uno si Date.Month([Date]) < FYStartMonth then #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn(
Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart).. Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", escriba date}}), {{"Column1", "Date"}}),
"FiscalFirstDay", cada uno si Date.Month([Date]) < FYStartMonth luego #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
"FWStartDate", cada Date.AddYears(Date.StartOfWeek( [Fecha], Día.Lunes), 1)),
{"FiscalFirstDay", "FWStartDate"}, {{"AllRows", cada _, escriba table [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}),
{"FiscalFirstDay"}, {{"AllRows2", cada _, escriba table [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}),
"Custom", cada Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]],
"Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}),
"AllRows", {"Date"}, {"Date"})[[Date], [FY Week]]
),
MergeFYW = Table.NestedJoin(InsertFFD, {"Date"}, AddFYDateRange, {"Date"}, "AddFYWeek", JoinKind.LeftOuter),
ExpandFYWeek = Table.TransformColumnTypes( Table.ExpandTableColumn(MergeFYW, "AddFYWeek", {"FY Week"}, {"Fiscal Week"}),{{"Fiscal Week", Int64.Type}}),
AddFYW = Table.AddColumn( ExpandFYWeek, "Fiscal Year & Week", cada uno si FYStartMonth =1 then [#"Week & Year"] else if Date.Month([Date]) < FYStartMonth then Text.From( Date.Year([Date])) & "-" & Text.PadStart( Text.From([Fiscal Week]), 2, "0") else Text.From( Date.Year([Date])+1) & "-" & Text.PadStart(Text.From([Fiscal Week]), 2, "0"), escriba text),
InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", cada uno si FYStartMonth =1 then [WeeknYear] else (if Date.Month([Date]) < FYStartMonth then Date.Year([Date]) else Date.Year([Date])+1) * 10000 + [Fiscal Week] * 100, Int64.Type),
InsertIsAfterToday = Table.AddColumn(InsertFWeeknYear, "IsAfterToday", cada uno no ([Date] <= Date.From(CurrentDate)), escriba logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", cada uno if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, escriba logical),
InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", cada uno si Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", cada uno si [IsWorkingDay] = true y [IsHoliday] <> true then true else false, escriba logical),
InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, escriba text),
CurrentDateRecord = Table.SelectRows(InsertDayType, cada uno ([Date] = CurrentDate)),
CurrentISOyear = CurrentDateRecord{0}[Año ISO],
CurrentISOqtr = CurrentDateRecord{0}[ISO QuarterOfYear],
CurrentYear = CurrentDateRecord{0}[Año],
CurrentMonth = CurrentDateRecord{0}[MonthOfYear],
CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
CurrentFW = CurrentDateRecord{0}[FWeeknYear],
InsertISOQtrOffset = Table.AddColumn(InsertDayType, "ISO QuarterOffset", cada uno ((4 * [ISO Year]) + [ISO QuarterOfYear]) - ((4 * CurrentISOyear) + CurrentISOqtr), número de tipo),
InsertISOYrOffset = Table.AddColumn(InsertISOQtrOffset, "ISO YearOffset", cada [Año ISO] - CurrentISOyear, número de tipo),
InsertFYoffset = Table.AddColumn(InsertISOYrOffset, "FiscalYearOffset", cada entento (si [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
InsertCurrentFQ = Table.AddColumn(InsertFYoffset, "IsCurrentFQ", cada uno if [FQuarternYear] = CurrentFQ then true else false, tipo logical),
InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", cada uno if [FPeriodnYear] = CurrentFP then true else false, escriba logical),
InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", cada uno si [FWeeknYear] = CurrentFW then true else false, escriba logical),
InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", cada uno si CurrentYear-1 = [Year] y [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, escriba logical),
ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoin(
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", escriba date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"},
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1)
, {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), cada [DateFY] <= CurrentDate)[PrevDateFY] ),
InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", cada uno if [FiscalYearOffset] = -1 y List.Contains(ListPrevFYDates, [Date] ) then true else false, escriba logical),
RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertPFYTD, each ([Date] <> CurrentDate)) else InsertPFYTD, {"Day of Year", "FiscalFirstDay"}),
ChType = Table.TransformColumnTypes(RemoveToday,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {" FiscalYearOffset", Int64.Type}}),
ReorderColumns = Table.ReorderColumns(ChType, {"Date", "Year", "YearOffset", "YearCompleted", "QuarterOfYear", "Quarter &Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month &Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "Day Type", "ISO Year", "ISO YearOffset", "ISO QuarterOfYear", "ISO Quarter", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO QuarterOffset", "ISO Weeknumber", "Week & Year", "WeeknYear", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year", "FiscalYearOffset", "Fiscal Quarter", "FQuarternYear", "IsCurrentFQ", "Fiscal Period", "FPeriodnYear", "IsCurrentFP", "Fiscal Week", "Fiscal Year & Week", "FWeeknYear", "IsCurrentFW", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "IsPYTD", "IsPFYTD"}, MissingField.UseNull)
in
ReorderColumns // documentación = [
Documentation.Name = " fxCalendar",
Documentation.Description = "Función de tabla de fechas para crear un calendario ISO-8601",
Documentation.LongDescription = "Función de tabla de fechas para crear un calendario ISO-8601",
Documentation.Category = " Tabla",
Documentation.Version = " 1.25: Se han añadido cuartos ISO y desplazamientos",
Documentation.Source = " local",
Documentation.Author = " Melissa de Korte ",
Documentation.Examples = { [Descripción = " Ver: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390",
Código = " Parámetros opcionales: #(lf)
(FYStartMonthNum) Número de mes que comienza el año fiscal, Januari si se omite #(lf)
(Días festivos) Seleccione una consulta (y columna) que contenga una lista de fechas de vacaciones #(lf)
(WDStartNum) Cambie la numeración predeterminada de días laborables de 0-6 a 1-7 introduciendo un 1 #(lf)
#(lf)
Importante a tener en cuenta: #(lf)
[Semana Fiscal] comienza un lunes y puede contener menos de 7 días en una primera y / o última semana de un año fiscal # (lf)
[IsWorkingDay] no tiene en cuenta las fechas de vacaciones #(lf)
[IsBusinessDay] tiene en cuenta las fechas opcionales de vacaciones #(lf)
[IsPYTD] y [IsPFYTD] comparan anterior [Día del año] con el número actual [Día del año], por lo que las fechas no se alinean en los años bisiestos",
Resultado = " " ] }
//]
en
Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), documentación))
