Hi community,
I have a Sql direct query source and built a dynamic Date Table on Power query editor (Please check the code below).
The code was provided from a thread in power bi community and it works as intended.
The main idea was to create a Date Table from 01-01-2016 until today (dinamically).
The only issue is this table date is not refreshing on Power Bi Service when another day comes in!
The today date, remains static relating to the last day i published the Dataset. To make it work i need to republish from PBI Desktop everyday. One solution was to key in (2º line) Lenght+10 and by doing so it gives me the 10 next days.
I've saw several threads about it but no one solved my issue.
Does anyone knows how to solve it?
let
Source = List.Dates(StartDate, Length+10, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
StartDate = #date(2016, 1, 1),
Today = DateTime.Date(DateTime.LocalNow()),
Length = Duration.Days(Today - StartDate),
Custom1 = #"Changed Type",
#"Sorted Rows" = Table.Sort(Custom1,{{"Date", Order.Descending}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type date}}, "pt-PT"),
#"Inserted Year" = Table.AddColumn(#"Changed Type with Locale", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek(([Date]), Day.Sunday), Int64.Type),
#"Inserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MM", each Text.Start([Month Name], 3), type text),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted First Characters", "Week of Year", each Date.WeekOfYear([Date]),Int64.Type),
#"Inserted First Characters1" = Table.AddColumn(#"Inserted Week of Year", "DD", each Text.Start([Day Name], 3), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Date", "Year", "Month Name", "MM", "Month", "Day Name", "DD", "Day of Week"})
in
#"Reordered Columns"
