Forum Discussion
Error while schedule refresh - getting error " This dataset includes a dynamic data source. "
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:
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 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 👍
23 Replies
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".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/
ImkeF Thanks for sharing the links. The blogpost did help, though the way to put it in Power Query was a bit tricky.
Greg_Deckler Thanks for your reply and tagging ImkeF in the reply. The blogpost she shared did help.
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
TextFormlet
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)Anonymous Can you please share the error message you get while refresh ?
Hi ghoshabhijeet ,
I have faced same issue report working on PowerBi desktop but refresh datasource is failed. Can you please look into the query.
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 !
Dear ghoshabhijeet , Thank you so much for your reply, I will message you separately.
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"Thanks for your query. Please open a new thread with the pbix file attached and you can tag me on the post.
ghoshabhijeet i have created new thread, pls help me
