Forum Discussion

sallywt's avatar
sallywt
Regular Visitor
8 years ago
Solved

JSON data from a web service call

I conntect to a D3 database using a restful web service.  This returns data in the following format and I want to use this data to provide a visual report.   { "ordercount": { "OUT": [[ "29/08...
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    8 years ago

    Then this is your code:

     

    let
    Source = Json.Document(File.Contents(...YourJsonFile...)),
    ordercount = Source[ordercount][orderList],
    orders = Table.FromRecords(ordercount[orders])
in
    orders
sallywt's avatar
sallywt
Regular Visitor
8 years ago

I did some more work on the web service and now I have a lot more meaningful JSON coming out like below

 

{
	"ordercount": {
	"orderList": {
		"orders": [
				{
					"orderdates": "31/08/2017",
					"headerscount": "6",
					"linescount": "6"
				},
				{

This makes it much easier to handle as it is more meaningful.
					"orderdates": "01/09/2017",
					"headerscount": "1",
					"linescount": "1"
				},
				{
					"orderdates": "04/09/2017",
					"headerscount": "3",
					"linescount": "4"
				}
		]

	},	
		"IDOUT": 			""
	}
}

 

 

ImkeF's avatar
ImkeF
Community Champion
8 years ago

Then this is your code:

 

let
    Source = Json.Document(File.Contents(...YourJsonFile...)),
    ordercount = Source[ordercount][orderList],
    orders = Table.FromRecords(ordercount[orders])
in
    orders
  • sallywt's avatar
    sallywt
    Regular Visitor
    8 years ago

    Having brough the data in through a web service call as the source connection and sorted out the format the data was coming in I have been able to convert it to a table and report on the data as I hoped I would.

     

    Thank you to everyone for your help it is much appreciated.