M Code to paginate not working in Service

Power Query
1

Hi

I have M Code that enables me to refresh data in a Power BI desktop report via an API from a CRM.

However, as soon as I publish the report on Service, I get the message of ‘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.’

Just wondering if anyone can see what the issue is in the code below. (And yes, I have tried Chat GPT.)

The CRM allows only 10,000 rows of data through the API at a time so the M code involves paginating which could be the source of the problem.

let
token_path = “/token”,
client_id = “XXXXXX”,
client_secret = “XXXXXX”,
authKey = "Basic " & Binary.ToText(Text.ToBinary(client_id & “:” & client_secret),0),
scope = “exports”,

Token_Response = Json.Document(Web.Contents(“https://xxx.xxxxx.io”,
[
RelativePath = “/token”,
Headers = [
#“Authorization”=authKey,
#“Content-Type”=“application/x-www-form-urlencoded;charset=UTF-8”],
Content = Text.ToBinary(“grant_type=client_credentials”)
]
)
),

token = Token_Response[access_token],

iterations = 5, // Number of iterations
url = “https://xxx.xxxxx.io/api/exports/pbi-tm-reactivations-3?pageSize=10000”,

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(
Web.Contents(
url,
[
Headers=[
#“Authorization” = "Bearer " & token,
#“content-type” = “application/json”
]
]
)
),
data = try Source[result] otherwise null,
nextCursor = try Source[metadata][nextCursor] otherwise null,
resultUid = try Source[metadata][resultUid] otherwise null,
next = “https://xxx.xxxxx.io/api/exports/pbi-tm-reactivations-3?pageSize=10000&cursor=” & nextCursor & “&resultUid=” & resultUid,
res = [Data=data, Next=next]
in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data]),
#“Converted to Table” = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Expanded Column1” = Table.ExpandListColumn(#“Converted to Table”, “Column1”)
in
#“Expanded Column1”

Any suggestions would be much apprecitated.

2

Hi @KieftyKids ,

To resolve the dynamic data source issue in Power BI Service, you can try the following:

  1. Parameterize URLs:
  • Replace dynamic URLs with parameters and then use these parameters in your M code.
  1. Use Dataflows:
  • Move your data extraction logic to Power BI Dataflows, which can handle dynamic sources more effectively.
  1. Custom Connector:
  • Develop a custom connector to manage the pagination and token handling within a static data source context.

M Code Example: Instead of dynamically generating URLs, you can parameterize the cursor and resultUid:

mCopy codelet
token_path = “/token”,
client_id = “XXXXXX”,
client_secret = “XXXXXX”,
authKey = "Basic " & Binary.ToText(Text.ToBinary(client_id & “:” & client_secret),0),
scope = “exports”,

Token_Response = Json.Document(Web.Contents("https://xxx.xxxxx.io",
[
    RelativePath = "/token",
    Headers = [
        #"Authorization"=authKey,
        #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
    Content = Text.ToBinary("grant_type=client_credentials")
]
)),

token = Token_Response[access_token],

iterations = 5, // Number of iterations
base_url = "https://xxx.xxxxx.io/api/exports/pbi-tm-reactivations-3?pageSize=10000",

GetPages = (url as text, token as text) =>
let
    Source = Json.Document(
        Web.Contents(url, [Headers=[Authorization="Bearer " & token, #"content-type"="application/json"]])
    ),
    data = try Source[result] otherwise null,
    nextCursor = try Source[metadata][nextCursor] otherwise null,
    resultUid = try Source[metadata][resultUid] otherwise null,
    next = if nextCursor <> null and resultUid <> null then base_url & "&cursor=" & nextCursor & "&resultUid=" & resultUid else null,
    result = [Data=data, Next=next]
in
    result,

InitialPage = GetPages(base_url, token),
Pages = List.Generate(
    () => [Page = InitialPage, Counter = 0],
    each [Page][Data] <> null and [Counter] < iterations,
    each [Page = GetPages([Page][Next], token), Counter = [Counter]+1],
    each [Page][Data]
),

#"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1")

in
#“Expanded Column1”

For more advanced guidance, refer to the Data Mentor platform to post specific queries.

Cheers,

Enterprise DNA Support Team