JSON from REST API

[Anonymous]

Hi,

I have a REST API that I am looking to import results from. I am struggling with two things.

1. Is there a way of letting users easily change the parameters, in this case start & end time, so that the query is updated accordingly? I'm thinking of a simple calendar that allows date selection, which are then used as parameters.

Method
GET: http://[SERVER]:8080/service/rest-api/results/query?[PARAMS] 
GET: https://[SERVER]:8443/service/rest-api/secure/results/query?[PARAMS]

Where params are:
start	REQUIRED: The start of the interval to query in the format yyyy-MM-ddTHH:mmZ.
end	REQUIRED: The end of the interval to query in the format yyyy-MM-ddTHH:mmZ.
types	REQUIRED: A comma separated list of types of results to query. Currently:
•	stoppage
•	runtime

Example URL
http://localhost:8080/service/rest-api/results/query?start=2020-05-06T12:00Z&end=2020-05-06T14:00Z&types=stoppage,runtime

2. The other issue is the way the data is returned and getting it into a table. I have tried different option via "Get Data - From Web" and depending on the selected delimiter end up with one or two columns, neither of which can be expanded or turned into a table.  Below is a sample of the nested results:

{
  "data":[{
    "type":"stoppage",
    "results":[ {
      "start":"2019-01-20T12:35:52.000+0000",
      "end":"2019-01-20T12:36:39.000+0000",
      "line":"71",
      "productCode":"123456",
      "batch":"9020T5006",
      "bulk":"72833351",
      "order":"711176321",
      "operator":"Caligula Muchanium",
      "primaryType":"machine",
      "primaryReasonText":"E/B Conv - EBS",
      "machineArea":"",
      "secondaryType":"setupMinor",
      "secondaryReasonText":"Btl jam/fall",
      "tertiaryType":"",
      "tertiaryReasonText":"",
      "part":"",
      "durationMillis":47000,
      "stopCount":1,
      "timeType":"real"
    },{
      "start":"2019-01-20T12:56:24.000+0000",
      "end":"2019-01-20T12:57:14.000+0000",
      "line":"73",
      "productCode":"7891011",
      "batch":"9020CB003",
      "bulk":"72833829",
      "order":"711176116",
      "operator":"Claudius Mageeium",
      "primaryType":"changeover",
      "primaryReasonText":"Changeover",
      "machineArea":"",
      "secondaryType":"changeover",
      "secondaryReasonText":"Div/Divless Change",
      "tertiaryType":"",
      "tertiaryReasonText":"",
      "part":"",
      "durationMillis":50000,
      "stopCount":1,
      "timeType":"real"
    }]
  },{
    "type":"runtime",
    "results":[{
      "start":"2019-01-20T11:04:21.000+0000",
      "end":"2019-01-20T12:35:52.000+0000",
      "line":"71",
      "productCode":"123456",
      "batch":"9020T5006",
      "bulk":"72833351",
      "order":"711176321",
      "operator":"Caligula Muchanium",
      "durationMillis":5491000
    },{
      "start":"2019-01-20T12:24:58.000+0000",
      "end":"2019-01-20T12:25:30.000+0000",
      "line":"73",
      "productCode":"7891011",
      "batch":"9020CB003",
      "bulk":"72833829",
      "order":"711176116",
      "operator":"Claudius Dium",
      "durationMillis":32000
   }]
  }]
}

Any help or pointers are greatly appreciated!

Many thanks

amhof

[v-deddai1-msft]

Hi @Anonymous ,

You should use Date.ToText to change the date type to text type. Please refer to the similiar post :https://community.powerbi.com/t5/Power-Query/Power-Query-Dynamic-DateTime-Parameters-in-JSON/m-p/604325

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[lbendlin]

Drop the CSV part.  Instead of 

let
    Source = Csv.Document(Web.Contents("http://localhost:8080/service/rest-api/results/query?start=2020-05-06T12:00Z&end=2020-05-06T14:00Z&types=stoppage,runtime"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),j=Json.Document(Source),

use

let
    Source = Json.Document(Web.Contents("http://localhost:8080/service/rest-api/results/query?start=2020-05-06T12:00Z&end=2020-05-06T14:00Z&types=stoppage,runtime")),

Does the expression error come with a button "Go to error" ?

[Anonymous · Accepted solution]

I ended up using Text.FromBinary instead of Json.Document in Source, as otherwise I got the error below:

Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type. Details: data=[List]

Is that acceptable? It works!

Or does that mean something's not setup right in the API?

let
        Source = Text.FromBinary(Web.Contents("http://localhost:8080/service/rest-api/results/query?start=2021-02-26T12:00Z&end=2024-03-01T14:00Z&types=stoppage,runtime")),
        j=Json.Document(Source),
        data = j[data],
        #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "results"}, {"type", "results"}),
        #"Expanded results" = Table.ExpandListColumn(#"Expanded Column1", "results"),
        #"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"start", "end", "line", "productCode", "batch", "bulk", "order", "operator", "primaryType", "primaryReasonText", "machineArea", "secondaryType", "secondaryReasonText", "tertiaryType", "tertiaryReasonText", "part", "durationMillis", "stopCount", "timeType"}, {"start", "end", "line", "productCode", "batch", "bulk", "order", "operator", "primaryType", "primaryReasonText", "machineArea", "secondaryType", "secondaryReasonText", "tertiaryType", "tertiaryReasonText", "part", "durationMillis", "stopCount", "timeType"})
in 
    #"Expanded results1"

[lbendlin]

1. no - users cannot change query parameters. You might be able to do something if your source supports direct query but that is highly unlikely for REST API sources.

2. Power Query has built-in JSON parsing. Where are you stuck?

let
    Source = "{
  ""data"":[{
    ""type"":""stoppage"",
    ""results"":[ {
      ""start"":""2019-01-20T12:35:52.000+0000"",
      ""end"":""2019-01-20T12:36:39.000+0000"",
      ""line"":""71"",
      ""productCode"":""123456"",
      ""batch"":""9020T5006"",
      ""bulk"":""72833351"",
      ""order"":""711176321"",
      ""operator"":""Caligula Muchanium"",
      ""primaryType"":""machine"",
      ""primaryReasonText"":""E/B Conv - EBS"",
      ""machineArea"":"""",
      ""secondaryType"":""setupMinor"",
      ""secondaryReasonText"":""Btl jam/fall"",
      ""tertiaryType"":"""",
      ""tertiaryReasonText"":"""",
      ""part"":"""",
      ""durationMillis"":47000,
      ""stopCount"":1,
      ""timeType"":""real""
    },{
      ""start"":""2019-01-20T12:56:24.000+0000"",
      ""end"":""2019-01-20T12:57:14.000+0000"",
      ""line"":""73"",
      ""productCode"":""7891011"",
      ""batch"":""9020CB003"",
      ""bulk"":""72833829"",
      ""order"":""711176116"",
      ""operator"":""Claudius Mageeium"",
      ""primaryType"":""changeover"",
      ""primaryReasonText"":""Changeover"",
      ""machineArea"":"""",
      ""secondaryType"":""changeover"",
      ""secondaryReasonText"":""Div/Divless Change"",
      ""tertiaryType"":"""",
      ""tertiaryReasonText"":"""",
      ""part"":"""",
      ""durationMillis"":50000,
      ""stopCount"":1,
      ""timeType"":""real""
    }]
  },{
    ""type"":""runtime"",
    ""results"":[{
      ""start"":""2019-01-20T11:04:21.000+0000"",
      ""end"":""2019-01-20T12:35:52.000+0000"",
      ""line"":""71"",
      ""productCode"":""123456"",
      ""batch"":""9020T5006"",
      ""bulk"":""72833351"",
      ""order"":""711176321"",
      ""operator"":""Caligula Muchanium"",
      ""durationMillis"":5491000
    },{
      ""start"":""2019-01-20T12:24:58.000+0000"",
      ""end"":""2019-01-20T12:25:30.000+0000"",
      ""line"":""73"",
      ""productCode"":""7891011"",
      ""batch"":""9020CB003"",
      ""bulk"":""72833829"",
      ""order"":""711176116"",
      ""operator"":""Claudius Dium"",
      ""durationMillis"":32000
   }]
  }]
}",
j=Json.Document(Source),
    data = j[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "results"}, {"type", "results"}),
    #"Expanded results" = Table.ExpandListColumn(#"Expanded Column1", "results"),
    #"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"start", "end", "line", "productCode", "batch", "bulk", "order", "operator", "primaryType", "primaryReasonText", "machineArea", "secondaryType", "secondaryReasonText", "tertiaryType", "tertiaryReasonText", "part", "durationMillis", "stopCount", "timeType"}, {"start", "end", "line", "productCode", "batch", "bulk", "order", "operator", "primaryType", "primaryReasonText", "machineArea", "secondaryType", "secondaryReasonText", "tertiaryType", "tertiaryReasonText", "part", "durationMillis", "stopCount", "timeType"})
in
    #"Expanded results1"

[Anonymous]

Hi, many thanks for your help!

---

2. Power Query has built-in JSON parsing. Where are you stuck?

It's primarily incompetence! So far, I've only used Power BI with direct database connections and simply took data from tables.

Using your example above with the sample data works great, now I am stuck making it work with a live query. I have used:

let
    Source = Csv.Document(Web.Contents("http://localhost:8080/service/rest-api/results/query?start=2020-05-06T12:00Z&end=2020-05-06T14:00Z&types=stoppage,runtime"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),j=Json.Document(Source),
        data = j[data],
        #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "results"}, {"type", "results"}),
        #"Expanded results" = Table.ExpandListColumn(#"Expanded Column1", "results"),
        #"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"start", "end", "line", "productCode", "batch", "bulk", "order", "operator", "primaryType", "primaryReasonText", "machineArea", "secondaryType", "secondaryReasonText", "tertiaryType", "tertiaryReasonText", "part", "durationMillis", "stopCount", "timeType"}, {"start", "end", "line", "productCode", "batch", "bulk", "order", "operator", "primaryType", "primaryReasonText", "machineArea", "secondaryType", "secondaryReasonText", "tertiaryType", "tertiaryReasonText", "part", "durationMillis", "stopCount", "timeType"})
in
    #"Expanded results1"

and get the following:

Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type.
Details:
    [Table]

Any idea what I am missing?