cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amhof
Regular Visitor

JSON from REST API

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

1 ACCEPTED SOLUTION
amhof
Regular Visitor

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"

 

View solution in original post

5 REPLIES 5
v-deddai1-msft
Community Support
Community Support

Hi @amhof ,

 

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/604...

 

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
Super User III
Super User III

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" ?

amhof
Regular Visitor

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"

 

View solution in original post

lbendlin
Super User III
Super User III

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"

 

 

 

amhof
Regular Visitor

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors