Tutorial: Dar forma a los datos y combinarlos en Power BI Desktop

Con Power BI Desktop, puede conectarse a muchos tipos de orígenes de datos distintos y dar forma a la información para que se ajuste a sus necesidades. De este modo, podrá crear informes visuales y compartirlos con otras personas. Dar forma a los datos significa transformar los datos: cambiar el nombre de las columnas o las tablas, convertir texto en números, quitar filas, configurar la primera fila como encabezado, etc. Combinar datos significa conectarse a dos o más orígenes de datos, darles la forma necesaria y consolidarlos después en una consulta individual.

En este tutorial, aprenderá a:

  • Dar forma a los datos con el Editor de Power Query.
  • Conectarse a diferentes orígenes de datos.
  • Combinar esos orígenes de datos y crear un modelo de datos para usarlo en los informes.

En este tutorial, se explica cómo se forma una consulta utilizando Power BI Desktop y se detallan algunas de las tareas más comunes. La consulta que se usa aquí se describe con más detalle, incluido el procedimiento para crear la consulta desde cero, en Introducción a Power BI Desktop.

El Editor de Power Query en Power BI Desktop usa los menús contextuales y la cinta de opciones Transformar. La mayor parte de los elementos que se pueden seleccionar en la cinta también están disponibles en el menú contextual que aparece al hacer clic con el botón derecho en un elemento (por ejemplo, en una columna).

Dar forma a los datos

Para dar forma a los datos en el Editor de Power Query, tiene que proporcionar instrucciones paso a paso para que esta característica ajuste los datos a medida que se cargan y se presentan. El origen de datos del que procede la información no se verá afectado. Los únicos datos a los que se va a dar forma o que se van a ajustar son los de esta vista concreta.

El Editor de Power Query registra los pasos especificados (por ejemplo, cambiar el nombre de una tabla, transformar un tipo de datos o eliminar una columna). Cada vez que esta consulta se conecte al origen de datos, el Editor de Power Query llevará a cabo esos pasos para que los datos siempre tengan la forma indicada. Este proceso tendrá lugar siempre que utilice el Editor de Power Query o que otra persona utilice su consulta compartida; por ejemplo, en el servicio Power BI. Estos pasos se capturan, de manera secuencial, en el panel Configuración de consulta, en PASOS APLICADOS. Explicaremos cada uno de estos pasos en este artículo.

Screenshot of Power Query Editor with the Query Settings pane and Applied steps list.

  1. Importe los datos desde un origen web. Seleccione la lista desplegable Obtener datos y, a continuación, elija Web.

    Screenshot of Power Query Editor with the Get data menu and Web source selected.

  2. Pegue esta dirección URL en el cuadro de diálogo De web y seleccione Aceptar.

    https://www.fool.com/research/best-states-to-retire
    

    Screenshot of Power Query Editor's From Web dialog with the source page's URL entered.

  3. En el cuadro de diálogo Navegador, seleccione Table 1 y, después, elija Transformar datos.

    Screenshot of Power Query Editor's Navigator dialog with HTML Table 1 selected and the Transform Data button highlighted.

Sugerencia

Algunas informaciones de las tablas de la dirección URL anterior pueden cambiar o actualizarse ocasionalmente. Como resultado, es posible que tenga que ajustar las selecciones o los pasos de este artículo en consecuencia.

  1. Se abre la ventana Editor de Power Query. Puede ver los pasos predeterminados aplicados hasta ahora, en el panel Configuración de consulta en PASOS APLICADOS.

    • Origen: conexión con el sitio web.
    • Tabla extraída de HTML: selección de la tabla.
    • Encabezados promovidos: cambiar la fila superior de datos en los encabezados de columna.
    • Tipo cambiado: cambiar los tipos de columna, que se importan como texto, a sus tipos inferidos.

    Screenshot of the Power Query Editor window with Query Settings highlighted.

  2. Cambie el nombre de la tabla del valor predeterminado Table 1 a Retirement Data y, después, presione Entrar.

    Screenshot of Power Query Editor showing how to edit a table name in Query Settings.

  3. Los datos existentes se ordenan por una puntuación ponderada, como se describe en la página web de origen en Metodología. Vamos a agregar una columna personalizada para calcular una puntuación diferente. A continuación, ordenaremos la tabla de esta columna para comparar la clasificación de la puntuación personalizada con la clasificación existente.

  4. En la cinta de opciones Agregar columna, seleccione Columna personalizada.

    Screenshot of Power Query Editor's Add Column ribbon with the Custom Column button highlighted.

  5. En el cuadro de diálogo Columna personalizada, en Nuevo nombre de columna, escriba Nueva puntuación. En Fórmula de columna personalizada, especifique lo siguiente:

    ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8
    
  6. Compruebe que el mensaje de estado es No se han detectado errores de sintaxis y seleccione Aceptar.

    Screenshot of Power Query Editor's Custom Column dialog showing the new column name, custom column formula, and no syntax errors.

  7. En Configuración de consulta, la lista PASOS APLICADOS ahora muestra el nuevo paso Personalizada agregada que acabamos de definir.

    Screenshot of Power Query Editor's Query Settings pane showing the Applied Steps list with the actions so far.

Ajuste de los datos

Antes de poder trabajar con esta consulta, hagamos algunos cambios para ajustar los datos:

  • Ajustar las clasificaciones quitando una columna.

    Por ejemplo, supongamos que Weather no es un factor en nuestros resultados. Quitar esta columna de la consulta no afecta a los demás datos.

  • Corrija cualquier error.

    Al quitar una columna, es necesario ajustar los cálculos de la columna Nueva puntuación mediante el cambio de su fórmula.

  • Ordenar los datos.

    Ordene los datos en función de la columna Nueva puntuación y compare con la columna Clasificación existente.

  • Reemplazar los datos.

    Es importante resaltar cómo se van a reemplazar valores específicos y cómo insertar un paso aplicado.

Estos cambios se describen en los pasos siguientes.

  1. Para quitar la columna Weather, selecciónela, elija la pestaña Inicio de la cinta de opciones y haga clic en Quitar columnas.

    Screenshot of Power Query Editor's Home menu with the Remove Columns button highlighted.

    Nota:

    Observe que los valores de Nueva puntuación no han cambiado, lo que se debe al orden de los pasos. Editor de Power Query registra los pasos secuencialmente, pero de forma independiente, entre sí. Para aplicar acciones en una secuencia diferente, puede mover cada paso aplicado hacia arriba o hacia abajo.

  2. Haga clic con el botón derecho en un paso para ver su menú contextual.

    Screenshot of Power Query Editor's Applied Steps context menu.

  3. Suba el último paso, Columnas quitadas, y colóquelo justo encima del paso Personalizada agregada.

    Screenshot of Power Query Editor's Applied Steps list with the Removed Columns step now moved above the Custom Column step.

  4. Seleccione el paso Personalizada agregada.

    Observe que la columna Nueva puntuación ahora muestra Error en lugar del valor calculado.

    Screenshot of Power Query Editor and the New score column containing Error values.

    Hay varias maneras de obtener más información sobre cada error. Si selecciona la celda sin hacer clic en la palabra Error, el Editor de Power Query mostrará la información del error.

    Screenshot of Power Query Editor showing the New score column with Error details.

    Si selecciona la palabra Error directamente, el Editor de Power Query crea un Paso aplicado en el panel Configuración de la consulta y muestra información sobre el error. Como no necesitamos mostrar información sobre los errores en ningún otro lugar, seleccione Cancelar.

  5. Para corregir los errores, es necesario realizar dos cambios: quitar el nombre de la columna Weather y cambiar el divisor de 8 a 7. Puede realizar estos cambios de dos maneras:

    1. Haga clic con el botón derecho en el paso Columna personalizada y seleccione Editar configuración. Esta acción abre el cuadro de diálogo Columna personalizada que usó para crear la columna Nueva puntuación. Edite la fórmula como se ha descrito anteriormente, hasta que tenga este aspecto:

      Screenshot of Power Query Editor's Custom Column dialog with formula errors fixed.

    2. Seleccione la columna Nueva puntuación, luego muestre la fórmula de datos de la columna seleccionando la casilla Barra de fórmulas de la pestaña Ver.

      Screenshot of Power Query Editor showing the New score column and its data formula with errors fixed.

      Edite la fórmula como se ha descrito anteriormente, hasta que tenga este aspecto y luego presione Entrar.

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] ) / 7)              
      

    El Editor de Power Query reemplaza los datos por los valores revisados y el paso Personalizada agregada se completa sin errores.

    Nota

    También puede seleccionar Quitar errores en la cinta o el menú contextual, lo que quitará todas las filas que contienen errores. Sin embargo, en este tutorial queremos conservar todos los datos de la tabla.

  6. Ordene los datos con arreglo a la columna Nueva puntuación. En primer lugar, seleccione el último paso aplicado, Personalizada agregada para mostrar los datos más recientes. Después, seleccione la lista desplegable situada junto al encabezado de columna Nueva puntuación y elija Orden ascendente.

    Screenshot of Power Query Editor showing the New score column with Sort Descending highlighted.

    Ahora, los datos están ordenados con arreglo a la columna Nueva puntuación. Puede seleccionar un paso aplicado en cualquier parte de la lista y seguir dando forma a los datos en ese momento en la secuencia. El Editor de Power Query insertará de forma automática un paso nuevo inmediatamente después del paso aplicado seleccionado actualmente.

  7. En PASOS APLICADOS, seleccione el paso anterior a la columna personalizada, que es el paso Columnas quitadas. Aquí reemplazaremos el valor de la clasificación Housing cost de Oregón. Haga clic con el botón derecho en la celda que contiene el valor Housing cost de Oregón y seleccione Reemplazar valores. Observe cuál es el paso aplicado que está seleccionado actualmente.

    Screenshot of the Power Query Editor window showing the Housing cost column with the Replace Values right-click menu item highlighted.

  8. Seleccione Insertar.

    Como vamos a insertar un paso, el Editor de Power Query nos recuerda que los pasos posteriores podrían hacer que la consulta se interrumpiera.

    Screenshot of Power Query Editor's Insert Step verification dialog.

  9. Cambie el valor de los datos a 100,0.

    El Editor de Power Query reemplaza los datos de Oregón. Cuando se crea un paso aplicado nuevo, el Editor de Power Query le asigna un nombre en función de la acción; en este caso, Valor reemplazado. Si tiene varios pasos con el mismo nombre en la consulta, el Editor de Power Query agrega un número creciente al nombre de cada paso aplicado posterior.

  10. Seleccione el último Paso aplicado y Filas ordenadas.

    Observe que los datos han cambiado con respecto a la nueva clasificación de Oregón. Este cambio se produce porque hemos insertado el paso Valor reemplazado en la ubicación correcta, antes del paso Personalizada agregada.

    Ahora, hemos dado a los datos la forma que necesitábamos. A continuación, vamos a conectarnos a otro origen de datos y combinar datos.

