cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlanFoley
Frequent Visitor

Convert this code which is a Function into a self looping piece of code for batch

Hi All

Firstly let me say that I am very new to m code and have managed to create a function from examples on the internet - this works great but because it is a function I need to pass it a parameter to  execute - in this case I enter "0" and click Invoke

 

I would really appreciate some assitance in converting this code from a function into a "self looping" query so it can be executed in batch without the need of any manual intervention

 

Here is the code:

 

(optional nextPageKey as text, optional loop as number, optional data as list) =>

let
Source = Json.Document(Web.Contents("https://domain.com/api/v2",
[
RelativePath=if loop = 0
then "entities?entitySelector=type%28APPLICATION%29&from=now-8d&fields=%2BfirstSeenTms"
else "entities?nextPageKey=" & nextPageKey,
Headers=[Authorization="Api-Token xxxxx"]
]
)),
token = try Source[nextPageKey] otherwise null,
currentData = Source[entities],
//appendedData = List.Combine({data, currentData}),
appendedData =
if currentData is null and data is null then {}
else if data is null then List.Combine({{}, currentData})
else if data is null then List.Combine({data, {}})
else List.Combine({data, currentData}),
loopNum = loop + 1,
output =
if token is null
//or loopNum > 100
then appendedData
else @fxAPPLICATION (token, loopNum, appendedData)
in
output

 

Thank you

Regards

Alan

13 REPLIES 13
PhilipTreacy
Super User
Super User

Hi @AlanFoley 

 

Sorry, got waylaid by other things.

 

Download the latest example PBIX

 

I've looked at your code and the code I posted last week is still pretty much what I would use.  Even with your PBIX file I can't test it though because I have no access to the API.  So you will have to insert the API url API Token and and make some other modifications to suit your needs.

 

I've created a new query called Call API and I have tested that the List.Generate function in that works correctly, at least to loop through and create a list of consecutive numbers and then bail out at a predetermined condition.

 

So I'm satisfied that my code is working and hopefully when you modify it it will do what you need.

 

In terms of checking for nextpageKey, when the response is received from the API, what you get back is this

api-resp.png

 

which is a Record and you can see that nextPageKey is in it.  If there is no more data to retrieve, the API responds with a similar record, just that nextPageKey is missing.

 

You can check for nextPageKey by checking the API response like this

 

try #"1stAPICall_JSON"[nextPageKey] otherwise null

 

 

If nextPageKey exists this code retrieves it.  If it isn't there it gives you null.  I've incorporated this into my Call API query

 

nextPageKey = try Result[nextPageKey] otherwise null,

 

 

You've got a section of code that I mentioned last week that I don't know what it is for.  You're also testing something called data twice and doing different things for the same value of data.  That logic isn't right but I don't know what it is you are trying to do.

 

            appendedData =  if currentData is null and data is null then {} 
                            else if data is null then List.Combine({{}, currentData})
                            else if data is null then List.Combine({data, {}})
                            else List.Combine({data, currentData})

 

 

So the Call API query is using List.Generate a list of the results from the API calls.  You can see elements being added to this list in the line 

each [Result][entities])

 

I think with the appendedData and currentData variables you are trying to save the data from the API but I don't think these are needed.  All your results will be in the list that List.Generate creates.  You can modify the data from there.

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thanks Phil

Really apprecaited

I will review and make a go of it

Thanks a mil

AlanFoley
Frequent Visitor

Thanks @AlanFoley  I'll look at these tonight.

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Phil

Were you able to access the files

Thanks

Alan

PhilipTreacy
Super User
Super User

Hi @AlanFoley 

 

Download example PBIX file

 

You can loop using List.Generate, something like this

let
Source = List.Generate(()=> 

    [Result = Web.Contents("https://www.microsoft.com/en-us/microsoft-365/blog/excel/"), nextPageKey = 2],

    each [nextPageKey] <= 10,
    
    each [ Result = Web.Contents("https://www.microsoft.com/en-us/microsoft-365/blog/excel/",
                    [ RelativePath = Text.From(nextPageKey)]

            ),

            nextPageKey = [nextPageKey] + 1

        ],
    
    each [Result])
in
    Source

 

In your case it would look more like this

let
Source = List.Generate(()=> 

    [Result = "", appendedData = {}, nextPageKey = "", loop = 0],

    each [nextPageKey] <> null,
    
    each [ Result = Json.Document(Web.Contents("https://domain.com/api/v2",
                    [ RelativePath = if loop = 0    then "entities?entitySelector=type%28APPLICATION%29&from=now-8d&fields=%2BfirstSeenTms"
                                                    else "entities?nextPageKey=" & nextPageKey,
                      Headers=[Authorization="Api-Token xxxxx"]
                    ]
            )),

            loop = [loop] + 1,

            nextPageKey = try Result[nextPageKey] otherwise null,

            currentData = Result[entities],

            appendedData =  if currentData is null and data is null then {} 
                            else if data is null then List.Combine({{}, currentData})
                            else if data is null then List.Combine({data, {}})
                            else List.Combine({data, currentData})

        ],
    
    each [Result])

in
    Source

 

I have no way to test this for you as I don't know what website you are working with or have authorisation to it.  

 

Also, there are things like 'data' which aren't defined so I don't know what this is supposed to hold.  

 

In this section of code

multiif.png

 

you are testing 'data' twice to see if it is null but trying to do 2 different things so that isn't right 🙂

 

Hopefully this will get you started along the right path.  See if you can modify my code in the attached file to work for you and if you can't please post back with more info about what exactly you are doing and supply your PBIX file if you can.

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Phil

Have spent quite some time understanding the solution you provided ..I modified my code as per your notes - however no output is returned - the list is empty

I have included the "Query No Output.PNG" file to the file share showing the return "Empty List" as well as a screenshot of the code I am using - It could be something simple I am missing

Appreciate if you could have another look

Thanks

Alan

Just noticed the screenshot does not show the value of "in"

It is as below:

 

in
   Source

Hi Phil

In hindsight it may be better to post the actual requirment instead of trying to fix my dodgy code

As per your request I will attached the .pbix - I will also attach the actual returned data in .json format for each of the API calls 

This is the sequence of API calls (Domain name abd Api-Token modified)

1. API Call: https://domain.com/api/v2/entities?entitySelector=type%28APPLICATION%29&from=now-8d&fields=%2BfirstS...
On the first call there are a number of "Data Points" returned:
- totalCount - Number of records in response
- pageSize - Limit imposed per API call
- nextPageKey - Value to be passed to get the next payload
- entities - Actual data to be used

2. API Call: https://domain.com/api/v2/entities?nextPageKey=yyyy%22&Api-Token=xxx
On the second call we pass the "nextPageKey" parameter only and its value - the "totalCount", "pageSize" and "entities" "Data Points" are returned (Because this is the last response with data the "nextPageKey" value is actually ommitted so we can't check if nextPageKey=NULL or something similar

If the returning response had many rows there would be a unique nextPageKey for the next response

Much Appreciated

Thanks

Alan

 

I am not able to attach any files ..how do I attach files?

hi @AlanFoley 

 

You can use OneDrive, Dropbox or Google Drive to upload your files then link to them.

 

Regards

 

Phil

hi @AlanFoley 

 

You can use OneDrive, Dropbox or Google Drive to upload your files then link to them.

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Phil

Thank you for your detailed response and for taking the time to assist me

I will need some time to review and understand your code as well as retrofit your suggestions in my PowerBI desktop file

I will provide some feedback as soon as I can

Thanks again

Alan

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors