Power Query - довольно мощный инструмент. Но я собираюсь выполнить Power Query Squared … написать запрос для одной страницы, а затем заставить Excel выполнить тот же запрос для всего списка веб-страниц.
Смотреть видео
- Сегодняшний трюк адаптирован из книги M is for Data Monkey.
- Создайте запрос для получения данных с одной веб-страницы
- Отредактируйте запрос, чтобы преобразовать его в функцию с (VariableName) => перед Let
- Измените жестко заданный URL на VariableName
- Переименуйте запрос в fxWeather
- Закрыть и загрузить. Данные исчезнут.
- Используйте Excel Trickery, чтобы создать таблицу всех URL-адресов
- Создайте запрос из этой таблицы.
- Добавьте новый столбец погоды
=fxWeather((URL)) - Разверните столбец. Снимите флажок с префикса
- Удивительный!
Стенограмма видео
Изучите Excel из подкаста, эпизод 2056: Power Query Squared
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Я наткнулся на этот трюк, когда готовился провести семинар на конференции в Далласе под названием Excelapalooza, величайшее название конференции по Excel за всю историю. Вы должны проверять его каждый сентябрь в Далласе.
И благодарность Кену Пулсу и Мигелю Эскобару, потому что мне пришлось час потратить на запрос мощности, поэтому, конечно, я вытащил классную книгу, лучшую в мире книгу по Power Query. Я листал книгу и увидел, что у них есть одна техника, и сказал: «Подожди секунду. Я собираюсь посмотреть, смогу ли я адаптировать эту технику ». И для меня это потрясающий запрос мощности. И вот что мы собираемся сделать: мы возьмем один запрос мощности, а затем мы запустим этот запрос мощности, этот запрос, десятки раз, хорошо?
Итак, пример, который я придумал, был где я хотел получить данные с веб-страницы, хорошо. И я пошел - я просто поискал несколько веб-страниц, которые мог бы - которые я мог бы использовать в качестве примера. Я оказался в Weather Underground, и вот URL-адрес, и вы можете видеть, что я был в Далласе-Форт-Уэрте, поэтому мы извлекаем данные для Далласа, и это похоже на 2 января 2015 года. Итак, прямо в URL-адресе находятся параметры , правильно? И это URL-адрес, который просто созрел для того, чтобы заставить эту штуку работать.
Мы быстро взглянем на веб-страницу, хотя это не так важно. Вы видите, что здесь, на веб-странице, много разных данных, и я решил, что просто собираюсь попытаться получить осадки, высокую и низкую температуру. И вот этот вопрос. И позвольте мне сказать вам прямо здесь, что этот подкаст не о том, как создать этот запрос, новый запрос, из других источников, из Интернета, указать URL-адрес, а затем кучу шагов, которые я не собираюсь здесь подробно описывать, чтобы получить окончательный ответ о максимальной температуре, минимальной температуре и осадках. Дело в том, что вы собираетесь взять свой собственный запрос и заставить его работать для множества вещей.
Итак, я нажимаю Close & Load, и этот запрос работает, он возвращает мою одну строку. Все круто. И я собираюсь вернуться, я собираюсь отредактировать этот запрос, и я собираюсь перейти к просмотру, расширенному редактору. Я возьму этот запрос и сделаю его функцией, хорошо? Итак, прямо перед словом LET я нажимаю Enter. И в скобках я дам ему переменную (MyURL), а затем => маленькая стрелка, хорошо? Здорово. А затем здесь, где URL-адрес заключен в кавычки, я хочу избавиться от всего URL-адреса, включая кавычки, а затем ввести имя моей переменной MyURL, хорошо. Итак, мы говорим, что мы собираемся передать его URL-адресу, и он будет выполнять тот же запрос, но с любым URL-адресом, который мы его передаем.
А теперь пара неприятных вещей, когда я нажимаю «Готово», о люди! Все мои примененные шаги пропали, и они хотят, чтобы я ввел параметр. Просто игнорируйте все это. Мы собираемся переименовать это; мы будем называть его fxWeather. FX, конечно, является аббревиатурой для функции, и вы должны действительно запомнить это имя и помнить, какие буквы заглавные, это будет очень важно через пару минут. Домой, Close & Load, и БАМ! Все ушли. О нет! Но это нормально. Хорошо, мы знаем, что это там. Это только связь. А теперь я пойду сюда, а это просто старый Excel, хорошо? Итак, вот URL-адрес, я разбил его на первую часть URL-адреса, конечную часть URL-адреса. Я знаю, что мне нужно пойти на свидание; Мне нужно отформатировать его в этом странном формате года,месяц и день, поэтому я использовал для этого функцию ТЕКСТ. Введите дату начала здесь. Я даже могу сменить аэропорт, так что теперь я туда-сюда. Давайте сделаем MCO для Орландо и сделаем некоторые последние данные. Итак, я начну с 01.10.2016, хорошо. Итак, теперь у нас есть этот замечательный столик. И, кстати, это обязательно должен быть стол. Вы должны использовать формат как таблицу или Ctrl + T. Итак, вы знаете, это просто захват этой даты, а затем + 1 + 1 + 1. Форматирую, строю URL.Вы должны использовать формат как таблицу или Ctrl + T. Итак, вы знаете, это просто захват этой даты, а затем + 1 + 1 + 1. Форматирую, строю URL.Вы должны использовать формат как таблицу или Ctrl + T. Итак, вы знаете, это просто захват этой даты, а затем + 1 + 1 + 1. Форматирую, строю URL.
Хорошо, теперь мы собираемся построить запрос из этой таблицы. Хорошо, вот и моя информация. Я собираюсь добавить новый столбец, добавить настраиваемый столбец, столбцы будут называться Погода, а формула будет = fxWeather. Убедитесь, что это один и тот же регистр, одинаковые буквы верхнего и нижнего регистра, и мы вставим это поле с именем URL, закрывая круглую скобку. Нет синтаксических ошибок, нажмите ОК. Они хотят узнать о конфиденциальности здесь, это все общедоступные данные, нажмите «Сохранить», хорошо. Итак, вот и свидание. Забавно, что они на самом деле изменили мой формат на то, что не похоже на то, с чего я начинал. А вот погода с символом расширения. Итак, я собираюсь щелкнуть символ развертывания, снять флажок Использовать исходное имя столбца в качестве префикса. Я хочу Максимум, Минимум, Осадки,нажмите ОК. Хорошо, теперь все, что мне нужно, - это дата и эта информация. Я щелкну правой кнопкой мыши и удалю этот столбец, щелкните правой кнопкой мыши и удалю этот столбец. Здесь мне не нужно это время, поэтому я скажу, что это просто свидание, хорошо. И посмотрите, что он делает на каждом свидании, когда я его прохожу; он возвращает максимум, минимум и осадки для Орландо. Каждая строка здесь ведет на другую веб-страницу. Только представьте, если бы это было не 15, а 5000 строк, вы бы настроили его на выполнение за ночь. Я писал для этого макросы. Фактически, одна из веб-страниц, на которой мы обсуждаем, заключается в том, как создать макрос для очистки веб-страниц от тысячи различных веб-страниц на сайте, что больше не требуется с помощью power query.Щелкните правой кнопкой мыши и удалите этот столбец, щелкните правой кнопкой мыши и удалите этот столбец. Здесь мне не нужно это время, поэтому я скажу, что это просто свидание, хорошо. И посмотрите, что он делает на каждом свидании, когда я его прохожу; он возвращает максимум, минимум и осадки для Орландо. Каждая строка здесь ведет на другую веб-страницу. Только представьте, если бы это было не 15, а 5000 строк, вы бы настроили его на выполнение за ночь. Я писал для этого макросы. Фактически, одна из веб-страниц, на которой мы обсуждаем, заключается в том, как создать макрос для очистки веб-страниц от тысячи различных веб-страниц на сайте, что больше не требуется с помощью power query.Щелкните правой кнопкой мыши и удалите этот столбец, щелкните правой кнопкой мыши и удалите этот столбец. Здесь мне не нужно это время, поэтому я скажу, что это просто свидание, хорошо. И посмотрите, что он делает на каждом свидании, когда я его прохожу он возвращает максимум, минимум и осадки для Орландо. Каждая строка здесь ведет на другую веб-страницу. Только представьте, если бы это было не 15, а 5000 строк, вы бы настроили его на выполнение за ночь. Я писал для этого макросы. Фактически, одна из обсуждаемых веб-страниц - это создание макроса для очистки веб-страниц от тысячи различных веб-страниц на сайте, что больше не требуется с помощью мощного запроса.Возвращение максимума, минимума и осадков для Орландо. Каждая строка здесь ведет на другую веб-страницу. Только представьте, если бы это было не 15, а 5000 строк, вы бы настроили его на выполнение за ночь. Я писал для этого макросы. Фактически, одна из обсуждаемых веб-страниц - это создание макроса для очистки веб-страниц от тысячи различных веб-страниц на сайте, что больше не требуется с помощью мощного запроса.Возвращение максимума, минимума и осадков для Орландо. Каждая строка здесь ведет на другую веб-страницу. Только представьте, если бы это было не 15, а 5000 строк, вы бы настроили его на выполнение за ночь. Я писал для этого макросы. Фактически, одна из обсуждаемых веб-страниц - это создание макроса для очистки веб-страниц от тысячи различных веб-страниц на сайте, что больше не требуется с помощью мощного запроса.
Now, when I Close & Load, it's funny the preview here is showing me all the results. When I Close & Load, they're actually going to go do each query. And so right now we have preview rows loaded and it will take a good long time for this information to get updated. So, go to lunch, do something especially if you're doing more than 15 rows. And it's funny the preview is correct but they're still going out and could chunk, could chunk, could chunking through each individual row.
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
Загрузите образец файла здесь: Podcast2056.xlsm
