Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ghoshabhijeet
Solution Supplier
Solution Supplier

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 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:

ghoshabhijeet_0-1598460750826.png

 

2 ACCEPTED SOLUTIONS

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

View solution in original post

@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:

 

ghoshabhijeet_1-1648158802082.png

 


** 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 👍

 

 

View solution in original post

23 REPLIES 23

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

Facing same DATASOURCE REFRESH ISSUE issue, but the above link is not working.

hachishti_0-1661623525087.png

 

 

@ImkeF  Thanks for sharing the links. The blogpost did help, though the way to put it in Power Query was a bit tricky.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.