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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
googlogmobi
Frequent Visitor

Dynamic data sources aren't refreshed in the Power BI service. Paging URL request

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 ?

1 ACCEPTED SOLUTION
googlogmobi
Frequent Visitor

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"),

View solution in original post

8 REPLIES 8
chipui
New Member

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 

MauriceEurope12
New Member

Dear Support Community,
 
I would need some help because my data source does not refresh in the web-based version of Power BI. 
 
I tried following the video https://www.youtube.com/watch?v=sv_upbpq_Cw, however, for some reason the query does not deliver any response. Would you please be so kind and help me in this respect?
 
Code 1:
let
    Quelle = Json.Document(Web.Contents(
        location &
        //"1600+Amphitheatre+Parkway,+Mountain+View,+CA"&
        "&key=" & APIkey)),
 
 
Code 2:
let
    Quelle = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/distancematrix/json?key=XXXXXXXXXXXXXXXXXXX&origins="&Origins&"&destinations="&Destinations&"&mode=driving&language=de-DE")),
  
 
Thank you very much for your endeavours in advance!
googlogmobi
Frequent Visitor

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 

 

 

 

googlogmobi
Frequent Visitor

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.

v-alq-msft
Community Support
Community Support

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.

googlogmobi
Frequent Visitor

@artemus  how can I change code to get workable solution with paging on Power BI Service?

artemus
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors