Forum Discussion
Date Query
Hi Guys,
I have used a video explaining how to make a query to have a date table. Worked pretty well, until I found out my client uses dates in the future. I need to change the code to include all dates from 2017. 01/01/2017 to 31/12/2017. Any idea? Thank you!
This is the code:
let
Source = List.Dates,
#"Invoke dates" = Source(#date(2017, 04, 01), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())- #date(2017, 04, 01))+1, #duration(1, 0, 0, 0)),
#"List to table" = Table.FromList(#"Invoke dates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Date = Table.RenameColumns(#"List to table",{{"Column1", "Date"}}),
#"Day Added" = Table.AddColumn(Date, "Day", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
#"Day Name Added" = Table.AddColumn(#"Day Added", "Day Name", each Date.ToText([Date],"ddd","pt-BR")),
#"Month No Added" = Table.AddColumn(#"Day Name Added", "Month No", each Date.ToText([Date],"MM","pt-BR")),
#"Month Name Added" = Table.AddColumn(#"Month No Added", "Month Name", each Date.ToText([Date],"MMM","pt-BR")),
#"Quarter No Added" = Table.AddColumn(#"Month Name Added", "Quarter No", each Date.QuarterOfYear([Date])),
#"Week No Added" = Table.AddColumn(#"Quarter No Added", "Week No", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])-1),2,"0")),
#"Year Added" = Table.AddColumn(#"Week No Added", "Year", each Date.Year([Date])),
#"Year Month Added" = Table.AddColumn(#"Year Added", "Year-Month", each Number.ToText([Year])&"-"&[Month Name]),
#"Year Quarter Added" = Table.AddColumn(#"Year Month Added", "Year-Quarter", each Number.ToText([Year]) & "Q"& Number.ToText([Quarter No],"00")),
#"Change type to text" = Table.TransformColumnTypes(#"Year Quarter Added",{{"Year", type text}, {"Date", type date}, {"Month No", type text}, {"Day", type text}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter No", type text}, {"Year-Quarter", type text}, {"Year-Month", type text}}),
#"Sort Day" = Table.AddColumn(#"Change type to text", "SortDay", each Date.Day([Date])),
#"Sort DayName" = Table.AddColumn(#"Sort Day", "SortDayName", each Date.DayOfWeek([Date],1)),
SortWeekNo = Table.AddColumn(#"Sort DayName", "SortWeekNo", each Date.WeekOfYear([Date])-1),
#"Sort YearMonth" = Table.AddColumn(SortWeekNo, "SortYearMonth", each [Year]&Text.PadStart([Month No],2,"0")),
#"Sort YearQuarter" = Table.AddColumn(#"Sort YearMonth", "SortYearQuarter", each [Year]&Text.PadStart([Quarter No],2,"0")),
#"Changed Type to NO" = Table.TransformColumnTypes(#"Sort YearQuarter",{{"SortYearMonth", Int64.Type}, {"SortYearQuarter", Int64.Type}, {"SortDayName", Int64.Type}, {"SortDay", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type to NO",{{"Date", "Data"}, {"Day", "Dia"}, {"Day Name", "Dia nome"}, {"Month No", "Mês número"}, {"Month Name", "Mês nome"}, {"Year", "Ano"}, {"Year-Month", "Ano-mês"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Quarter No", "Week No", "Year-Quarter", "SortDay", "SortDayName", "SortWeekNo", "SortYearMonth", "SortYearQuarter"}),
in
Remark: your code is incomplete as there is nothing after "in" and a comma is preceding "in".
It doesn't matter though as the adjustment will be in the first part of the code.One way, closest to your solution, adjust the second step to:
#"Invoke dates" = Source(#date(2017, 1, 1), Duration.Days(#date(2017,12,31)- #date(2017, 1, 1))+1, #duration(1, 0, 0, 0)),
Another way (my preference): fist create a list with numbers representing dates, after conversion to table, adjust the data type to date.
let Source = {Number.From(#date(2017,1,1))..Number.From(#date(2017,12,31))}, #"List to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"List to table",{{"Column1", type date}}), Date = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),No further changes to the code.
2 Replies
Remark: your code is incomplete as there is nothing after "in" and a comma is preceding "in".
It doesn't matter though as the adjustment will be in the first part of the code.One way, closest to your solution, adjust the second step to:
#"Invoke dates" = Source(#date(2017, 1, 1), Duration.Days(#date(2017,12,31)- #date(2017, 1, 1))+1, #duration(1, 0, 0, 0)),
Another way (my preference): fist create a list with numbers representing dates, after conversion to table, adjust the data type to date.
let Source = {Number.From(#date(2017,1,1))..Number.From(#date(2017,12,31))}, #"List to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"List to table",{{"Column1", type date}}), Date = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),No further changes to the code.
Thanks Marcel! Worked! I'll try the other way when I learn a little more!! :-) Thanks!!
