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

[v-heq-msft · 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?