Contentstack API - dynamic data sources aren't refreshed in the Power BI service

[RicardoAGdSilva]

Good afternoon all,

I have been facing this issue for a few days, already check the forum and Chris Webb blogs and all the solutions provided didn't work. Soory for the post on this topic again but I can't find one related with Contentstack and everything I try didn't work.

Already try with RelativePath and Query on the link string.

The idea it's to call the Contentstack API to get the data, but since the limit it's 100 they tell us in they documentation to use the skips to get all the data we need.

The query we have being trying is this one: 

let

    Source = Json.Document(Web.Contents("https://eu-cdn.contentstack.com",

[RelativePath="/v3/content_types/TYPE_OF_CONTENT/entries?XXXXXXX=ABC&include_count=true",

Headers=[api_key="APIKEY", access_token="APITOKEN", #"Content-Type"="application/json"]])),

    count = Source[count],  // Assuming count is the column name in the Source

    // Calculate the total number of skips

    numberOfSkips = Number.RoundUp(count / 100) * 100,

    // Generate a list of skips

    skipsList = List.Generate(() => 0, each _ < numberOfSkips, each _ + 100),

    // Apply skips dynamically

    customQueries = List.Transform(skipsList, (skip) =>

        let

    data = Json.Document(Web.Contents("https://eu-cdn.contentstack.com//v3/content_types/TYPE_OF_CONTENT/entries?XXXXXXX=ABC&include_count=..."& Text.From(skip), [Headers=[api_key="APIKEY", access_token="APITOKEN", #"Content-Type"="application/json"]]))

        in

            data

    ),

    #"Converted to Table" = Table.FromList(customQueries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"entries", "count"}, {"Column1.entries", "Column1.count"}),

    #"Expanded Column1.entries" = Table.ExpandListColumn(#"Expanded Column1", "Column1.entries"),

    #"Expanded Column1.entries1" = Table.ExpandRecordColumn(#"Expanded Column1.entries", "Column1.entries", {"title", "value"}, {"Column1.entries.title", "Column1.entries.value"})

in

    #"Expanded Column1.entries1"

Thank you.

[RicardoAGdSilva]

When write the query like this I have the same issue and doesn't work: 

let
Source = Json.Document(Web.Contents("https://eu-cdn.contentstack.com",
[RelativePath="/v3/content_types/TYPE_OF_CONTENT/entries",
Query[XXXXXXX="ABC",include_count="true"],

But if I write the query with only one parameter it works: 

let
Source = Json.Document(Web.Contents("https://eu-cdn.contentstack.com",
[RelativePath="/v3/content_types/TYPE_OF_CONTENT/entries",
Query=[XXXXXXX="ABC"],

When way I can keep the query with two parameters?

[lbendlin]

check if your "true"  parameter value is case sensitive.

[RicardoAGdSilva]

I checked and if I change it, it gives an error.

This API as a limit of 100 per page but the link doesn't have the page number. I have been looking for other ways to go around this but can't find anything.

So my problem it's that if I Query=[XXXXXXX="ABC"], I get 100 data points, but I want all the others. If I  query this I can publish and PBI Service refresh without issues.

But we are using Query[XXXXXXX="ABC",include_count="true"], to get all the other pages together, but if I use this one PBI Service won't refresh the data.

Don't know if someone as a better aproach to this situation.

[lbendlin]

Already try with RelativePath and Query on the link string.

There is no "try".  You must use it.   Adjust your code accordingly