Recolección de Respuestas de Forms con Power Query -

Recopilación de Respuestas de Forms con Power Query

Read Time:13 Minute, 21 Second

En el artículo anterior, Clonación y Edición de Forms con Power Automate, exploramos cómo utilizar Microsoft Forms junto con Power Apps y Dataverse para recopilar información de un grupo amplio de personas. Utilizamos Power Automate y la API de Microsoft Forms para crear formularios de confirmación de asistencia personalizados para cada evento, ofreciendo una mayor flexibilidad y personalización.

En esta segunda parte de la serie, nos adentraremos en cómo recoger preguntas y respuestas a través de Power Query. Este enfoque nos permitirá analizar y gestionar los datos recopilados de manera más eficiente, integrando las respuestas en nuestras bases de datos y facilitando la toma de decisiones basadas en datos precisos.

El proceso de recogida de respuestas lo realizaremos a través de un dataflow de Power Apps para importar las preguntas y respuestas de cada uno de los formularios asociados a los eventos. Este método es esencial para trabajar con las respuestas dentro de nuestra aplicación de gestión de eventos, permitiéndonos mantener un control detallado y actualizado de la información.

Por ejemplo, si realizáramos este mismo proceso en Power Query pero para Power BI, podríamos mostrar los datos de una forma más gráfica a través de informes, sin necesidad de pasar por Dataverse u otro tipo de base de datos. Este proceso de recolectar las preguntas y respuestas con Power Query abre un abanico de posibilidades de uso e integración, desde la visualización de datos hasta la integración en aplicaciones empresariales.

Definición del Esquema de Tablas en Dataverse

Antes de diseñar el flujo de datos, es crucial establecer el esquema de la base de datos relacional donde se almacenará la información de cada formulario. A continuación, se presenta el esquema diseñado.

Aclaración: El campo <<Parent>> de la tabla <<Survey Question>> se refiere a la misma tabla con una relación jerárquica, ya que las preguntas de tipo <<Likert>> en Microsoft Forms tienen esta estructura.

Esquema de Tablas y Claves Externas

Una vez creado el esquema de tablas, es necesario definir las siguientes <<External Key>> en cada una de ellas. Esto facilitará la referencia a los registros de tipo >»><<hacer referencias a los registros de tipo Lookup>> al configurar el mapeo de carga de datos.

Survey: <<Form Id>>
Survey Question: <<Form Id>> y <<Question Id>>
Survey Response: <<Form Id>> y <<Response Id>>
Survey Question Response: <<Form Id>>, <<Question Id>> y <<Response Id>>

Ejemplo de Creación de una External Key en Dataverse

A continuación, se muestra un ejemplo de cómo crear una <<External Key>> en una tabla de Dataverse, específicamente para la tabla <<Survey Question Response>>.

Dataflow de Power Apps

Para recolectar las preguntas y respuestas de Microsoft Forms, utilizaremos llamadas HTTP a la API de Microsoft Forms. Antes de cargar la información en Dataverse, realizaremos un proceso de transformación y tratamiento de datos. Dado que algunas preguntas requieren transformaciones específicas, analizaremos en profundidad la estructura de los JSON obtenidos.

Para simplificar el proceso de explicación, mostraré como recoger la información de un único formulario. Utilizaremos el formulario creado para el evento: Sydney AI Workshop. De aquí tomaremos el valor del campo <<Form Id>>.

Paso a paso

A continuación muestro las consultas que debemos de crear.

#1: Creación de parámetros

Una buena práctica al desarrollar procesos ETL en Power Query es el uso de parámetros para almacenar URLs o identificadores de los orígenes de datos a los que nos conectamos. En este caso, vamos a crear tres parámetros:

TenantId: Almacena el ID de nuestro tenant.
GroupId: Almacena el ID del grupo donde se están creando los formularios de Microsoft Forms.
FormId: ID del formulario de Microsoft Forms que queremos recolectar preguntas y respuestas.

#2: Clasificación de preguntas

Dentro de Microsoft Forms, las preguntas se clasifican según una tipología. Para facilitar la clasificación dentro de nuestro sistema, en la tabla de <<Questions>>, la columna <<Question Type>> es de tipo choice. Por ello, añado una consulta donde incluyo el listado de los tipos de preguntas disponibles en Microsoft Forms y los valores de choices en Dataverse para facilitar la carga de datos.

