Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hola
Tengo una API de REST desde la que estoy buscando importar resultados. Estoy luchando con dos cosas.
1. ¿Hay alguna manera de permitir a los usuarios cambiar fácilmente los parámetros, en este caso la hora de inicio y finalización, para que la consulta se actualice en consecuencia? Estoy pensando en un calendario simple que permita la selección de fechas, que luego se utilizan como parámetros.
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. El otro problema es la forma en que se devuelven los datos y ponerlos en una tabla. He intentado diferentes opciones a través de "Obtener datos - De la Web" y dependiendo del delimitador seleccionado terminan con una o dos columnas, ninguna de las cuales se puede expandir o convertir en una tabla. A continuación se muestra una muestra de los resultados anidados:
{
"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
}]
}]
}
Cualquier ayuda o punteros son muy apreciados!
Muchas gracias
amhof
Solved! Go to Solution.
Terminé usando Text.FromBinary en lugar de Json.Document en Source, ya que de lo contrario recibí el siguiente error:
Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type. Details: data=[List]
¿Es eso aceptable? ¡Funciona!
¿O eso significa que algo no está configurado correctamente en la 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"
No @amhof,
Debe usar Date.ToText para cambiar el tipo de fecha a tipo de texto. Por favor refiérase a la publicación similar :https://community.powerbi.com/t5/Power-Query/Power-Query-Dynamic-DateTime-Parameters-in-JSON/m-p/604...
Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los demás miembros a encontrarla más rápidamente.
Saludos
Dedmon Dai
Suelte la parte CSV. En lugar de
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),
Uso
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")),
¿El error de expresión viene con un botón "Ir a error"?
Terminé usando Text.FromBinary en lugar de Json.Document en Source, ya que de lo contrario recibí el siguiente error:
Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type. Details: data=[List]
¿Es eso aceptable? ¡Funciona!
¿O eso significa que algo no está configurado correctamente en la 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 - los usuarios no pueden cambiar los parámetros de consulta. Es posible que pueda hacer algo si el origen admite consultas directas, pero es muy poco probable para los orígenes de la API de REST.
2. Power Query tiene análisis JSON integrado. ¿Dónde estás atascado?
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"
Hola, muchas gracias por su ayuda!
2. Power Query tiene análisis JSON integrado. ¿Dónde estás atascado?
¡Es principalmente incompetencia! Hasta ahora, solo he usado Power BI con conexiones directas a bases de datos y simplemente he tomado datos de tablas.
Usando su ejemplo anterior con los datos de ejemplo funciona muy bien, ahora estoy atascado haciendo que funcione con una consulta en vivo. He utilizado:
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"
y obtenga lo siguiente:
Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type.
Details:
[Table]
¿Alguna idea de lo que me estoy perdiendo?