Handle pagination in Zendesk Basic Auth

[Anonymous]

Dears, 

I need your help in this Power Query, I already connected to MY Zendesk API and fetch the data from their and as you know it only retrieve one page at a time, so i'm trying to loop over all pages and export data from their. but i got an error: "Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer" 

which is related to the access user/password "authontication" the code not able to process and grant them to proccess. 

this is my code:

let 
    BaseUrl         = "https://yourdomain.zendesk.com/api/v2/tickets?",
	User		="yourusername",
	Password		="yourpassword",
    EntitiesPerPage = 100,
 
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Basic " & User &":" & Password ]],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "$count=true&$top=0",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

could you please help in this, i'm not that expert in the M query. 

the target is to loop over all pages to get the data. 

Thank you so much. 

 

@v-shex-msft 

@v-chuncz-msft 

@pdbenbow 

 

[Anonymous]

For those interested I have updated and shared the code from this post here:
https://community.powerbi.com/t5/Desktop/Custom-Zendesk-Connector-Power-Query-Code/td-p/2407620

[Fowmy]

@Anonymous 

Refer to this video from Miguel Escobar
You can adapt it to work for your scenario

https://www.youtube.com/watch?v=vhr4w5G8bRA

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy thank you so much for your reply, I already went through Matt solution and it is really useful. 

But what i'm facing now that as you see in the attachment "(step 'Pages') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination."  when i checked the load steps all the pages retrieve it is about 92 pages "URL" in my case now, but it stopped on this step. 

and on the last step for the TableFormList it gives this error and i think it is because the previous error: "Expression.Error: The import Source matches no exports. Did you miss a module reference?"

Any Idea about this, please? 

[pdbenbow]

---

@Anonymous wrote:

But what i'm facing now that as you see in the attachment "(step 'Pages') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination."  when i checked the load steps all the pages retrieve it is about 92 pages "URL" in my case now, but it stopped on this step. 

---

 @Anonymous , check out this article: https://www.poweredsolutions.co/2019/03/12/data-privacy-and-the-formula-firewall/

From the article:

"In short, what’s happening is that within the same query, you’re trying to access multiple data sources and they’ve been set up with incompatible privacy levels, meaning that you need to head over to the Data Source Settings window and set up your ALL your data sources in that query to the same privacy level; either ALL Public or ALL Organizational."

Recommend you change all your data sources to "Organizational" privacy level.

[Anonymous]

Thank you so much, I have checked them all, and all the same. I will rewrite the code and load them in batches and see.