Dynamic data source issue

[BI007]

Hello. I have this M script in Power Query, which is getting data using api (using pagination):

let

// Function to retrieve a page of data
GetPage = (url as text) =>
let
RawData = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & GetToken()]])),
Users = RawData[value],
NextLink = try RawData[#"@odata.nextLink"] otherwise null,
DataTable = if List.IsEmpty(Users) then null else Table.FromList(Users, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
[Table = DataTable, NextLink = NextLink],

// Initial
Initial = GetPage("https://graph.microsoft.com/v1.0/groups"),

// Generate a list to collect all pages
AllPages = List.Generate(
() => [Page = Initial, Link = Initial[NextLink]],
each [Link] <> null,
each [Page = GetPage([Link]), Link = [Page][NextLink]],
each [Page][Table]
),

CombinedData = Table.Combine(List.RemoveNulls(AllPages)),
#"Expanded Column1" = Table.ExpandRecordColumn(CombinedData, "Column1", {"id", "description"}, {"id", "description"})
in
#"Expanded Column1"

The problem is that, as "url" is passed as parameter, the data source becomes dynamic, so there is problem with refresh in service. How should it be fixed?
GetToken is separate function whick is working well.

I checked other links with solutions, but unable to fix script. It will be fine if smbd can fix / update this code provided.

Thanks in advance

[lucaspedrosa]

Eu preciso de ajuda! Estou tendo problema com esse erro: 

Este conjunto de dados inclui uma fonte de dados dinâmica. Como as fontes de dados dinâmicas não são atualizadas no serviço do Power BI, esse conjunto de dados não será atualizado.

• Fonte de dados para Query1

[BI007]

Hello,

You should use RelativePath and Query in Web.Content to specify the path of the URL.

If you still can't do it, write me the script which you have and I'll update you.


Best regards

[Anonymous · Accepted solution]

Hi @BI007 ,
Thanks for ZhangKun reply.
One way to do this is to make the URL parameters static in the query folding context

let
    GetPage = (url as text) =>
    let
        RawData = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & GetToken()]])),
        Users = RawData[value],
        NextLink = try RawData[#"@odata.nextLink"] otherwise null,
        DataTable = if List.IsEmpty(Users) then null else Table.FromList(Users, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        [Table = DataTable, NextLink = NextLink],
    InitialUrl = "https://graph.microsoft.com/v1.0/groups",
    Initial = GetPage(InitialUrl),
    AllPages = List.Generate(
        () => [Page = Initial, Link = Initial[NextLink]],
        each [Link] <> null,
        each [Page = GetPage([Link]), Link = [Page][NextLink]],
        each [Page][Table]
    ),

    CombinedData = Table.Combine(List.RemoveNulls(AllPages)),
    #"Expanded Column1" = Table.ExpandRecordColumn(CombinedData, "Column1", {"id", "description"}, {"id", "description"})
in
    #"Expanded Column1"

You can also use the RelativePath and Query options to build more flexible and maintainable URLs.

let
    GetPage = (relativePath as text, query as record) =>
    let
        RawData = Json.Document(Web.Contents("https://graph.microsoft.com", [RelativePath = relativePath, Query = query, Headers = [Authorization = "Bearer " & GetToken()]])),
        Users = RawData[value],
        NextLink = try RawData[#"@odata.nextLink"] otherwise null,
        DataTable = if List.IsEmpty(Users) then null else Table.FromList(Users, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        [Table = DataTable, NextLink = NextLink],
    InitialRelativePath = "v1.0/groups",
    InitialQuery = [],
    Initial = GetPage(InitialRelativePath, InitialQuery),
    AllPages = List.Generate(
        () => [Page = Initial, Link = Initial[NextLink]],
        each [Link] <> null,
        each [Page = GetPage(Text.Middle([Link], Text.Length("https://graph.microsoft.com/")), []), Link = [Page][NextLink]],
        each [Page][Table]
    ),

    CombinedData = Table.Combine(List.RemoveNulls(AllPages)),
    #"Expanded Column1" = Table.ExpandRecordColumn(CombinedData, "Column1", {"id", "description"}, {"id", "description"})
in
    #"Expanded Column1"

Data refresh in Power BI - Power BI | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[BI007]

Thank you, you helped me really very much.

[BI007]

in my case, first parameter is dynamic, not second of Web.Contents. 

Can you show me on the script provided what is the correct way?

[ZhangKun]

https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-source...

You should use RelativePath and Query in Web.Content to specify the path of the URL.

// https://abc.com/123?q=2
Web.Contents(
    "https://abc.com", 
    [
        Query = [
            q = "2"
        ], 
        RelativePath = "123", 
        Headers=[Authorization="Bearer " & GetToken()]
    ]
)

[BI007]

Any suggestion please? Can you update the script I have provided?