Descarga el listado de tipo de preguntas de Microsoft Forms en el siguiente enlace.

#3: Recolección de preguntas

Este script en lenguaje M se utiliza para obtener y procesar las preguntas de un formulario de Microsoft Forms. A continuación, se detallan las acciones realizadas en cada paso del script:

Definición de la fuente

En mi caso, como hemos seleccionado un formulario específico, inicio la consulta a partir de un parámetro. Sin embargo, en vuestro caso, tendréis que conectaros a la tabla donde tengáis guardados todos los formularios.

let
  Source = FormId,
  #"Converted to table" = #table(1, {{Source}}),
  #"Renamed columns" = Table.RenameColumns(#"Converted to table", {{"Column1", "Form Id"}}),

Source: Se define la variable Source que contiene el FormId del formulario.
Conversión a Tabla: Se convierte el FormId en una tabla con una sola columna.
Renombrado de Columnas: Se renombra la columna resultante a “Form Id”.

Obtener Preguntas

  #"Get questions" = Table.AddColumn(#"Renamed columns", "Questions", each Json.Document(Web.Contents("https://forms.office.com/formapi/api/"&TenantId&"/groups/"&GroupId&"/forms('"&[Form Id]&"')/questions"))),
  #"Expanded Questions" = Table.ExpandRecordColumn(#"Get questions", "Questions", {"value"}, {"value"}),
  #"Expanded value" = Table.ExpandListColumn(#"Expanded Questions", "value"),
  #"Expanded value 1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id", "title", "order", "type", "groupId"}, {"Question Id", "Title", "Order", "Question Type Name", "Parent Question Id"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded value 1", {{"Form Id", type text}, {"Question Id", type text}, {"Title", type text}, {"Order", type number}, {"Question Type Name", type text}, {"Parent Question Id", type text}}),

Obtener Preguntas: Se añade una columna “Questions” que contiene las preguntas del formulario obtenidas mediante una llamada a la API de Microsoft Forms.
Expansión de Preguntas: Se expande la columna “Questions” para obtener los valores de las preguntas.
Expansión de Valores: Se expande la lista de preguntas.
Expansión de Detalles de Preguntas: Se expande cada registro de pregunta para obtener detalles como id, title, order, type, y groupId.
Cambio de Tipo de Columna: Se transforman los tipos de las columnas para asegurar que los datos sean tratados correctamente.

La sintaxis de la llamada a la API de Microsoft Forms para obtener las preguntas sería:

https://forms.office.com/formapi/api/[Tenant ID]/groups/[Group ID]/forms('[FORM ID]')/questions

Unión con Tipos de Pregunta

  #"Merged with question type choice" = Table.NestedJoin(#"Changed column type", {"Question Type Name"}, #"Question Type Choice", {"Name"}, "Question Type Choice", JoinKind.Inner),
  #"Expanded Question Type Choice" = Table.ExpandTableColumn(#"Merged with question type choice", "Question Type Choice", {"Value"}, {"Question Type"}),

Unión con Tipos de Pregunta: Se realiza una unión interna con otra tabla (Question Type Choice) basada en el nombre del tipo de pregunta.
Expansión de Tipos de Pregunta: Se expande la columna resultante para incluir los valores de los tipos de pregunta.

Ordenación de Filas

Debemos realizar esta ordenación debido a que hay ciertos tipos de preguntas que tienen preguntas padres. Con esta ordenación, logramos que primero se carguen las preguntas padres y luego las preguntas hijas.

  #"Sorted rows" = Table.Sort(#"Expanded Question Type Choice", {{"Parent Question Id", Order.Ascending}}),

Añadir columna con el ID de la pregunta padre

Se añade una columna llamada “Form Id (Parent Question)” que contiene el Form Id si la pregunta tiene un Parent Question Id.

#"Add column with question id for parent" = Table.TransformColumnTypes(Table.AddColumn(#"Sorted rows", "Form Id (Parent Question)", each if [Parent Question Id] = null then null else [Form Id]),{{"Form Id (Parent Question)", type text}})
in
  #"Add column with question id for parent"

#4: Recolección de respuestas (cabecera)

La recolección de respuestas se realiza en dos fases. Primero, se obtienen las respuestas proporcionadas por cada usuario, que actuarán como la cabecera. Luego, para cada respuesta de usuario, se recopilan las respuestas correspondientes a cada pregunta generada en el formulario. En nuestro caso, el formulario contiene dos respuestas. ¡Vamos a ello!

