Forum Discussion
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/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.
Then this is your code:
let Source = Json.Document(File.Contents(...YourJsonFile...)), ordercount = Source[ordercount][orderList], orders = Table.FromRecords(ordercount[orders]) in orders
5 Replies
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.
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
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": "" } }Then this is your code:
let Source = Json.Document(File.Contents(...YourJsonFile...)), ordercount = Source[ordercount][orderList], orders = Table.FromRecords(ordercount[orders]) in orders
