Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
Solved! Go to 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/
Imke Feldmann (The BIccountant)
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. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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:
Good Luck 👍
|
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.
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.
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)
@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.
@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:
Good Luck 👍
|
Thank you. This solutions works.
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
Haven't had any issues with it so far
@AnonymousIf my solution works, please mark my post as a solution, so that it can help others. Thanks !
@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".
@Greg_Deckler Thanks for your reply and tagging @ImkeF in the reply. The blogpost she shared did help.