cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ImkeF
MVP

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

I cannot see why this is could be the reason.

Coming back ot my previous post, I'd like to see what is actually returned (on each level, if a nested object is returned).

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

@ImkeF 

 

Okay, so the GeneratedList Step looks as follows:

14-10-2019 11-31-24.jpg

When expanding the list to see the full records, it looks like this:
14-10-2019 11-34-26.jpg
And then when I filter for one ID, what I get is the same ID 237 times (as the counter is set to stop at 237):
14-10-2019 11-36-38.jpg
And this is the full code (up until the step when I filter for ID):

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    #"Expanded res" = Table.ExpandRecordColumn(#"Expanded Column1", "res", {"Data"}, {"res.Data"}),
    #"Expanded res.Data" = Table.ExpandListColumn(#"Expanded res", "res.Data"),
    #"Expanded res.Data1" = Table.ExpandRecordColumn(#"Expanded res.Data", "res.Data", {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"}, {"Column1.res.Data.url", "Column1.res.Data.id", "Column1.res.Data.external_id", "Column1.res.Data.via", "Column1.res.Data.created_at", "Column1.res.Data.updated_at", "Column1.res.Data.type", "Column1.res.Data.subject", "Column1.res.Data.raw_subject", "Column1.res.Data.description", "Column1.res.Data.priority", "Column1.res.Data.status", "Column1.res.Data.recipient", "Column1.res.Data.requester_id", "Column1.res.Data.submitter_id", "Column1.res.Data.assignee_id", "Column1.res.Data.organization_id", "Column1.res.Data.group_id", "Column1.res.Data.collaborator_ids", "Column1.res.Data.follower_ids", "Column1.res.Data.email_cc_ids", "Column1.res.Data.forum_topic_id", "Column1.res.Data.problem_id", "Column1.res.Data.has_incidents", "Column1.res.Data.is_public", "Column1.res.Data.due_at", "Column1.res.Data.tags", "Column1.res.Data.custom_fields", "Column1.res.Data.satisfaction_rating", "Column1.res.Data.sharing_agreement_ids", "Column1.res.Data.fields", "Column1.res.Data.followup_ids", "Column1.res.Data.brand_id", "Column1.res.Data.allow_channelback", "Column1.res.Data.allow_attachments"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded res.Data1", each ([Column1.res.Data.id] = 103733)),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Column1.res.Data.id] = 103733))
in
    #"Filtered Rows"


I seriously lost here... much appreciate your support here!

 

Thanks again!

ImkeF
MVP

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

thanks @airfreighter  

but I need to see the following steps:

  •  Expanded Column1
  •  Expanded res
  •  Expanded res.Data

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

@ImkeF Alright here you go:

Expanded Column1
Expanded Column 1.jpg

Expanded res
Expanded Res.jpg

Expanded res.Data

res.Data.jpg

Thanks!!

ImkeF
MVP

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

Thanks, but I still need more, unfortunately: What does this return?:

 

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    Sample =  #"Expanded Column1" {230}[res]
in
Sample

 

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

@ImkeF Thanks for the prompt reply, sure, Here you go:

 

This is the step "Example"

sample.jpg
expanded.jpgAnd this is what happens when I work my way thorugh.

ImkeF
MVP

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

Hi @airfreighter ,

you're not using the function parameter in the fnGetOnePage-function.

 

Change step Source like so:

 

Source = url,

 

instead of: Source = Source,

(so it just calls the first query over and over again..)

 

... I know, it hurts 😉

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

Hi @ImkeF 

Really? that can't be. Because when I change it in the code to this:

FnGetOnePage =
  (url) as record =>
   let
    Source= url,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1])

in
    GeneratedList

It just returns an empty list in the step GeneratedList:

 

LIst.jpgIf I omit the definition of Source in the FnGetOnePage step entirely, what I get is the same result as before with the loop being stuck on one page. And really, since the source does not change for the FnGetOnePage, is it needed to define this parameter?
It should always start on the /MYURL/tickets.json URL, but grab data (the current page, tickets) and next (the NextPage) depending on the return of "NextPage".

Could it be that we need to overwrite the url in each iteration to grab the latest url from the latest step? Right now it is pointing to page 2 over and over again, right? so it's stuck on the next_page reference for page one (the MYURL/tickets.json default response) for every step in the loop...
So I think the problem might be here:

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1])

But I don't have enough experience with M to really see what's going on...

 

Again, I think I am very close but I just don't seem to get it right...:)

 

ImkeF
MVP

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

you're right - the url need to be called somewhere as well 🙂

so how does this work?:

 

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    Sample =  #"Expanded Column1" {230}[res]
in
   Sample

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

@ImkeF Success!! Finally, it worked. So here's the final overall code, including the steps to get to the final full table:

 

let
 Source = Json.Document(Web.Contents("MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "MYURL/api/v2/tickets.json", // I am using basic authentication
 
  FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res"}, {"res"}),
    #"Expanded res" = Table.ExpandRecordColumn(#"Expanded Column1", "res", {"Data", "Next"}, {"Data", "Next"}),
    #"Expanded Data" = Table.ExpandListColumn(#"Expanded res", "Data"),
    #"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"}, {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"})

in
    #"Expanded Data1"

Thanks ever so much for your help!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (954)