Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Here is code that works fine in Power BI Desktop:
let
ListAllNICs= (SubscriptionId as text) =>
let
GetPages = (URL as text) =>
let
Source = Json.Document(Web.Contents(
URL
)),
LL= @Source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
URL = "https://management.azure.com/subscriptions/" & SubscriptionId & "/providers/Microsoft.Network/networkInterfaces?api-version=2018-11-01",
Fullset = GetPages(URL)
in
Fullset
in
ListAllNICs
But in Power BI Service appears error:
This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed
Probably, don't works this code:
Source[#"nextLink"]
Because this code works in Service:
GetDataBySubscription = Json.Document(Web.Contents(
"https://management.azure.com/subscriptions/",
[RelativePath= SubscriptionId &"/providers/Microsoft.Network/networkInterfaces?api-version=2018-11-01"])),
Fullset = GetDataBySubscription[value],
Please, advise, how to avoid error in Power BI Service and save paging with nextLink ?
Solved! Go to Solution.
This helped me
let
GetPages = (Path)=>
let
Host = "https://management.azure.com/subscriptions/",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = Path]
)),
LL= @Source[value],
Next = Text.Replace(Source[#"nextLink"], Host, ""),
result = try @ll & @GetPages(Next) otherwise @ll
in
result,
Fullset = GetPages(SubscriptionId&"/resources?api-version=2019-05-01"),
This helped me fixed my problems. You may want to refer this. https://tengkuma.vercel.app/blog/posts/conquering-urls-with-relativepath-and-query-in-power-bi
This is workable code in Power BI Desktop, that returns values, but not in Power BI Service because of dynamic source:
let
url = GetManagementURL(AzureKind)&"/subscriptions/"&subscriptionId&"/providers/microsoft.Security/tasks?api-version=2015-06-01-preview",
iterations = 10,
FuncGetOnePage = (url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[value] otherwise null,
next = try Source[nextLink] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedListOfPages = List.Generate(()=>[i=0, res = FuncGetOnePage(url)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FuncGetOnePage([res][Next])],
each [res][Data])
in
GeneratedListOfPages
How to inject code with RelativePath in code above?
let
GetPages = (Path)=>
let
Host = "https://management.azure.com/subscriptions/",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = Path]
)
)
I tried this code but returns null:
let
Host = "https://management.azure.com/subscriptions/",
Path = SubscriptionId & "/providers/microsoft.Security/alerts?api-version=2015-06-01-preview",
iterations = 10,
FuncGetOnePage = (Path) as record =>
let
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = Path]
)),
data = try Source[value] otherwise null,
next = try Source[nextLink] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedListOfPages = List.Generate(()=>[i=0, res = FuncGetOnePage(Path)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FuncGetOnePage([res][Next])],
each [res][Data])
in
GeneratedListOfPages
This helped me
let
GetPages = (Path)=>
let
Host = "https://management.azure.com/subscriptions/",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = Path]
)),
LL= @Source[value],
Next = Text.Replace(Source[#"nextLink"], Host, ""),
result = try @ll & @GetPages(Next) otherwise @ll
in
result,
Fullset = GetPages(SubscriptionId&"/resources?api-version=2019-05-01"),
Hi @googlogmobi - Thanks for posting this. I understand that it is 2 years old and it looks like you were trying to make use of the CCOE dashboards. Please let me know if you managed to enable auto refresh for them in Power BI service?
I am trying the same but no luck so far.
Hi, @googlogmobi
I'd like to suggest you refer to the following blogs about dynamic data sources.
Web.Contents(), M Functions And Dataset Refresh Errors In Power BI
Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@artemus how can I change code to get workable solution with paging on Power BI Service?
Any call to Web.Contents must use a constant string, or a reference to a parameter in the first parameter. This is why the RelativePath is used.