Date Table does not load up in power query for transformation

[Anonymous]

Hello All,

I have created a Calendar Table Power BI Desktop using 

= CALENDAR(DATE(2011,1,1),DATE(2020,12,31))

It generates the date but when I try to load it in power query it does not show up.

I also tried marking it as Date Table.  But it does not show up in the Power Query.

Any help would be greatly appreciated

[Watsky · Accepted solution]

@Anonymous  Wasn't sure if you wanted to create a date table in Power Query but here's the one I use:

let
    Source = #date(2011, 1, 1),Encoding = 65001,
    #"Convert to List" = List.Dates(Source, Number.From(#date(2020,12,31))-Number.From(Source),#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(#"Convert to List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Column to Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date Range"}}),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Column to Date",{{"Date Range", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type to Date", "Year", each Date.Year([Date Range]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date Range]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date Range]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted End of Year", "End of Month", each Date.EndOfMonth([Date Range]), type date),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted End of Month", "Month Name", each Date.MonthName([Date Range]), type text),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Month Name", "Start of Week", each Date.StartOfWeek([Date Range]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date Range]), type date),
    #"Duplicated Column" = Table.DuplicateColumn(#"Inserted End of Week", "Date Range", "Weekday"),
    #"Create Weekday" = Table.TransformColumns(#"Duplicated Column", {{"Weekday", each Date.DayOfWeekName(_), type text}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Create Weekday", "Date Range", "Week of Year"),
    #"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column1",{{"Week of Year", Date.WeekOfYear, Int64.Type}}),
    #"Inserted Quarter" = Table.AddColumn(#"Calculated Week of Year", "Quarters",  each  "Q" & Number.ToText(Date.QuarterOfYear([Date Range])),type text),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Quarter", "End of Quarter", each Date.EndOfQuarter([Date Range]), type date)
in
    #"Inserted End of Quarter"

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[parry2k]

@Anonymous if you created a table using DAX expression it will not show up in PQ. You can create a calendar using PQ, there are tons of posts on how to create one.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