Dynamic Data Source Error - Date

[elaine1217]

Hello,

I have a dashboard that refreshes fine in Desktop but when I publish it is saying the below query is a dynamic source and can't refresh. I have used the same source in another dashboard that is refreshing so not sure what the problem is. Let me know if there is a better way to do this. Thanks!

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refresh Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Last Refresh Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Last Refresh Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Year", each Date.Year([Last Refresh Date])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Month", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "FY", each if [Month] > 9 then [Year]+1 else [Year]),
#"Extracted Last Characters" = Table.TransformColumns(#"Added Custom2", {{"FY", each Text.End(Text.From(_, "en-US"), 2), type text}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"FY", Int64.Type}})
in
#"Changed Type2"

UPDATE: I am thinking the error message is incorrect. This query used to be called "Query1" so I rewrote it to use an Excel as a source instead of DateTime. The service is still saying that Query1 is the dynamic data source, but Query1 no longer exists.

I am wondering if this query is the issue. My dashboard uses an Excel document with file names listed which are then imported. This allows users to update the file name in the Excel sheet and have it automatically use a more recent version of the document. The statmenet that imports it is below. Basically it is importing the first row of a filtered excel sheet. Any ideas on how to make this data source not dynamic?

= #"Filtered Rows"{0}[Contents]{[Item=#"Filtered Rows"{0}[#"Tab Name(s)"], Kind="Sheet"]}[Data]

Update - i did open a ticket with Microsoft and they aren't able to identify the issue. The Query1 error message is generic error. I have used old versions and defintely the issue is the above statement (= #"Filtered Rows"{0}[Contents]{[Item=#"Filtered Rows"{0}[#"Tab Name(s)"], Kind="Sheet"]}[Data]). 

This is how I import all my data so it is pretty critical - I can't really replace it, but is there anyway to alter it so it will allow PowerBI to refresh?

[elaine1217]

Can you walk me through using an external reference table or link an article? Honestly I just need this thing to be able to refresh automatically asap, even if it's not the 'ideal' solution

[lbendlin]

Create an Excel spreadsheet that has all necessary dates and their yearweek number as per your definition. Place that file on a Sharepoint or OneDrive.  Ingest it as a data source in your semantic model.

[elaine1217]

Ok - this didn't help. I actually removed the Query, wrote a new one to do what you recommended, and re-published and am getting the same error. I tried ignoring privacy levels as this issue in the past has been related to conflicting privacy levels. Still getting this error, despite the fact that 'Query1' no longer exists in the file. 

[lbendlin]

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[lbendlin]

1. DateTime.LocalNow() has no meaning in the Power BI service, it will fall back to UTC

2. "Last Refresh Date"  is largely meaningless.  The Power BI Service will happily refresh your old stale data over and over again if you ask it to.  

Drop this idea, and instead use a date column from your actual fact table to indicate the last modified date. That is actually meaningful for your users.

[elaine1217]

1. Can you explain that one further?

2. That's not the only thing i use this for - I have to calculate a fiscal year from the date and compare it to some data. 

Thanks. 

[lbendlin]

1. there is no "local"  in the Power BI service.

2. 

I have to calculate a fiscal year from the date

No, you don't.  Resist the urge, and instead use an external reference table.  This data is immutable, there is no point in trying to calculate it.