Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

JSON de REST API

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

1 ACCEPTED 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"

View solution in original post

5 REPLIES 5
Syndicate_Admin
Administrator
Administrator

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

Syndicate_Admin
Administrator
Administrator

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"

Syndicate_Admin
Administrator
Administrator

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors