Dynamic Date Table not refreshing on Power Bi Service

[moutinhoabreu]

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"

[v-janeyg-msft]

Hi, @moutinhoabreu 

Please reference this part: refresh-and-dynamic-data-sources

In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. You can open the Data Source Settings dialog in Power Query Editor, and then select Data Sources In Current File.

If that warning is present in the Data Source Settings dialog that appears, then a dynamic data source that cannot be refreshed in the Power BI service is present.

If there is no warning, then you can refresh through the gateway.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[moutinhoabreu]

Hi @v-janeyg-msft Thanks for your reply.

No message appears, when opening Data Source Settings.

So i don't understand why its not working. I see several similar cases on the web, with direct query source, where the data table is refreshing.

Thanks.

[v-janeyg-msft]

@moutinhoabreu   Did you install gateway and set schedule refresh? Is 'refresh now' successful?

[moutinhoabreu]

Yea, there's a gateway installed, latest version and it's working otherwise the sql direct query would not update automatically.

@v-janeyg-msft when you mention schedule refresh I don't understand as in direct query there's no need to setup schedule refresh as we work with live data. 
Thanks 

[v-janeyg-msft]

@moutinhoabreu  OK, Have you refreshed the browser cache or page refresh? There may be a delay.

If everything is normal, I think it should be dynamic data sources refrresh restrictions.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[moutinhoabreu]

Hi @v-janeyg-msft Thanks for your reply.

Yes i did cleaned my browser cache. Regarding delay it's excluded because day after day the date table doesn't refresh at all. It remains on last day i pusblished from PBI Desktop.

Its weird as Microsoft annouces Dinamyc date table and in my case it's more Static Date Table. At no point reading the Microsoft documents i see any kind of restrictions for building a M language Dinamyc date table on a Sql On premises direct query.

Thanks anyway for your effort. Lets hope someone can help with my trhead.

Kind regards.