Extraer páginas web con Power Query - Consejos de Excel

Power Query es bastante poderoso. Pero estoy a punto de hacer Power Query Squared … escribir una consulta para una página y luego hacer que Excel realice la misma consulta para una lista completa de páginas web.

Ver video

El truco de hoy está adaptado del libro M is for Data Monkey
Cree una consulta para obtener datos de una página web
Edite la consulta para convertirla en una función con (VariableName) => antes de Let
Cambiar la URL codificada de forma rígida a VariableName
Cambiar el nombre de la consulta a fxWeather
Cerrar y cargar. Los datos desaparecerán.
Use Excel Trickery para crear una tabla de todas las URL
Cree una consulta a partir de esa tabla.
Agregar una nueva columna de Clima =fxWeather((URL))
Expande la columna. Desmarcar Prefijo
¡Increíble!

Transcripción del video

Aprenda Excel del Podcast, Episodio 2056: Power Query Squared

Oye, bienvenido de nuevo al netcast, soy Bill Jelen. Me encontré con este truco mientras me preparaba para hacer un seminario en una conferencia en Dallas llamada Excelapalooza, el mejor nombre de conferencia de Excel de todos los tiempos. Debería comprobarlo cada septiembre en Dallas.

Y el crédito para Ken Puls y Miguel Escobar porque tuve que hacer una hora en Power Query, así que, por supuesto, saqué un libro increíble, el mejor libro del mundo sobre Power Query. Estaba hojeando el libro y vi que tenían una técnica y dije: “Espera un segundo. Voy a ver si puedo adaptar esta técnica ”. Y esto para mí es impresionante en la consulta de energía. Y esto es lo que vamos a hacer, vamos a tomar una consulta de potencia y luego vamos a ejecutar esa consulta de potencia, esa consulta, docenas de veces, ¿de acuerdo?

Entonces, el ejemplo que se me ocurrió fue donde quería extraer datos de una página web, de acuerdo. Y salí, solo busqué algunas páginas web que pudiera, que pudiera usar como ejemplo. Terminé en Weather Underground y aquí está la URL, y pueden ver que estaba en Dallas-Fort Worth, por lo que estamos extrayendo datos para Dallas y parece que es el 2 de enero de 2015. Así que ahí mismo en la URL están los parámetros , ¿derecho? Y esa es una URL que está lista para hacer que esto funcione.

Echaremos un vistazo rápido a la página web, aunque no es tan importante. Verá que hay muchos datos diferentes aquí en la página web y decidí que solo iba a intentar obtener precipitaciones y temperaturas altas y bajas. Y aquí está esa consulta. Y déjeme decirle aquí mismo que este podcast no trata sobre cómo crear esta consulta, nueva consulta, desde otras fuentes, desde la web, especificar la URL y luego un montón de pasos que no voy a detallar aquí para obtener mi respuesta final de Max Temp, Min Temp y precipitación. El punto es que tomará su propia consulta y la hará funcionar para un montón de cosas.

Entonces, hago clic en Cerrar y cargar y esta consulta está funcionando, está devolviendo mi única fila. Todo es asombroso. Y voy a volver, voy a Editar esta consulta y voy a Ver, Editor avanzado. Voy a tomar esta consulta y voy a hacer que sea una función, ¿de acuerdo? Así que aquí mismo, antes de la palabra LET, presiono Enter. Y entre paréntesis, le voy a dar una variable (MyURL) y luego => pequeña flecha allí, ¿de acuerdo? Frio. Y luego aquí abajo, donde tienen la URL entre comillas, quiero deshacerme de la URL completa, incluidas las comillas, y luego escribir mi nombre de variable MyURL, está bien. Entonces, lo que estamos diciendo es que lo vamos a pasar a la URL y va a hacer la misma consulta pero con cualquier URL que pasemos.

Ahora, un par de cosas desconcertantes aquí, cuando hago clic en Listo, ¡oh hombres! Todos mis pasos aplicados se han ido y quieren que ingrese un parámetro. Solo ignora todo eso. Vamos a cambiar el nombre de esto; lo llamaremos fxWeather. FX, por supuesto, es la abreviatura de función y tienes que recordar realmente este nombre y recordar qué letras están en mayúscula, eso será muy importante en un par de minutos. Inicio, Cerrar y Cargar y ¡BAM! Todo se ha ido. ¡Oh no! Pero eso está bien. Muy bien, entonces sabemos que está ahí. Es solo una conexión. Ahora, voy a venir aquí y esto es simplemente Excel antiguo, ¿de acuerdo? Así que aquí está la URL, la dividí en la primera parte de la URL, la parte final de la URL. Sé que necesito tomar la fecha; Necesito formatearlo en este extraño formato de año,mes y día, así que usé la función TEXTO para hacer eso. Pon la fecha de inicio aquí. Incluso puedo cambiar de aeropuerto, así que ahora voy y vengo. Hagamos MCO para Orlando y hagamos algunos datos recientes. Entonces comenzaré el 1/10/2016, está bien. Así que ahora tenemos esta gran mesita preparada aquí. Y por cierto, tiene que ser una mesa. Tienes que usar formato como tabla o Ctrl + T. Entonces, ya sabes, esto es solo tomar esa fecha y luego + 1 + 1 + 1. Lo formateo, construyo la URL.Tienes que usar formato como tabla o Ctrl + T. Entonces, ya sabes, esto es solo tomar esa fecha y luego + 1 + 1 + 1. Lo formateo, construyo la URL.Tienes que usar formato como tabla o Ctrl + T. Entonces, ya sabes, esto es solo tomar esa fecha y luego + 1 + 1 + 1. Lo formateo, construyo la URL.

