Forum Discussion

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

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"