topic Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. " in Service

Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Wed, 26 Aug 2020 16:54:19 GMT

Hi there,

I have developed a dashboard where the data is being pulled from Jira. The limitation of JQL is it gives 100 results at a time. So, I had to run loop to get all the data using Power Query. It is working fine in Power BI desktop but when I publish the same report in Power BI service. It gives me the below error message. Not sure, how to resolve this. Please help.

Error snapshot:

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

Greg_Deckler — Wed, 26 Aug 2020 16:59:54 GMT

ghoshabhijeet - Huh, that's interesting. I know that ImkeF has done similar things looping through websites and I don't remember that error coming up. You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ImkeF — Wed, 26 Aug 2020 18:48:29 GMT

Hi ghoshabhijeet ,

this blogpost consolidates all relevant info about dynamic datasources: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Sat, 29 Aug 2020 17:37:08 GMT

ImkeF Thanks for sharing the links. The blogpost did help, though the way to put it in Power Query was a bit tricky.

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Sat, 29 Aug 2020 17:39:30 GMT

Greg_Deckler Thanks for your reply and tagging ImkeF in the reply. The blogpost she shared did help.

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

Anonymous — Thu, 10 Mar 2022 07:46:14 GMT

Hi ghoshabhijeet ,

Could you kindly share how you solved the issue of "Dynamic Data source"? (The mentioned blog post is not available when opening). I have done the same of connecting to a JIRA rest API and everything is working perfectly in PBI desktop, however once published the datasource cant be refreshed. My code for reference below, any help will be greatly appreciated.

(Returns Credentials used in next query)

let
Source = "email" & ":" & "token",
Bytes = Text.ToBinary(Source),
TextForm = Binary.ToText(Bytes,BinaryEncoding.Base64)
in
TextForm

let
BaseUrl = "https://companyname.net/rest/api/3/search?jql=project in ('BI')&maxResults=100",
JiraIDPerPage = 100,

GetJson = (Url) =>
let
RawData = Web.Contents(Url,[Headers=[Authorization="Basic " & Credentials]]),
Json = Json.Document(RawData)
in Json,

GetJiraIDCount = () =>
let Url = BaseUrl & "&maxResults=0",
Json = GetJson(Url),
Count = Json[#"total"]
in Count,

GetPage = (Index) =>
let Skip = "&startAt=" & Text.From(Index * JiraIDPerPage),
Top = "&maxResults=" & Text.From(JiraIDPerPage),
Url = BaseUrl & Skip & Top,
Json = GetJson(Url),
Value = Json[#"issues"]
in Value,

JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
JiraID = List.Union(Pages),
Table = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Fri, 11 Mar 2022 13:41:21 GMT

Anonymous Can you please share the error message you get while refresh ?

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

Anonymous — Mon, 14 Mar 2022 07:09:48 GMT

Thanks for your response, please see below:

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Sun, 20 Mar 2022 19:53:01 GMT

AnonymousSorry, for late reply !

You have to create relative path for the url instead of static one.

For example:
Source1 = Json.Document(Web.Contents("https://companyname.net",[RelativePath="/rest/agile/1.0/board/1/sprint",Query=[state="active"]])),

So, with your code the URL should look something like this:
BaseUrl = "https://companyname.net",[RelativePath="/rest/api/3/search?jql=project in('BI')&maxResults=100"]

Try using this URL, I hope it will work. If it doesn't work, please share your .pbix file, I can help you fix the code in your file.

---------------------------------------------------------------------------------
** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search. Thank you !

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

Anonymous — Tue, 22 Mar 2022 14:09:59 GMT

ghoshabhijeet , thanks for your time.

I have tried that query, however I receive an error. I am unable to attach the pbix (.pbix not supported; any ideas?) below the full query with the error.

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Wed, 23 Mar 2022 09:27:36 GMT

Anonymous You can upload in onedrive, google drive or dropbox and share the link.

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

Anonymous — Wed, 23 Mar 2022 09:37:02 GMT

ghoshabhijeet ,

https://www.dropbox.com/s/df7mf0sfl1uc797/BI%20Project%20Management_test.pbix?dl=0

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Fri, 25 Mar 2022 03:50:34 GMT

Anonymous I have worked on your issue and it's solved. I have tested the solution on Power BI Service and the code works perfectly fine. The data is getting refreshed in Power BI Service.

Here is my M-Code solution:

let
Source = Json.Document(Web.Contents("https://insyncsolutions.atlassian.net",
[RelativePath="/rest/api/3/search",Query=[q="jql=project in('BI')"],
Headers=[Authorization="Basic " & Credentials]])),
totrecords = Source[total],
CurrentstartAtList = List.Generate(()=>0, each _ < totrecords, each _ +100),
//a= List.Transform(CurrentstartAtList, each """" & Text.From(_) & """"),

data = List.Transform(CurrentstartAtList, each Json.Document(Web.Contents("https://insyncsolutions.atlassian.net",
[RelativePath="/rest/api/3/search",
Query=[maxResults="100",startAt=Text.From(_),jql="project in('BI')"],
Headers=[Authorization="Basic " & Credentials]]))),
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"expand", "startAt", "maxResults", "total", "issues"}, {"Column1.expand", "Column1.startAt", "Column1.maxResults", "Column1.total", "Column1.issues"}),
#"Expanded Column1.issues" = Table.ExpandListColumn(#"Expanded Column1", "Column1.issues"),
#"Expanded Column1.issues1" = Table.ExpandRecordColumn(#"Expanded Column1.issues", "Column1.issues", {"id", "key", "fields"}, {"id", "key", "fields"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.issues1",{"Column1.expand", "Column1.startAt", "Column1.maxResults", "Column1.total"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([id] <> null))
in
#"Filtered Rows"

Power BI Service Data Refresh Success:

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Fri, 25 Mar 2022 06:37:57 GMT

AnonymousIf my solution works, please mark my post as a solution, so that it can help others. Thanks !

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

Anonymous — Fri, 25 Mar 2022 06:51:56 GMT

Thank you. This solutions works.

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

AmyMisa — Mon, 09 May 2022 15:47:15 GMT

I am experiencing a similar issue - I just wanted to ask whether this solution is still working for you? 🙂 Before I go ahead and edit my code

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

Anonymous — Mon, 09 May 2022 15:51:28 GMT

Haven't had any issues with it so far

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

hachishti — Sat, 27 Aug 2022 15:02:26 GMT

Hi ghoshabhijeet ,

I have faced same issue report working on PowerBi desktop but refresh datasource is failed. Can you please look into the query.

https://community.powerbi.com/t5/Power-Query/This-dataset-includes-a-dynamic-data-source-Since-dynamic-data/m-p/2729076#M84307

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

hachishti — Sat, 27 Aug 2022 18:05:57 GMT

Facing same DATASOURCE REFRESH ISSUE issue, but the above link is not working.

Re: Error while schedule refresh - getting error " This dataset includes a dynamic data source. "

ghoshabhijeet — Mon, 05 Sep 2022 16:08:40 GMT

hachishti Sure. Could you please share the pbix file ? If yes, please upload the file on a cloud drive and share the link. If you want you can share the link on private message. Thanks !