Muy bien, vamos a crear una consulta a partir de esta tabla. Muy bien, y ahí está mi información. Voy a Agregar una nueva columna, Agregar una columna personalizada, las columnas se llamarían Clima y la fórmula será = fxWeather. Asegúrese de que sea exactamente el mismo caso, las mismas letras mayúsculas y minúsculas, e insertaremos ese campo llamado URL así, cerrando paréntesis. Sin errores de sintaxis, haga clic en Aceptar. Quieren saber acerca de la privacidad aquí, todos estos son datos públicos, haga clic en Guardar, de acuerdo. Entonces, ahí está nuestra cita. Es gracioso que hayan cambiado mi formato a algo que no se parece a lo que empecé. Y luego aquí está el clima con el símbolo de expansión. Entonces voy a hacer clic en el símbolo de expansión, desmarcar Usar el nombre de la columna original como prefijo. Quiero el Max, el Min, la Precipitación,haga clic en Aceptar. Muy bien, y ahora todo lo que necesito es la fecha y esa información. Así que haré clic derecho y eliminaré esta columna, haré clic derecho y eliminaré esta columna. Por aquí, no necesito ese tiempo, así que diré que esto es solo una cita, está bien. Y mira lo que está haciendo cada fecha que lo paso; está devolviendo lo alto, lo bajo y la precipitación para Orlando. Cada fila aquí va a una página web diferente. Imagínese, si no fueran 15 filas sino 5.000 filas, lo configuraría para que se ejecute durante la noche. Solía escribir macros para esto. De hecho, una de las páginas web es cómo crear una macro para extraer páginas web de mil páginas web diferentes en un sitio, ya no es necesario con Power query.Haremos clic derecho y eliminar esta columna, clic derecho y eliminar esta columna. Por aquí, no necesito ese tiempo, así que diré que esto es solo una cita, está bien. Y mira lo que está haciendo cada fecha que lo paso; está devolviendo lo alto, lo bajo y la precipitación para Orlando. Cada fila aquí va a una página web diferente. Imagínese, si no fueran 15 filas sino 5.000 filas, lo configuraría para que se ejecute durante la noche. Solía escribir macros para esto. De hecho, una de las páginas web es cómo crear una macro para extraer páginas web de mil páginas web diferentes en un sitio, ya no es necesario con Power query.Haremos clic derecho y eliminar esta columna, clic derecho y eliminar esta columna. Por aquí, no necesito ese tiempo, así que diré que esto es solo una cita, está bien. Y mira lo que está haciendo cada fecha que lo paso; está devolviendo lo alto, lo bajo y la precipitación para Orlando. Cada fila aquí va a una página web diferente. Imagínese, si no fueran 15 filas sino 5.000 filas, lo configuraría para que se ejecute durante la noche. Solía escribir macros para esto. De hecho, una de las páginas web es cómo crear una macro para extraer páginas web de mil páginas web diferentes en un sitio, ya no es necesario con Power query.s devolviendo lo alto, lo bajo y la precipitación para Orlando. Cada fila aquí va a una página web diferente. Imagínese, si no fueran 15 filas sino 5.000 filas, lo configuraría para que se ejecute durante la noche. Solía escribir macros para esto. De hecho, una de las páginas web es cómo crear una macro para extraer páginas web de mil páginas web diferentes en un sitio, ya no es necesario con Power query.s devolviendo lo alto, lo bajo y la precipitación para Orlando. Cada fila aquí va a una página web diferente. Imagínese, si no fueran 15 filas sino 5.000 filas, lo configuraría para que se ejecute durante la noche. Solía escribir macros para esto. De hecho, una de las páginas web es cómo crear una macro para extraer páginas web de mil páginas web diferentes en un sitio, ya no es necesario con Power query.

Ahora, cuando cierro y cargo, es gracioso que la vista previa aquí me muestre todos los resultados. Cuando cierre y cargue, en realidad van a hacer cada consulta. Y ahora mismo tenemos cargadas las filas de vista previa y esta información tardará bastante en actualizarse. Entonces, ve a almorzar, haz algo especialmente si estás haciendo más de 15 filas. Y es curioso que la vista previa sea correcta, pero todavía están saliendo y podrían fragmentarse, podrían fragmentarse, podrían fragmentarse en cada fila individual.

And there it's loaded. Is this amazing or what? Hey, I do a lot of Excel seminars, the Power Excel seminar in Orlando, Florida. Look at these beautiful temperatures we have down here on November 4, 2016. My afternoon will be all about Power Query, Power BI, Power Pivot, Power Map. So, I'm going to invite you down to Orlando to check out this seminar. There'll be a link there in the top-right hand corner.

Alright, so recap. Today's trick is from this book, M is for (DATA) MONKEY. We built a query to get one web page and then edit that query to change it into a function. So right before the Let statement variable name => and then change the hard-coded URL to whatever that variable name is. Rename the query to fxWeather, Close & Load, the data disappears. Then, we use some sort of Excel trickery to create a table of all the URLs we want to crawl, create a query from that table. This has to be a Ctrl+T table, add a new column of Weather = fxWeather and again it has to match the case there, (URL), Expand that column, uncheck Prefix. BAM! It is amazing.

Thanks to Ken and Miguel for writing this book. Thanks to you for stopping by. Hope to see you in Orlando on November 4th 2016. See you next time for another netcast from.

Download File

Descargue el archivo de muestra aquí: Podcast2056.xlsm