Power Query: importar datos desde la web

La web es un lugar enorme con tanta información. Sin embargo, el hecho de que los datos estén ahí no hace que sea fácil acceder a ellos. En esta publicación, veremos cómo podemos cargar datos web en Power Query. Si bien es posible que le entusiasme la idea de absorber datos de todas partes con solo hacer clic en un botón, no es una solución milagrosa. Power Query puede hacer mucho, pero, para empezar, el sitio web debe tener un formato razonablemente utilizable.
De todos modos, basta de charlas, es hora de importar algunos datos de la web.
Importación web básica
A los efectos de este ejercicio, utilizaremos https://www.xe.com para importar tipos de cambio en Power Query.
El sitio web contiene páginas que muestran los tipos de cambio de cualquier moneda en cualquier fecha. Aquí están las tarifas en USD para el 1 de julio de 2019.
La parte más útil de la captura de pantalla anterior es la URL. Si miras de cerca, contiene el código de moneda y la fecha dentro de la URL. Hmmm… Me pregunto si podríamos usar eso de una manera más avanzada.
https://www.xe.com/currencytables/?from= USD fecha= 2019-07-01
De todos modos, antes de pasar a lo más avanzado, comencemos con lo básico. Copie la URL, luego en Excel haga clic en Datos – Obtener datos – De otras fuentes – De la Web
Se abrirá la ventana Desde Web. Pegue la URL que copiamos anteriormente en el cuadro de URL y haga clic en Aceptar .
La siguiente ventana que aparecerá será la ventana del Navegador . Como las tablas web de los sitios web pueden tener convenciones de nomenclatura deficientes, no siempre está claro qué tabla necesitamos. Debemos pulsar sobre cada uno hasta encontrar el que queremos. Una vez que haya encontrado la tabla correcta, haga clic en Transformar datos .
Cuando se carga el Editor de Power Query, contiene todos los datos de moneda.
Asigne un nombre útil a la consulta, como FXRates
Haga clic en Inicio: cerrar y cargar para enviar los datos a Excel. ¡Qué fácil fue eso!
Si la página web contiene datos en vivo, actualizará la consulta con esos datos en vivo cada vez que hagamos clic en actualizar. Eso es algo bastante poderoso.
Editando la consulta
Ahora editemos la consulta para usarla para diferentes fechas y monedas (al hacer esto, nos encontraremos con un error que debemos corregir; podemos manejarlo).
En Excel haga clic en Datos – Consultas y conexiones
Haga doble clic en la consulta en el panel Consultas y conexiones para abrir el editor de Power Query.
El editor de Power Query se abrirá nuevamente, haga clic en el paso Fuente dentro de Pasos aplicados. La URL está codificada en la consulta. Edite el código M en la barra de fórmulas para cambiar la consulta para incluir una moneda diferente y una fecha diferente. Para nuestro ejemplo, seleccioné EUR y el 1 de enero de 2019.
El siguiente código M:
= Web.Page(Web.Contents("https://www.xe.com/currencytables/?from= USD fecha= 2019-07-01 "))
se convierte
= Web.Page(Web.Contents("https://www.xe.com/currencytables/?from= EUR fecha= 2019-01-01 "))
Haga clic en los pasos restantes de la consulta. El paso Tipo cambiado revelará el siguiente error.
Mirar el código M en la barra de fórmulas revela el problema; hay referencias específicas a la moneda USD en el encabezado (consulte las secciones resaltadas en rojo a continuación).
= Table.TransformColumnTypes(Data0,{{"Código de moneda ▲▼", escriba texto}, {"Nombre de moneda ▲▼", escriba texto}, {" Unidades por USD ", escriba número}, {" USD por unidad ", teclea un número}})
Nuestra moneda seleccionada ahora es EUR; el encabezado de la columna se convertiría en Unidades por EUR y EUR por Unidad.
El cambio en los encabezados de las columnas proviene directamente de la página web de origen; por lo tanto no hay nada que podamos hacer para detenerlo. Pero hay cosas que podemos hacer para solucionarlo.
- Elimine el paso Cambiar tipo que está causando el error.
- Degrade el encabezado a la primera fila haciendo clic en Transformar – Primera fila del usuario como encabezados (menú desplegable) – Usar encabezados como primera fila .
- Elimine la fila superior haciendo clic en Inicio – Eliminar filas – Eliminar filas superiores . Ingrese 1 en el cuadro Número de filas, haga clic en Aceptar .
- Ingrese manualmente los nombres de las columnas adecuadas (yo opté por Código, Moneda, Tasa y Tasa invertida).
- Aplique tipos de datos adecuados a cada columna (yo he elegido Texto, Texto, Decimal, Decimal).
La ventana de vista previa ahora se ve así:
Haga clic en Cerrar carga para enviar la consulta a Excel. Ahora podemos cambiar la moneda y las fechas en la URL de origen, lo que significa que podemos obtener el tipo de cambio para cualquier fecha que queramos.
Usar parámetros con consultas web
Editar el código M cada vez que queremos importar diferentes fechas y tarifas no es lo ideal. Lleva mucho tiempo y, además, ¿podemos realmente confiar en que otros usuarios que no saben nada sobre Power Query actualizarán el paso correctamente?
Si ha seguido esta serie de Power Query, recordará que anteriormente creamos parámetros para administrar variables. Si intentamos esto con consultas web, nos encontraremos con un error extraño (consulte la captura de pantalla a continuación).
Las razones de esto son complicadas y demasiado técnicas para nuestra serie introductoria. Si quieres más detalles, mira esta publicación .
Pero no te preocupes, existe otra forma de utilizar las celdas de una hoja de cálculo para recuperar la información que queremos.
Usar fórmulas personalizadas
En una publicación anterior, combinamos todos los archivos de una carpeta . Para lograr esto, utilizamos la función Excel.Workbook() para combinar cualquier archivo que estuviera en la carpeta. Podemos crear nuestra propia función, que en lugar de combinar archivos, devolverá consultas web basadas en las URL que le proporcionamos.
Comencemos con una nueva tabla (la he llamado tblURLs). Contiene los siguientes valores:
- Código
- Fecha
- URL
La fórmula de la celda C2 se construye concatenando el código de moneda y la fecha en la estructura de la URL.
="https://www.xe.com/currencytables/?from="[@Code]"date="TEXTO([@Date],"aaaa-mm-dd")
A continuación, agregue esta tabla a Power Query. Seleccione una celda dentro de la tabla, luego Datos – De tabla/rango .
Ahora estamos listos para crear una función personalizada.
Creando la función
Volvamos a la consulta original, haciendo clic en la consulta FXRates de la lista de consultas.
Vamos a cambiar esta consulta a una función personalizada. Haga clic en Inicio – Editor avanzado para mostrar el Código M.
Cambie el código M de la siguiente manera:
(1) Agregue el siguiente código al principio de la consulta:
(URL) como tabla =
(2) Cambie la URL de origen dentro del código a la URL de letras, que es la variable que creamos anteriormente.
El código M ahora será el siguiente (las secciones resaltadas son las partes que hemos cambiado).
(URL) as table = let Source = Web.Page(Web.Contents( URL )), Data0 = Source{0}[Data], #"Encabezados degradados" = Table.DemoteHeaders(Data0), #"Tipo modificado" = Table.TransformColumnTypes(#"Encabezados degradados",{{"Columna1", escriba texto}, {"Columna2", escriba texto}, {"Columna3", escriba texto}, {"Columna4", escriba texto}}), # "Filas superiores eliminadas" = Table.Skip(#"Tipo modificado",1), #"Columnas renombradas" = Table.RenameColumns(#"Filas superiores eliminadas",{{"Columna1", "Código"}, {"Columna2 ", "Moneda"}, {"Columna3", "Tasa"}, {"Columna4", "Tasa invertida"}}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Columnas renombradas",{{"Tasa ", escriba número}, {"Tasa Invertida", escriba número}, {"Código", escriba texto}, {"Moneda", escriba texto}})en #"Tipo Cambiado1"
Haga clic en Listo en la ventana del Editor avanzado para aceptar los cambios.
La ventana de Power Query cambiará para verse así:
En la lista de consultas muestra la consulta de tipo de cambio que acabamos de cambiar con un fx al lado, esto significa que es una función.
Usando la función
Abra la consulta tblURLs.
Haga clic en Agregar columna – Columna personalizada
En la ventana Columna personalizada, ingrese lo siguiente:
Nuevo nombre de columna:
Tipos de cambio importados
Fórmula de columna personalizada:
=FXRate([URL])
Luego haga clic en Aceptar .
Haga clic en Continuar en la advertencia de privacidad.
En la ventana Niveles de privacidad, marque la casilla para Ignorar niveles de privacidad y luego haga clic en Guardar.
Haga clic en el icono de expansión en la parte superior de la columna Tipos de cambio importados (la nueva columna que acabamos de agregar). Desmarque la casilla Usar nombre de columna original como prefijo y luego haga clic en Aceptar .
Los datos de XE.com se han importado a la consulta.
Ordene la consulta mediante:
- Cambiar el tipo de datos de cada columna
- Eliminar las columnas que no necesitamos.
Haga clic en Cerrar carga para enviar la información a Excel.
Es hora de probar esto para demostrar que podemos obtener tipos de cambio para cualquier moneda y en cualquier fecha. En Excel, cambie la fecha o el código de moneda en la tabla tblURL. Haga clic en Datos – Actualizar todo , los tipos de cambio de la consulta ahora se actualizarán. Qué genial es eso, ¿eh?
Importar varias páginas web
Hasta ahora hemos importado una sola página web. Pero ¿y si quisiéramos importar varias páginas web? Si la función personalizada funciona, será fácil.
Agregue algunas filas más de datos a la tabla tblURL.
Haga clic en Datos: actualizar todo
Consulte la tabla de tipos de cambio... ¡ BOOM! (Ese fue el sonido de tu mente estallando). Power Query ha importado todas las fechas y tarifas incluidas en la tabla de tblURL. Podemos obtener todos los tipos de cambio para cualquier fecha y cualquier moneda base.
Algunas advertencias
Esto es algo poderoso. Pero hay un par de cosas que debes tener en cuenta:
- Si llama a demasiadas URL, es posible que la consulta tarde en actualizarse. Fuerza.
- Si xe.com cambia de sitio web, la consulta puede dejar de funcionar correctamente.
- No podemos obtener datos de todos los sitios web porque algunos no están estructurados de la manera correcta.
Deja una respuesta