JSON data from a web service call

[sallywt]

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/2017",			"31/08/2017",			"01/09/2017",			"04/09/2017"
			],[			"6",			"6",			"1",			"3"
			],[			"15",			"6",			"1",			"4"
			]
			],	
		"IDOUT": 			""
	}
}

Can anyone advise how I can do this?  I can see the data but I'm not sure how all the fields can be included in a report.

[v-shex-msft]

HI @sallywt,

ImkeF's solution seems well, I think you can also take a look at below article about how to get data from web api and format json data.
Can you use Power BI to call REST APIs and parse JSON?

Regards,

Xiaoxin Sheng

[sallywt]

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 · Accepted solution]

Then this is your code:

let
    Source = Json.Document(File.Contents(...YourJsonFile...)),
    ordercount = Source[ordercount][orderList],
    orders = Table.FromRecords(ordercount[orders])
in
    orders

[sallywt]

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. 

[ImkeF]

You need to transform this into a table with a date-column:

let
    Source = Json.Document(File.Contents("...PathToYourJson...")),
    OUT = Source[ordercount][OUT],
    ToTable = Table.FromColumns(OUT, {"Date", "Col1", "Col2"}),
    #"Changed Type" = Table.TransformColumnTypes(ToTable,{{"Date", type date}, {"Col1", type number}, {"Col2", type number}})
in
    #"Changed Type"

 You might want to adjust the names of the "value-columns" in step "ToTable" to meaningful names.