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
ImkeF's avatar
ImkeF
Community Champion
8 years ago

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.