Forum Discussion
Error while schedule refresh - getting error " This dataset includes a dynamic data source. "
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 👍
Anonymous Can you please share the error message you get while refresh ?
Thanks for your response, please see below:
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 !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.
Anonymous You can upload in onedrive, google drive or dropbox and share the link.