Combinar datos

Los datos sobre los diferentes estados son interesantes y serán útiles para crear otras consultas y análisis. Sin embargo, la mayoría de los datos sobre los estados usan una abreviatura de dos letras para los códigos de estado, no el nombre completo del estado. Debemos encontrar un modo de asociar las abreviaturas con los nombres de los estados.

Hay otro origen de datos públicos que proporciona esa asociación, pero necesita unos buenos ajustes para que podamos conectarlo a nuestra tabla sobre la jubilación. Para dar forma a los datos, siga estos pasos:

  1. En la cinta de opciones Inicio de Editor de Power Query, seleccione Nuevo origen > Web.

  2. Escriba la dirección del sitio web que contiene las abreviaturas de los estados, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations , y seleccione Conectar.

    En Navegador, se muestra el contenido del sitio web.

    Screenshot of Power Query Editor's Navigator page showing the Codes and abbreviations table selected.

  3. Seleccione Códigos y abreviaturas para estados estadounidenses, distrito federal, territorios y otras regiones.

    Sugerencia

    Llevará algo de tiempo dar forma a los datos de la tabla para reducirlos y que se adapten a lo que queremos. ¿Hay una forma más rápida o fácil de realizar los siguientes pasos? Sí, podríamos crear una relación entre las dos tablas y dar forma a los datos según esa relación. Los pasos de ejemplo siguientes son útiles para aprender a trabajar con tablas. Sin embargo, las relaciones pueden ayudarle a usar rápidamente datos de varias tablas.

Para ajusta los datos, siga estos pasos:

  1. Quite la fila superior. Esta fila es el resultado del modo en que se creó la tabla de la página web, así que no la necesitamos. Desde la cinta de opciones Inicio, seleccione Quitar filas > Quitar filas superiores.

    Screenshot of Power Query Editor highlighting the Remove Rows dropdown and the Remove Top Rows item.

    Aparece el cuadro de diálogo Quitar filas superiores. Especifique 1 fila para quitar.

  2. Promueva la nueva fila superior a los encabezados con Usar primera fila como encabezado desde la pestaña Inicio o desde la pestaña Transformar de la cinta de opciones.

  3. Como la tabla Datos de jubilación no tiene información sobre Washington D. C. ni territorios, debemos filtrarlos en nuestra lista. Seleccione la lista desplegable de la columna Nombre y estado de región_1 y desactive todas las casillas excepto Estado.

    Screenshot of Power Query Editor showing a column filter with only the State value selected.

  4. Quite todas las columnas innecesarias. Dado que solo necesitamos la asignación de cada estado a su abreviatura oficial de dos letras (Nombre y estado de la región y columnas ANSI), podemos quitar las demás columnas. En primer lugar, seleccione la columna Nombre y estado de la región, mantenga presionada la tecla CTRL y seleccione la columna ANSI. En la pestaña Inicio de la cinta de opciones, seleccione Quitar columnas > Quitar otras columnas.

    Screenshot of Power Query Editor highlighting the Remove Columns dropdown and the Remove Other Columns item.

    Nota:

    La secuencia de los pasos aplicados en el Editor de Power Query es importante y afecta a la forma en que se ajustan los datos. También es importante tener en cuenta cómo un paso puede afectar a otro paso posterior. Por ejemplo, si quita un paso de los pasos aplicados, es posible que los pasos posteriores no se comporten según lo previsto originalmente.

    Nota

    Al cambiar el tamaño de la ventana del Editor de Power Query para reducir el ancho, algunos elementos de la cinta de opciones se comprimen para optimizar el uso del espacio visible. Al aumentar el ancho de la ventana de Editor de Power Query, se expanden los elementos de la cinta de opciones para hacer el mayor uso posible del área aumentada de esta.

  5. Cambie el nombre de las columnas y la tabla. Hay un par de formas de cambiar el nombre de una columna. En primer lugar, seleccione la columna y, después, puede seleccionar Cambiar nombre en la pestaña Transformar de la cinta de opciones o bien hacer clic con el botón derecho y seleccionar Cambiar nombre. En la siguiente imagen se muestran ambas opciones, pero solo necesitará elegir una.

    Screenshot of Power Query Editor highlighting the Rename button and also the Rename right-click item.

  6. Vamos a llamarlas Nombre de estado y Código de estado. Para cambiar el nombre de la tabla, escriba uno en NombreCódigos de estado en el panel Configuración de consulta.

    Screenshot of Power Query Editor window showing the results of shaping state codes source data into a table.

