Forum Discussion

ghoshabhijeet's avatar
ghoshabhijeet
Solution Supplier
5 years ago
Solved

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 Que...
Need Help
Tips and Tricks
  • ghoshabhijeet's avatar
    ghoshabhijeet
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    ghoshabhijeet
    Solution Supplier
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your response, please see below:

       

      • ghoshabhijeet's avatar
        ghoshabhijeet
        Solution Supplier
        4 years ago

        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 !