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.