Combinación de consultas

Ahora que hemos dado a la tabla Códigos de estado la forma que queríamos, vamos a combinar estas dos tablas (o consultas) en una sola. Como las tablas que tenemos ahora son el resultado de las consultas que aplicamos a los datos, a menudo también se les llama consultas.

Hay dos formas principales de combinar las consultas: combinar y anexar.

  • Si desea agregar una o más columnas a otra consulta, combine las consultas.
  • Si desea agregar una o más filas de datos que desea agregar a una consulta existente, asocie la consulta.

En este caso, lo que vamos a hacer es combinar las consultas:

  1. En el panel izquierdo de Editor de Power Query, seleccione la consulta en la que quiere combinar la otra consulta. En este caso, será Datos de jubilación.

  2. Seleccione Combinar consultas > Combinar consultas en la pestaña Inicio de la cinta de opciones.

    Screenshot of Power Query Editor's Merge Queries dropdown with the Merge Queries item highlighted.

    Es posible que tenga que establecer los niveles de privacidad para garantizar que los datos se combinan sin que se incluyan o transfieran datos no deseados.

    Aparece la ventana Combinar. Le pedirá que seleccione la tabla que desea combinar con la tabla seleccionada y las columnas correspondientes que se van a utilizar para realizar la combinación.

  3. Seleccione Estado en la tabla Datos de jubilación y, después, seleccione la consulta Códigos de estado.

    Cuando haya seleccionado las columnas correspondientes, se habilitará el botón Aceptar.

    Screenshot of Power Query Editor's Merge dialog.

  4. Seleccione Aceptar.

    El Editor de Power Query crea una columna al final de la consulta con el contenido de la tabla (consulta) que se ha combinado con la consulta actual. Todas las columnas de la consulta combinada se comprimen en la columna, pero puede expandir la tabla e incluir las columnas que quiera.

  5. Para expandir la tabla combinada y seleccionar las columnas que desea incluir, haga clic en el icono de Expandir ( ).

    Aparecerá la ventana Expandir.

    Screenshot of Power Query Editor's column Expand dialog showing the State Code column highlighted.

  6. En este caso, solo vamos a utilizar la columna Código de estado. Seleccione esa columna, desactive Usar el nombre de columna original como prefijo y seleccione Aceptar.

    Si dejáramos activada la casilla Usar el nombre de columna original como prefijo, la columna combinada se llamaría Códigos de estado.Código de estado.

    Nota

    Si desea explorar cómo incorporar la tabla Códigos de estado, puede experimentar un poco. Si no le gustan los resultados, elimine ese paso de la lista PASOS APLICADOS del panel Configuración de consulta y la consulta regresará al estado anterior a la aplicación del paso Expandir. Puede realizar esta operación tantas veces como quiera hasta que el proceso de expansión tenga la apariencia deseada.

    Ahora tiene una única consulta (tabla) con dos orígenes de datos combinados, a los que se les dio forma para que se adecuaran a sus necesidades. Esta consulta puede ser una base para conexiones de datos interesantes, como estadísticas de costos de vivienda, calidad de vida o tasa de criminalidad en cualquier estado.

  7. Para aplicar los cambios y cerrar Editor de Power Query, seleccione Cerrar y aplicar en la pestaña Inicio de la cinta de opciones.

    El modelo semántico transformado aparece en Power BI Desktop, listo para usarse para crear informes.

    Screenshot of Power Query Editor's Close & Apply button.

Para más información sobre Power BI Desktop y sus funcionalidades, consulte los siguientes recursos: