把REST API的JSON数据导入EXCEL

//Excel loads JSON data from REST API

//Data >> Get Data >> From Web >> Advanced Editor

let

    url = "yourAPIaddress",

    //Assemble Headers

headers = [

#"appKey"="yourAppKey",

#"Content-Type"="application/json",

#"Cache-Control"="no-cache",

#"x-thingworx-session"="true",

#"Accept"="application/json"

],

    //Assemble Input data

input1name = Excel.CurrentWorkbook(){[Name="Input1"]}[Content], //Input1 is the named range for parameter1

input1value = input1name{0}[Column1],

input2name = Excel.CurrentWorkbook(){[Name="Input2"]}[Content], //Input2 is the named range for parameter2

input2value = input2name{0}[Column1],

postData = Json.FromValue([dataType=input1value, durationFilter=input2value]),

//postData = Json.FromValue([input1="A", input2=123]),

//Post data

    response = Web.Contents( //post data

        url,

        [

            Headers = headers,

            Content = postData

        ]

    ),

    //Get result data

jsonResult = Json.Document(response),

//Get actual data list

QueryResult= Table.FromRecords({jsonResult}, {"rows"}),

//Expand rows

    ExpandRows = Table.ExpandListColumn(QueryResult, "rows"),

//Convert rows into flat Table

    QueryResultExpanded = Table.ExpandRecordColumn(ExpandRows, "rows", 

{"dateType", "duration", "createdon"}, 

{"dateType", "duration", "createdon"}),

    //Convert data type

#"Changed Type" = Table.TransformColumnTypes(QueryResultExpanded,{{"dateType", type text}, {"duration", type number}, {"createdon", Int64.Type}}),

    //Convert Datetime column

#"Added Custom" = Table.AddColumn(#"Changed Type", "createdon2", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [createdon]/1000))

in

    #"Added Custom"