Forum Discussion
Multiple remote sources in single query - error handling at source
Hello All,
Hope you are well,
Was wondering if anybody can help resolve my issue in error handling at source level.
A bit of background;
I currently have a pagination function set up as described in the below blog: (Excellent post BTW)
https://www.daytodatastuff.co.uk/blog/creating-loops-in-m
By following this blogs tutorial I was able to create a solution to my issue described below (almost)
I am using the Brightpearl (ERP/CRM system) API with views to retrieve data on every sales order in the system.
In order to retrieve order information, you must specify a single order ID with this URL 'https://euw1.brightpearlconnect.com/public-api/1234/order-service/order/{Order ID}'.
To get a list of order IDs you must first specify the customer ID for the orders you want to list 'https://euw1.brightpearlconnect.com/public-api/1234/order-service/sales-order-search?customerId={Customer ID}'
To get a list of Customer IDs you can call the below URL. To which I needed to create a loop for as this only sends 200 results (company IDs) at a time
https://euw1.brightpearlconnect.com/public-api/Albagaialtd/contact-service/contact?firstResult=1
So I have a looped query to retrieve all customer IDs and another query to retrieve all the order IDs for each customer IDs. It is working perfectly in the power query preview however when I go to load to the table I get errors and the query to retrieve all order IDs for each customer ID does not complete.
After some investigation, I have found that the reason it is giving errors is because some company IDs have no order IDs stored against them where as a lot of them do. So when it loops through to retrieve the order ID from a company ID that has no order IDs the table is obviously empty but instead of ignoring and moving on to the next available company ID parameter it holts the whole query refresh and returns nothing in my table.
Was hoping someone could help provide a solution to this - Was thinking I could do something with the 'Try' function but I am unsure where to place it?
Thank you so much for your help in advance!
Much appreciated
Will
(Customer ID 484 holds no order IDs)
Thank you so much for your help - unfortunetly I didnt get chance to try your method before solving for myself.
Because it was the first time I was trying to load the data to a table in my workbook it was failing completion due to the IDs that hold no order records. To overcome this I just needed to load every row within power query first (Was around 10,000 rows so took a while scrolling to the bottom of the table to load more) then I just removed the errors from the column so when I went to load it to table it worked perfectly! 🙂
Thanks again,
Will
2 Replies
Thank you so much for your help - unfortunetly I didnt get chance to try your method before solving for myself.
Because it was the first time I was trying to load the data to a table in my workbook it was failing completion due to the IDs that hold no order records. To overcome this I just needed to load every row within power query first (Was around 10,000 rows so took a while scrolling to the bottom of the table to load more) then I just removed the errors from the column so when I went to load it to table it worked perfectly! 🙂
Thanks again,
Will
Did you expand the table column before retrieving order information? Assume you have expanded it and get a column with all order IDs (with null on empty table rows), you can modify the next step similar to below.
For example, if the next step is to add a custom column with a custom function which calls order information
= getOrderInfo([order id])You can use "try" like below
= try getOrderInfo([order id]) otherwise nullOr use "if... then.. else..." to deal with null differently
if [order id] = null then null else getOrderInfo([order id])Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
