Fabio Cerqueira
- 16 de jul.
- 3 min para ler

PowerBI | dCalendário Completo

Clique em "Obter dados" e depois em "Consulta em Branco"

O PowerBi vai abrir uma tela conhecida como Power Query. Logo em seguida clique em "Editor Avançado"

O PowerBI vai abrir a tela para você adicionar o código em linguagem M. Apague completamente o código que é adicionado automaticamente.

Com o campo completamente em branco, cole esse script abaixo:

let

P_Today = DateTime.LocalNow(),

P_StartDate = #date(2018, 1, 1),

P_EndDate = #date(Date.Year(P_Today),12,31),

P_Culture = "en-EN",

P_FirstDayOfWeek = 1,

P_IsCarnivalHoliday = true,

Holiday = if P_Culture = "pt-PT" then "Feriado" else "Holiday",

Quarter = if P_Culture = "pt-PT" then "T" else "Q",

Week = if P_Culture = "pt-PT" then "S" else "W",

Weekend = if P_Culture = "pt-PT" then "Fim de Semana" else "Weekend",

WorkDay = if P_Culture = "pt-PT" then "Dia Útil" else "Work Day",

DayCount = Duration.Days(Duration.From(P_EndDate - P_StartDate)) + 1,

Source = List.Dates(P_StartDate, DayCount, #duration(1, 0, 0, 0)),

TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),

ChangedType = Table.TransformColumnTypes(TableFromList, {{"Column1", type date}}),

RenamedColumns = Table.RenameColumns(ChangedType, {{"Column1", "Date"}}),

InsertId = Table.AddColumn(RenamedColumns, "DateId", each Date.Year([Date])*10000 + Date.Month([Date])*100 +Date.Day([Date])),

InsertYear = Table.AddColumn(InsertId, "Year", each Date.Year([Date])),

InsertQuarter = Table.AddColumn(InsertYear, "Quarter", each Date.QuarterOfYear([Date])),

InsertSemester = Table.AddColumn(InsertQuarter, "Semester", each if [Quarter] < 3 then 1 else 2),

InsertMonth = Table.AddColumn(InsertSemester, "Month (#)", each Date.Month([Date])),

InsertWeek = Table.AddColumn(InsertMonth, "Week", each Date.WeekOfYear([Date], P_FirstDayOfWeek)),

InsertDay = Table.AddColumn(InsertWeek, "Day", each Date.Day([Date])),

InsertMonthName = Table.AddColumn(InsertDay, "Month (Long)", each Date.ToText([Date], "MMMM", P_Culture), type text),

InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month", each try(Text.Range([#"Month (Long)"],0,3)) otherwise [#"Month (Long)"]),

InsertCalendarWeek = Table.AddColumn(InsertShortMonthName, "Week (Year)", each Week & Number.ToText([Week]) & " " & Number.ToText([Year])),

