Forum Discussion

BI007's avatar
BI007
Helper I
1 year ago
Solved

Dynamic data source issue

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

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      BI007
      Helper I
      1 year ago

      Thank you, you helped me really very much.

    • BI007's avatar
      BI007
      Helper I
      1 year ago

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

  • BI007's avatar
    BI007
    Helper I
    1 year ago

    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?

  • lucaspedrosa's avatar
    lucaspedrosa
    New Member
    1 year ago

    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's avatar
      BI007
      Helper I
      1 year ago

      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