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

[ghoshabhijeet]

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:

[ImkeF · Accepted solution]

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/

[Anonymous]

@ghoshabhijeet  

Am also facing a similar issue, 

I have developed a dashboard that will display the consolidated Test Case Execution and data being pulled from ADO. And I have used parameters and created a Custom function to view the data of multiple Projects in the Same Organization, but when i publish the dashboard i end up with an error... could you please help me to alter the Query when we use Parameters/Custom function.. How to add Relative path..

let
Source = OData.Feed ("https://analytics.dev.azure.com/OrganizationName/"&PlatformParameters&"/_odata/v4.0-preview/TestPoints?"),
#"Expanded Project" = Table.ExpandRecordColumn(Source, "Project", {"ProjectName"}, {"Project.ProjectName"}),
#"Expanded TestSuite" = Table.ExpandRecordColumn(#"Expanded Project", "TestSuite", {"TestPlanTitle", "Title", "TitleLevel1", "TitleLevel2", "TitleLevel3", "TitleLevel4", "TitleLevel5"}, {"TestSuite.TestPlanTitle", "TestSuite.Title", "TestSuite.TitleLevel1", "TestSuite.TitleLevel2", "TestSuite.TitleLevel3", "TestSuite.TitleLevel4", "TestSuite.TitleLevel5"}),
#"Expanded TestCase" = Table.ExpandRecordColumn(#"Expanded TestSuite", "TestCase", {"WorkItemId", "Title", "State", "Area", "Iteration"}, {"TestCase.WorkItemId", "TestCase.Title", "TestCase.State", "TestCase.Area", "TestCase.Iteration"}),
#"Expanded TestCase.Area" = Table.ExpandRecordColumn(#"Expanded TestCase", "TestCase.Area", {"AreaPath"}, {"TestCase.Area.AreaPath"}),
#"Duplicated AreaPath Column" = Table.DuplicateColumn(#"Expanded TestCase.Area", "TestCase.Area.AreaPath", "TestCase.Area.AreaPath - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated AreaPath Column", "TestCase.Area.AreaPath - Copy", Splitter.SplitTextByDelimiter("\", QuoteStyle.Csv), {"TestCase.Area.AreaPath - Copy.1", "TestCase.Area.AreaPath - Copy.2", "TestCase.Area.AreaPath - Copy.3"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each true),
#"Expanded Tester" = Table.ExpandRecordColumn(#"Filtered Rows", "Tester", {"UserName"}, {"Tester.UserName"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Tester",{"LastResultState", "ChangedDateSK", "TestPointSK", "AnalyticsUpdatedDate", "ProjectSK", "TestSuiteSK", "TestConfigurationSK", "TestConfigurationId", "TesterUserSK", "AssignedToUserSK", "Priority", "AutomationStatus"}),
#"Expanded ChangedOn" = Table.ExpandRecordColumn(#"Removed Columns", "ChangedOn", {"Date"}, {"ChangedOn.Date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded ChangedOn",{{"ChangedDate", type datetime}, {"ChangedOn.Date", type datetime}})
in
#"Changed Type"

[ghoshabhijeet]

Thanks for your query. Please open a new thread with the pbix file attached and you can tag me on the post.

[Anonymous]

@ghoshabhijeet i have created new thread, pls help me 

https://community.powerbi.com/t5/Power-Query/Error-while-schedule-refresh-getting-error-quot-This-da...

[hachishti]

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-dynam...

[ghoshabhijeet]

@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 !

[hachishti]

Dear @ghoshabhijeet , Thank you so much for your reply, I will message you separately.

[ehangreens]

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)

[ghoshabhijeet]

@ehangreens Can you please share the error message you get while refresh ?

[ehangreens]

Thanks for your response, please see below:

[ghoshabhijeet]

@ehangreensSorry, 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 !

[ehangreens]

@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.

[ghoshabhijeet]

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

[ehangreens]

@ghoshabhijeet ,

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

[ghoshabhijeet · Accepted solution]

@ehangreens  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 👍

[ehangreens]

Thank you. This solutions works.

[AmyMisa]

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

[ehangreens]

Haven't had any issues with it so far

[ghoshabhijeet]

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

[Greg_Deckler]

@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".

[ghoshabhijeet]

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