InsertCalendarMonth = Table.AddColumn(InsertCalendarWeek, "Month (Year)", each [#"Month"] & " " & Number.ToText([Year])),

InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter (Year)", each Quarter & Number.ToText([Quarter]) & " " & Number.ToText([Year])),

InsertCalendarSem = Table.AddColumn(InsertCalendarQtr, "Semester (Year)", each "S" & Number.ToText([Semester]) & " " & Number.ToText([Year])),

InsertDayWeek = Table.AddColumn(InsertCalendarSem, "Week Day (#)", each Date.DayOfWeek([Date], P_FirstDayOfWeek ) + 1),

InsertDayName = Table.AddColumn(InsertDayWeek, "Week Day", each Date.ToText([Date], "dddd", P_Culture), type text),

InsertWeekYear = Table.AddColumn(InsertDayName, "WeekYearId", each [Year] * 100 + [Week]),

InsertMonthYear = Table.AddColumn(InsertWeekYear, "MonthYearId", each [Year] *100 + [#"Month (#)"]),

// InsertStartWeek = Table.AddColumn(InsertWeekYear , "Start of Week", each Date.StartOfWeek([Date], P_FirstDayOfWeek), type date),

// InsertEndWeek = Table.AddColumn(InsertStartWeek , "End of Week", each Date.EndOfWeek([Date], P_FirstDayOfWeek), type date),

InsertQuarterYear = Table.AddColumn(InsertMonthYear, "QuarterYearId", each [Year] * 100 + [Quarter]),

InsertSemesterYear = Table.AddColumn(InsertQuarterYear, "SemesterYearId", each [Year] * 100 + [Semester]),

#"Capitalized Each Word" = Table.TransformColumns(InsertSemesterYear,{{"Month (Long)", Text.Proper}, {"Month", Text.Proper}, {"Month (Year)", Text.Proper}, {"Week Day", Text.Proper}}),

#"Relative (Year)" = Table.AddColumn(#"Capitalized Each Word", "Year (Relative)", each [Year] - Date.Year(P_Today)),

#"Relative (Month)" = Table.AddColumn(#"Relative (Year)", "Month (Relative)", each [#"Year (Relative)"] * 12 + ([#"Month (#)"] - Date.Month(P_Today))),

#"Relative (Week)" = Table.AddColumn(#"Relative (Month)", "Week (Relative)", each Duration.TotalDays(DateTime.Date(Date.StartOfWeek([Date])) - DateTime.Date(Date.StartOfWeek(P_Today))) / 7),

#"Relative (Day)" = Table.AddColumn(#"Relative (Week)", "Day (Relative)", each Duration.TotalDays([Date] - DateTime.Date(P_Today))),

// MergedHolidays = Table.NestedJoin(#"Relative (Day)",{"Date"},GetHoliday(P_StartDate,P_EndDate,P_Culture, P_IsCarnivalHoliday),{"Date"},"Holidays",JoinKind.LeftOuter),

// ExpandedHolidays = Table.ExpandTableColumn(MergedHolidays, "Holidays", {"Holiday"}, {"Holiday"}),

// AddedWorkDay = Table.AddColumn(ExpandedHolidays, "Work Day", each if [Holiday] = null then (if [#"Week Day (#)"] > 5 then Weekend else WorkDay) else Holiday),

#"Reordered Columns" = Table.ReorderColumns(#"Relative (Day)", {"Date", "Day", "Week Day (#)", "Week Day", "Week", "Month (Long)", "Month", "Month (#)", "Quarter", "Semester", "Year", "Week (Year)", "Month (Year)", "Quarter (Year)", "Semester (Year)", "WeekYearId", "MonthYearId", "QuarterYearId", "SemesterYearId", "Day (Relative)", "Week (Relative)", "Month (Relative)", "Year (Relative)"}),

#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns", {{"Day", Int64.Type}, {"Week Day (#)", Int64.Type}, {"Week", Int64.Type}, {"Month (#)", Int64.Type}, {"Quarter", Int64.Type}, {"Semester", Int64.Type}, {"Year", Int64.Type}, {"Week (Year)", type text}, {"Quarter (Year)", type text}, {"Semester (Year)", type text}, {"WeekYearId", Int64.Type}, {"SemesterYearId", Int64.Type}, {"MonthYearId", Int64.Type}, {"QuarterYearId", Int64.Type}, {"Day (Relative)", Int64.Type}, {"Month (Relative)", Int64.Type}, {"Year (Relative)", Int64.Type}, {"DateId", Int64.Type}, {"Week (Relative)", Int64.Type}}),

ColumnPT = Table.RenameColumns(#"Changed Type", {{"Date", "Data"}, {"DateId", "DataId"}, {"Day", "Dia"}, {"Week Day (#)", "Dia Semana (#)"}, {"Week Day", "Dia Semana"}, {"Week", "Semana"}, {"Month (Long)", "Mês (Extenso)"}, {"Month", "Mês"}, {"Month (#)", "Mês (#)"}, {"Quarter", "Trimestre"}, {"Semester", "Semestre"}, {"Year", "Ano"}, {"Week (Year)", "Semana (Ano)"}, {"Month (Year)", "Mês (Ano)"}, {"Quarter (Year)", "Trimestre (Ano)"}, {"Semester (Year)", "Semestre (Ano)"}, {"WeekYearId", "SemanaAnoId"}, {"MonthYearId", "MesAnoId"}, {"QuarterYearId", "TrimestreAnoId"}, {"SemesterYearId", "SemestreAnoId"}, {"Day (Relative)", "Dia (Relativo)"}, {"Month (Relative)", "Mês (Relativo)"}, {"Year (Relative)", "Ano (Relativo)"}, {"Week (Relative)", "Semana (Relativa)"}}),

result = if P_Culture = "pt-PT" then ColumnPT else #"Changed Type"

result