Definición de la fuente

let
  Source = FormId,
#"Converted to table" = #table(1, {{Source}}),
#"Renamed columns" = Table.RenameColumns(#"Converted to table", {{"Column1", "Form Id"}}),

Source: Se define la variable Source que contiene el FormId del formulario.
Conversión a Tabla: Se convierte el FormId en una tabla con una sola columna.
Renombrado de Columnas: Se renombra la columna resultante a “Form Id”.

Obtener Respuestas

#"Get Responses" = Table.AddColumn(#"Renamed columns", "Responses", each Json.Document(Web.Contents("https://forms.office.com/formapi/api/"&TenantId&"/groups/"&GroupId&"/forms('"&[Form Id]&"')/responses"))),
#"Expanded Responses" = Table.ExpandRecordColumn(#"Get Responses", "Responses", {"value"}, {"value"}),
#"Expanded value" = Table.ExpandListColumn(#"Expanded Responses", "value"),
#"Expanded value 1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id", "startDate", "submitDate", "responder", "responderName"}, {"Response Id", "Start Date", "Response Date", "Responder", "Responder Name"}),
#"Changed column type" = Table.TransformColumnTypes(#"Expanded value 1", {{"Form Id", type text}, {"Response Id", type text}, {"Start Date", type datetime}, {"Response Date", type datetime}, {"Responder", type text}, {"Responder Name", type text}})
in
  #"Changed column type"

Obtener Respuestas: Se añade una columna “Responses” que contiene las respuestas del formulario obtenidas mediante una llamada a la API de Microsoft Forms.
Expansión de Preguntas: Se expande la columna “Responses” para obtener los valores de las respuestas.
Expansión de Valores: Se expande la lista de respuestas.
Expansión de Detalles de Respuestas: Se expande cada registro de respuesta para obtener detalles como id, startDate, submitDate, responder, y responderName.
Cambio de Tipo de Columna: Se transforman los tipos de las columnas para asegurar que los datos sean tratados correctamente.

La sintaxis de la llamada a la API de Microsoft Forms para obtener las respuestas sería:

https://forms.office.com/formapi/api/[Tenant ID]/groups/[Group ID]/forms('[FORM ID]')/responses

#5: Recolección de respuestas (detalle)

Definición de la fuente

let
  Source = FormId,
#"Converted to table" = #table(1, {{Source}}),
#"Renamed columns" = Table.RenameColumns(#"Converted to table", {{"Column1", "Form Id"}}),

Source: Se define la variable Source que contiene el FormId del formulario.
Conversión a Tabla: Se convierte el FormId en una tabla con una sola columna.
Renombrado de Columnas: Se renombra la columna resultante a “Form Id”.

La sintaxis de la llamada a la API de Microsoft Forms para obtener las respuestas sería la misma. Lo que varia es a la hora de seleccionar los atributos en el JSON resultante.

https://forms.office.com/formapi/api/[Tenant ID]/groups/[Group ID]/forms('[FORM ID]')/responses

Obtener Respuestas

  #"Get Responses" = Table.AddColumn(#"Renamed columns", "Responses", each Json.Document(Web.Contents("https://forms.office.com/formapi/api/"&TenantId&"/groups/"&GroupId&"/forms('"&[Form Id]&"')/responses"))),
  #"Expanded Responses" = Table.ExpandRecordColumn(#"Get Responses", "Responses", {"value"}, {"value"}),
  #"Expanded value" = Table.ExpandListColumn(#"Expanded Responses", "value"),
  #"Expanded value 1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id", "answers"}, {"Response Id", "Answers"}),

Obtener respuestas: Se añade una columna llamada “Responses” que contiene los datos JSON obtenidos de la API de Forms. Esta columna se llena con las respuestas del formulario.
Expandir columna de respuestas: Se expande la columna “Responses” para obtener el campo “value”, que contiene una lista de respuestas.
Expandir lista de valores: Se expande la lista contenida en la columna “value”, de modo que cada respuesta individual se convierte en una fila separada.
Expandir registros de valores: Se expande cada registro en la columna “value” para obtener los campos “id” y “answers”, renombrándolos adecuadamente para mayor claridad.

