Forum Discussion

madhumnair's avatar
madhumnair
New Member
3 years ago
Solved

Create a Date Table will all essentials for a PowerBI Analytic project

HI there i like to share a date table created which can be resued to all your PowerBI analytic project.     let Source = List.Dates( #"Startdate", Duration.Days( Enddate - #"Startdate" ) + 1, #d...
  • madhumnair's avatar
    3 years ago

    Another method

     

     

    let
    Source = List.Dates( #"Startdate", Duration.Days( Enddate - #"Startdate" ) + 1,
    #duration(1, 0, 0, 0)
    ),
    #"Expanded Dates" = Table.FromList(
    Source,
    Splitter.SplitByNothing(),
    { "Dates" }, // Adds Column Name
    null,
    ExtraValues.Error
    ),
    #"Duplicate Date" = Table.DuplicateColumn(#"Expanded Dates", "Dates", "Dates - Copy"),
    #"DDMMYYY split" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicate Date", {{"Dates - Copy", type text}}, "en-IN"), "Dates - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"DD", "MM", "YYYY"}),
    WEEKNUMBER = Table.AddColumn(#"DDMMYYY split", "WeekNo", each Text.PadStart( Text.From(
    Date.WeekOfYear([Dates])),2,"0")),
    WEEKNAME = Table.AddColumn(WEEKNUMBER, "Weekname", each Date.DayOfWeekName([Dates])),
    WEEKDAYNUMBER = Table.AddColumn(WEEKNAME, "Weekdayno", each Date.DayOfWeek([Dates])),
    MONTHSHORTNAME = Table.AddColumn(WEEKDAYNUMBER, "MonthShortName", each Text.Start(Date.MonthName([Dates]),3)),
    MONTHLONGNAME = Table.AddColumn(MONTHSHORTNAME, "Monthlongname", each Date.MonthName([Dates])),
    DATEDIFFERENCE = Table.AddColumn(MONTHLONGNAME, "DATE_DIFF", each Duration.Days([Dates]-


    Date.From(
    DateTime.LocalNow()))),
    DATEFILTER = Table.AddColumn(DATEDIFFERENCE, "Filter_Date", each if [DATE_DIFF] = 0 then "Today" else if [DATE_DIFF] = 1 then "Tomorrow" else if [DATE_DIFF] = -1 then "Yesterday" else null),
    WEEKFILTER = Table.AddColumn(DATEFILTER, "Filter_Week", each if
    Date.IsInCurrentWeek([Dates]) then "ThisWeek" else if
    Date.IsInPreviousNWeeks([Dates],1) then "Previous Week" else if Date.IsInNextNWeeks([Dates],1) then "Next Week" else null),
    MONTHFILTER = Table.AddColumn(WEEKFILTER, "Filter_Month", each if
    Date.IsInCurrentMonth([Dates]) then "This Month" else if
    Date.IsInPreviousNMonths([Dates],1) then "Previous Month" else if Date.IsInNextNMonths([Dates],1) then "Next Month" else null),
    YEARFILTER = Table.AddColumn(MONTHFILTER, "Filter_Year", each if
    Date.IsInCurrentYear([Dates]) then "This Year" else if
    Date.IsInPreviousNYears([Dates],1) then "Previous Year" else if Date.IsInNextNYears([Dates],1) then "Next Year" else null),
    #"YYYY-MM" = Table.AddColumn(YEARFILTER, "YYYY-MM", each

    [YYYY] & "-" & [MM] )
    in
    #"YYYY-MM"