0

I am trying to populate an Excel Workbook with data from a web source using their API. When I connect to the datasource, the advanced query editor appears and I am able to browse the data, it behaves sort of like an Access Database Application. When I select "Convert To Table" Excel basically places the top level of the datasource into the excel workbook and I cannot drill down into the records. It is a difficult one to explain.

Below is what is returned from the API call (this is a single record)

{"TotalResults":1,"ReturnedResults":1,"Results":[{"DueDate":"2018-10-31T00:00:00Z","FromDocument":"","AllowOnlinePayment":true,"Paid":false,"Status":"Unpaid","Locked":false,"CustomerId":3148838,"CustomerName":"Cell C Limited","Customer":{"Name":"Cell C Limited","SalesRepresentativeId":37307,"TaxReference":"4870194356","ContactName":"Charlene","Telephone":"0847770913","Fax":"","Mobile":"","Email":"csandstrom@cellc.co.za;accountspayable@cellc.co.za;EDC@cellc.co.za","WebAddress":"","Active":true,"IsObfuscated":false,"Balance":2333157.4800,"CreditLimit":0.0,"CommunicationMethod":2,"PostalAddress01":"Cell C ","PostalAddress02":"Waterfall Campus","PostalAddress03":"C/O Maxwell Drive and Pretoria Road","PostalAddress04":"Buccleuch","PostalAddress05":"","DeliveryAddress01":"September 2018 Print & Post Distribution","DeliveryAddress02":"","DeliveryAddress03":"","DeliveryAddress04":"","DeliveryAddress05":"PO: TBC","AutoAllocateToOldestInvoice":false,"EnableCustomerZone":false,"CustomerZoneGuid":"b9e1fbab-a23d-438c-b064-7a17d0a4dd56","CashSale":false,"TextField1":"","TextField2":"","TextField3":"","YesNoField1":false,"YesNoField2":false,"YesNoField3":false,"DefaultPriceListName":"Default Price List","AcceptsElectronicInvoices":false,"Modified":"2018-10-18T12:46:49.01","Created":"2018-10-18T12:42:31.233","HasActivity":true,"ID":3148838},"SalesRepresentativeId":37307,"SalesRepresentative":{"ID":37307,"FirstName":"David","LastName":"Markman","Name":"David Markman","Active":true,"Email":"davidm@infoslipsconnect.com","Mobile":"","Telephone":"","Created":"2018-10-18T12:42:31.233","Modified":"2018-10-18T12:46:49.01"},"Modified":"2018-10-18T12:46:49.01","Created":"2018-10-18T12:42:31.233","ID":483959431,"Date":"2018-10-18T00:00:00Z","Inclusive":false,"DiscountPercentage":0.0,"TaxReference":"4870194356","DocumentNumber":"INV03357","Reference":"14480 - October Print & Post","Message":"NEW Banking Details:\nAccount Name: Infoslips Connect (Pty) Ltd\nBank: Investec Bank Limited\nBranch: 100 Grayston Drive - 58 01 05\nAccount Number: 10011832402\nPlease forward Proof of Payment to accounts@infoslipsconnect.com\n","Discount":0.0000,"Exclusive":98243.0400,"Tax":14736.4600,"Rounding":0.0000,"Total":112979.5000,"AmountDue":112979.5000,"PostalAddress01":"Cell C ","PostalAddress02":"Waterfall Campus","PostalAddress03":"C/O Maxwell Drive and Pretoria Road","PostalAddress04":"Buccleuch","PostalAddress05":"","DeliveryAddress01":"September 2018 Print & Post Distribution","DeliveryAddress02":"","DeliveryAddress03":"","DeliveryAddress04":"","DeliveryAddress05":"PO: TBC","Printed":true,"Editable":true,"HasAttachments":false,"HasNotes":false,"HasAnticipatedDate":false,"Lines":[{"SelectionId":4804411,"TaxTypeId":2691481,"ID":328907379,"Description":"CELL C PRINT & POST ","LineType":0,"Quantity":1.0000,"UnitPriceExclusive":0.0000,"Unit":"","UnitPriceInclusive":0.0000,"TaxPercentage":0.15,"DiscountPercentage":0.0,"Exclusive":0.0000,"Discount":0.0000,"Tax":0.0000,"Total":0.0000,"Comments":"","AnalysisCategoryId1":16041,"UnitCost":0.0000},{"SelectionId":12926677,"TaxTypeId":2691481,"ID":328907380,"Description":"CellC: PDF Compiled","LineType":0,"Quantity":52926.0000,"UnitPriceExclusive":0.3400,"Unit":"","UnitPriceInclusive":0.3900,"TaxPercentage":0.15,"DiscountPercentage":0.0,"Exclusive":17994.8400,"Discount":0.0000,"Tax":2699.2300,"Total":20694.0700,"Comments":"","AnalysisCategoryId1":16041,"UnitCost":0.0000},{"SelectionId":12926854,"TaxTypeId":2691481,"ID":328907382,"Description":"CellC: Rounding Difference","LineType":0,"Quantity":1.0000,"UnitPriceExclusive":-238.1600,"Unit":"","UnitPriceInclusive":-273.8800,"TaxPercentage":0.15,"DiscountPercentage":0.0,"Exclusive":-238.1600,"Discount":0.0000,"Tax":-35.7200,"Total":-273.8800,"Comments":"","AnalysisCategoryId1":16041,"UnitCost":0.0000},{"SelectionId":12926687,"TaxTypeId":2691481,"ID":328907383,"Description":"CellC: PDF Print","LineType":0,"Quantity":109782.5000,"UnitPriceExclusive":0.7300,"Unit":"","UnitPriceInclusive":0.8300,"TaxPercentage":0.15,"DiscountPercentage":0.0,"Exclusive":80141.2300,"Discount":0.0000,"Tax":12021.1800,"Total":92162.4100,"Comments":"","AnalysisCategoryId1":16041,"UnitCost":0.0000},{"SelectionId":12926854,"TaxTypeId":2691481,"ID":328907384,"Description":"CellC: Rounding Difference","LineType":0,"Quantity":1.0000,"UnitPriceExclusive":-87.8200,"Unit":"","UnitPriceInclusive":-100.9900,"TaxPercentage":0.15,"DiscountPercentage":0.0,"Exclusive":-87.8200,"Discount":0.0000,"Tax":-13.1700,"Total":-100.9900,"Comments":"","AnalysisCategoryId1":16041,"UnitCost":0.0000},{"SelectionId":12926687,"TaxTypeId":2691481,"ID":328907385,"Description":"CellC: Hand Delivery","LineType":0,"Quantity":2.0000,"UnitPriceExclusive":0.3400,"Unit":"","UnitPriceInclusive":0.3900,"TaxPercentage":0.15,"DiscountPercentage":0.0,"Exclusive":0.6800,"Discount":0.0000,"Tax":0.1000,"Total":0.7800,"Comments":"","AnalysisCategoryId1":16041,"UnitCost":0.0000},{"SelectionId":12926854,"TaxTypeId":2691481,"ID":328907386,"Description":"CellC: Rounding Difference","LineType":0,"Quantity":1.0000,"UnitPriceExclusive":-0.0100,"Unit":"","UnitPriceInclusive":-0.0100,"TaxPercentage":0.15,"DiscountPercentage":0.0,"Exclusive":-0.0100,"Discount":0.0000,"Tax":0.0000,"Total":-0.0100,"Comments":"","AnalysisCategoryId1":16041,"UnitCost":0.0000},{"SelectionId":12926761,"TaxTypeId":2691481,"ID":328907387,"Description":"CellC: Nixie","LineType":0,"Quantity":1.0000,"UnitPriceExclusive":432.2800,"Unit":"","UnitPriceInclusive":497.1200,"TaxPercentage":0.15,"DiscountPercentage":0.0,"Exclusive":432.2800,"Discount":0.0000,"Tax":64.8400,"Total":497.1200,"Comments":"","AnalysisCategoryId1":16041,"UnitCost":0.0000}]}

My question is, I need to be able to reference the data returned by the query, but cant do so because the data doesn't seem to be there once I convert to table. What is the best way of achieving this?

Results in screenshots below:

enter image description here

enter image description here Not sure if I am making sense on this.

Thanks, Steven

Steven Nel
  • 69
  • 1
  • 14
  • 1) Can you share the M code behind this (obfuscate personal details)? 2) Does the API only return a single record? Or are you only sharing one as an example? 3) Are you open to VBA solution 4) What data do you want from the table ? Or do you just want the whole table? 5) Have you looked at the functionality PowerQuery has for working with JSON? 6) Is the API public and testable? – QHarr Oct 18 '18 at 14:01
  • Hi There. There is no code behind it that I have access to. The API is provided by a 3rd party. The call returns 100 records at a time. I am open to VBA. – Steven Nel Oct 18 '18 at 14:05
  • Doesn't the advanced query editor display the M code? – QHarr Oct 18 '18 at 14:07
  • Sorry, I am not familiar with M Code. Then I open the advanced query editor it displays the following: let Source = Json.Document(Web.Contents("https://accounting.sageone.co.za/api/2.0.0/TaxInvoice/get?apikey={xxxxxxxxxxxxxxxxxxx}&companyid=12345&includeDetail=true&includeCustomerDetails=true&$orderby=Created%20desc")), Results = Source[Results], Results1 = Results{0} in Results1 – Steven Nel Oct 18 '18 at 14:30
  • Delete this ^^ comment as it has your api key in. Or rather obfuscate the API key. – QHarr Oct 18 '18 at 14:30
  • Oops... edited the comment. Is this what you were referring to? – Steven Nel Oct 18 '18 at 14:35
  • Yup. That is what I was referring to. Can you include a screenshot of what the output looks like? – QHarr Oct 18 '18 at 14:36
  • Added the images to the origional question. First one is the initial result, I can then drill down into the results and each record. The second image one of the records. – Steven Nel Oct 18 '18 at 15:15
  • Again.... you might want to obfuscate sensitive information. Pen over it with MS Paint of some such if necessary. – QHarr Oct 18 '18 at 15:25
  • I'm not really good at this am I?? I have amended the image. – Steven Nel Oct 18 '18 at 15:30
  • lol.. it is a learning process :-) – QHarr Oct 18 '18 at 16:27

1 Answers1

1

I managed to get this right by watching few YouTube videos on M Code and and the Query Editor. All I needed to do was expand columns to get the data from the results into the same table. You certainly got me pointed in the right direction.

Steven Nel
  • 69
  • 1
  • 14