Parsear JSON de Respuestas

  #"Parse JSON answers" = Table.AddColumn(#"Expanded value 1", "JSON Converted", each Json.Document([Answers])),
  #"Expanded JSON Converted" = Table.ExpandListColumn(#"Parse JSON answers", "JSON Converted"),
  #"Expanded JSON Converted 1" = Table.ExpandRecordColumn(#"Expanded JSON Converted", "JSON Converted", {"answer1", "questionId"}, {"Answer Text", "Question Id"}),

Convertir respuestas JSON: Se añade una columna llamada “JSON Converted” que convierte el campo “Answers” de texto JSON a un objeto JSON.
Expandir respuestas JSON: Se expande la lista contenida en la columna “JSON Converted”, de modo que cada respuesta individual se convierte en una fila separada.
Expandir registros de respuestas JSON: Se expande cada registro en la columna “JSON Converted” para obtener los campos “answer1” y “questionId”, renombrándolos adecuadamente para mayor claridad.

Manejo de errores para obtener el texto de respuesta:

Si nos fijamos en el paso anterior, en la columna de <<Answer Text>> tenemos filas que contienen registros y otra directamente valores. Esto se debe a que las preguntas que son de tipo <<Likert>> almacena las respuestas en un JSON anidado. A continuación se explica el proceso para extraer las respuestas correctamente.

  #"Duplicated answer text" = Table.DuplicateColumn(#"Expanded JSON Converted 1", "Answer Text", "Answer Text - Copy"),
  #"Expanded displayText" = Table.ExpandRecordColumn(#"Duplicated answer text", "Answer Text", {"displayText"}),
  #"Handled error to get response text" = Table.AddColumn(#"Expanded displayText", "Response Text", each try [displayText] otherwise [#"Answer Text - Copy"]),

Duplicar columna de texto de respuesta: Se duplica la columna “Answer Text” para crear una copia llamada “Answer Text – Copy”.
Expandir atributo <<displayText>>: Se expande la columna “Answer Text” para obtener el campo “displayText”.
Manejar errores para obtener el texto de respuesta: Se añade una columna llamada “Response Text” que intenta obtener el valor de “displayText” y, en caso de error, utiliza el valor de “Answer Text – Copy”.

La clave es expandir la columna <<displayText>> para obtener el contenido de los JSON anidados, pero esto nos va a generar error en la filas donde no hay JSON anidados. Por ello, añadimos una columna condicional donde intentamos obtener el valor de <<displayText>> y, en caso de error, utiliza el valor de <<Answer Text – Copy>>.

Finalmente, seleccionamos las columnas relevantes y transformamos los tipo de datos para que sean correctos y adecuados para el análisis posterior.

#"Choose columns" = Table.SelectColumns(#"Handled error to get response text", {"Form Id", "Response Id", "Question Id", "Response Text"}),
#"Changed column type" = Table.TransformColumnTypes(#"Choose columns", {{"Form Id", type text}, {"Response Id", type text}, {"Question Id", type text}, {"Response Text", type text}})
in
  #"Changed column type"

#6: Mapeo y Carga de datos

Una vez realizado el proceso de transformación de los datos, ya los tenemos listos para realizar el mapeo con los campos de nuestras tablas en Dataverse. Detalles a tener en cuenta:

Seleccionar en cada una de las tablas las <<External Key>> creadas previamente como columna clave.
El orden de la carga de las tablas debe de ser: Questions, Responses y Question Responses. Ese es el motivo de nombrar las tablas de esta forma: 1_Questions, 2_Responses y 3_Question_Responses.

A continuación, muestro el mapeo de cada una de las tablas.

Preguntas

Respuestas (cabecera)

Respuestas (detalle)

Resultado Final

Una vez publicado y refrescado nuestro dataflow, la información se vería así dentro de nuestra aplicación.

En resumen, hemos logrado sincronizar las preguntas y respuestas de un formulario de Microsoft Forms con nuestra aplicación, lo que nos abre un abanico de posibilidades para automatizar procesos según las respuestas obtenidas. Si lo implementamos directamente desde Power BI, podremos mantener nuestros informes actualizados de manera continua.

En la próxima entrega, exploraremos cómo personalizar las preguntas del formulario utilizando datos de nuestra aplicación. Esto nos permitirá añadir una capa extra de personalización y actualizar nuestros formularios según nuestras necesidades específicas.

¡Hasta la próxima!