Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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
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" ?
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"
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"
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |