Get all datas from a Database using API

[antoinemicksls]

Hi,

I am new on Power BI. I am trying to get all datas from a database by a API.

To learn how we can get all data from an API I follow the following tutorial : Power BI - Tales From The Front - REST APIs - YouTube

I test It's working for me : 

let
    Source = Json.Document(Web.Contents("https://api.giphy.com/v1/gifs/search?api_key=jO78X8NofWbkPPHJnNCPZpOhsdCh9oNJ&q=hamburger&limit=25&offset=0&rating=g&lang=en")),
    pagination = Source[pagination],
    total_count = pagination[total_count],
    Custom1 = List.Numbers(0, total_count/50, 50),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://api.giphy.com/v1/gifs/search?api_key=jO78X8NofWbkPPHJnNCPZpOhsdCh9oNJ&q=hamburger&limit=25&offset=0&rating=g&lang=en"&[Column1]))),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandListColumn(#"Expanded Custom", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"title", "images"}, {"title", "images"}),
    #"Expanded images" = Table.ExpandRecordColumn(#"Expanded data1", "images", {"downsized"}, {"downsized"}),
    #"Expanded downsized" = Table.ExpandRecordColumn(#"Expanded images", "downsized", {"url"}, {"url"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded downsized",{{"title", type text}, {"url", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1"})
in
    #"Removed Columns"

For this example, when I connect the data source by API : 

> I get Data, Pagination and Meta :

 

But when I apply this kind of connection with an other website from an API with the bearer token I dont have the same value (data, pagination..) :

> I have :

==> records, offset, page_size..

So, I can't load more than 20 values on my datamodel.

> Have you got a solution to get pagination table (To make a loop by total count divided by 5...) ? Please

> Have you got an other solution to get all data from API ? Please

Thanks in advance,

Antoine MICK

[v-jingzhang]

Hi @antoinemicksls 

Every API is different so it's difficult to have a common solution. The second API didn't provide a total_count value so we couldn't divide total count by 5.

Do you have API documentation which describes how it deals with pagination? For example, some APIs have a format like https://xxxxxx/xxx/1, https://xxxxxx/xxx/2, the final number is the page number. 

Best Regards,
Community Support Team _ Jing