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:
@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 👍
|
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Power BI Monthly Update - May 2024
Check out the May 2024 Power BI update to learn about new features.
Europe’s largest Microsoft Fabric Community Conference
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
