как получить, использовать и автоматически обновлять

Это подробное руководство по Power Query — ваша отправная точка для освоения сложных преобразований данных в Excel. Вы научитесь импортировать данные из различных источников, преобразовывать и объединять их в соответствии с вашими требованиями, писать формулы на языке M, автоматизировать обновление запросов и многое другое.

В постоянно развивающемся мире данные необходимы практически для всего, что мы делаем: от принятия важных бизнес-решений до ведения дружеских дебатов о лучших начинках для пиццы. Однако информация часто поступает из разных источников, в разных форматах и иногда кажется организованной, как сундук с игрушками малыша. Итак, вот в чем загадка: как мы можем импортировать, преобразовывать и анализировать данные в Excel, не тратя часы на ручные манипуляции или написание сложных формул? Решением является Power Query, бесценный инструмент для любого аналитика данных, экономящий часы времени, уменьшающий количество ручных ошибок и превращающий неуправляемый информационный хаос в гармоничную симфонию идей.

Что такое Power Query в Excel?

Power Query, также известный как Получить и трансформировать— это многоцелевой инструмент в программах Microsoft Excel и Power BI, который позволяет импортировать данные из самых разных источников и преобразовывать эти данные в соответствии с вашими конкретными потребностями.

Впервые он был представлен как надстройка для Excel 2010, а затем стал встроенной функцией в Excel 2016 и последующих версиях. Power Query (PQ) предоставляет удобный интерфейс для подключения к различным источникам данных, формирования, объединения и очистки данных, а затем их загрузки в Excel для углубленного анализа и составления полных отчетов в виде сводных таблиц, диаграмм и информационных панелей. , и более.

По сути, Power Query дает пользователям Excel возможность легко извлекать, манипулировать и систематизировать данные из обширного массива источников, что меняет правила игры для профессионалов, работающих со сложными наборами данных.

Для чего можно использовать Power Query?

Power Query — универсальный инструмент с множеством приложений. Вот некоторые распространенные варианты использования:

Извлечение данных. Подключайтесь к различным источникам данных, включая базы данных, файлы Excel, текстовые файлы и онлайн-сервисы.

Преобразование данных. Доступен широкий спектр функций преобразования данных, позволяющих преобразовать ваши данные в более подходящую для анализа структуру, например фильтрацию, сортировку, группировку, объединение, разделение столбцов на несколько полей, создание вычисляемых столбцов и многое другое.

Очистка данных. Качество данных имеет решающее значение для точного анализа. PQ предоставляет инструменты для выявления и исправления ошибок, удаления дубликатов, обрезки лишних пробелов, заполнения пропущенных значений и стандартизации форматов, гарантируя чистоту и надежность ваших данных.

Объединение данных. PQ упрощает объединение и объединение данных из разных мест в единый связный набор данных, облегчая комплексный анализ.

Автоматизация рабочих процессов. Power Query записывает каждый ваш шаг, создавая запрос, который позже можно легко изменить или повторно использовать. Это особенно полезно при работе с повторяющимися источниками данных или когда вы хотите поддерживать актуальность анализа с минимальными усилиями. Кроме того, вы можете обновить свой запрос, чтобы получить последние данные из источника в любое время, по требованию или по расписанию.

Теперь, когда мы выяснили, что такое Power Query и его потенциальные применения, давайте посмотрим, как вы можете использовать эту полезную функцию в своем Excel.

Как получить Power Query в Excel

Инструмент Power Query доступен во всех современных версиях, начиная с Excel 2010 и заканчивая Excel 365. В зависимости от конкретной версии, которую вы используете, вам может потребоваться установить его как надстройку или получить к нему доступ непосредственно с ленты.

Примечание. В Excel 2007 и более ранних версиях Power Query не поддерживается.

Как открыть Power Query в Excel 2016 – 365

В более новых версиях Excel Power Query — это встроенная функция, доступная без каких-либо дополнительных установок. Его можно найти на Данные вкладка под Получить и преобразовать данные группа. Чтобы начать использовать его возможности, просто нажмите на любую из команд в этой группе.

Как добавить Power Query в Excel 2013 и 2010

В более старых версиях Excel Power Query не включен по умолчанию, но его можно загрузить и установить в качестве надстройки с сайта. Веб-сайт Майкрософт.

После установки надстройки и перезапуска Excel вы должны увидеть новую вкладку на ленте с надписью Мощность запроса. Чтобы начать импорт данных из различных источников, нажмите одну из этих кнопок: Из файла, Из базы данных, Из Интернета или Из других источников.

Кончик. Обязательно загрузите пакет, соответствующий вашей версии Excel и операционной системе. Чтобы проверить вашу версию и разрядность (32-битная или 64-битная), перейдите по ссылке Файл > Счет > О Excel.

Редактор Power Query в Excel

Сердце и душа всех преобразований данных находится в редакторе Power Query. Это отдельный графический интерфейс Excel, в котором обрабатываются ваши запросы. Это основное рабочее пространство, в котором вы будете проводить большую часть своего времени, уточняя и изменяя данные.

Вы можете получить доступ к редактору двумя способами:

На Данные вкладка, в Получить и трансформировать группу, нажмите Получить данные и выберите Запустите редактор Power Query..
Альтернативно, на Данные вкладку, нажмите кнопку Запросы и связи кнопку, чтобы открыть эту панель. Затем дважды щелкните запрос на панели, чтобы запустить редактор.

Когда вы подключаетесь к источнику данных или создаете новый запрос, Редактор открывается автоматически.

Вот шесть основных элементов редактора Power Query:

Лента. В верхней части редактора вы найдете ленту, на которой различные команды и параметры упорядочены по пяти основным вкладкам: Файл, Дом, Трансформировать, Добавить столбеци Вид. На каждой вкладке есть разные группы команд, соответствующие конкретной задаче, которую вы хотите выполнить.
Запросы. Это своего рода панель навигации в левой части окна редактора, на которой отображаются все запросы в вашей книге. Эту панель можно использовать для переименования, дублирования, ссылки, удаления, изменения порядка или организации запросов в группы. Просто щелкните запрос правой кнопкой мыши, чтобы просмотреть доступные варианты.
Предварительный просмотр данных. Панель предварительного просмотра данных, расположенная спереди и в центре, предоставляет снимок вашего набора данных после применения каждого шага запроса в режиме реального времени. Хотя его основная цель — облегчить проверку данных, он также позволяет выполнять некоторые базовые преобразования, такие как сортировка и фильтрация столбцов, изменение типов данных и т. д.
Характеристики. В правой части окна редактора, в Настройки запроса панель, вы найдете Характеристики раздел. Он показывает свойства выбранного запроса, такие как его имя, описание и настройки загрузки. Нажмите кнопку Все объекты недвижимости ссылку для просмотра и изменения этих параметров по мере необходимости.
Применяемые шаги: Чуть ниже Характеристики В разделе «Примененные шаги» имеется область «Примененные шаги», в которой представлен хронологический список всех действий, которые вы предприняли во время преобразования данных. Вы можете использовать этот список для просмотра, редактирования, изменения порядка или удаления любого шага вашего запроса. Вы также можете нажать на определенный шаг, чтобы увидеть, как он повлияет на ваши данные, в области предварительного просмотра данных.
Панель формул. Это полоса в верхней части области предварительного просмотра данных, на которой отображается формула или выражение для выбранного шага запроса. Вы можете использовать эту панель для редактирования или написания собственных формул с использованием языка Power Query M.

Расширенный редактор Power Query

Расширенный редактор — это текстовый интерфейс, который позволяет писать и редактировать код M — язык, лежащий в основе Power Query. Думайте об этом как о своем VIP-пропуске за кулисы трансформации Power Query. Стандартный интерфейс PQ позволяет изменять форму данных с помощью действий «укажи и щелкни», а расширенный редактор дает вам точный контроль над преобразованиями данных.

Каждое действие, которое вы выполняете в Power Query, генерирует соответствующий код в фоновом режиме с использованием языка M. Вы можете просмотреть и отредактировать этот код в расширенном редакторе. Чтобы открыть его, нажмите кнопку Расширенный редактор кнопка на Дом вкладка, в Запрос группа.

Как использовать Power Query в Excel

В этом разделе представлен обзор того, как использовать Power Query для анализа данных. Вы узнаете основные этапы импорта, преобразования, объединения и экспорта данных. Эти шаги:

Получить данные – подключайтесь к различным источникам данных и загружайте их в Power Query.
Трансформировать – очистите и измените данные, чтобы адаптировать их к вашим конкретным требованиям.
Объединить – объединять данные из различных источников в единый организованный набор данных.
Написание формул – создавать собственные формулы с помощью языка формул Power Query.
Нагрузка – экспортируйте окончательную таблицу данных в Excel и обновите ее вручную или автоматически.

Получить данные

Чтобы начать работать с данными в Power Query, вам необходимо импортировать их из источника. PQ поддерживает множество различных источников данных, таких как файлы, базы данных, онлайн-сервисы и т. д.

Доступ к трем наиболее распространенным источникам можно получить непосредственно с ленты Excel — на вкладке Данные вкладка, в Получить и преобразовать данные группа. Этими первоисточниками являются:

Из текста/CSV — используйте эту опцию, если ваши данные хранятся в текстовом файле или файле CSV.
Из Интернета — идеально подходит для импорта данных непосредственно с веб-страницы.
Из таблицы/диапазона — подходит для данных, которые уже есть в Excel и организованы в таблицу или диапазон.

Если вам необходимо импортировать данные из других источников, нажмите кнопку Получить данные кнопку, а затем изучите дополнительные параметры:

Из файла — импортируйте файлы различных типов, такие как листы Excel, TXT/CSV, XML, JSON и PDF.
Из базы данных — подключение к таким базам данных, как SQL Server или Microsoft Access.
Из Azure — доступ к данным из базы данных SQL Azure, Azure Data Lake Storage и других служб Azure.
Из Power Platforms — импортируйте данные из служб Microsoft Power BI.
Из других источников — эта категория предлагает еще больше возможностей, включая данные из таблиц/диапазонов Excel, из Интернета, каналов OData, ODBC, OLEDB и других.

Например, чтобы использовать файл CSV в качестве источника данных, вы можете нажать кнопку От Текст/CSV кнопку на ленте или пройдите более длинный путь: Получить данные > Из файла > Из текста/CSV. Если вам нужно объединить несколько файлов CSV, сохраните их в одной папке и выберите Из папки вариант.

На следующем экране вы увидите предварительный просмотр импортированных данных, а также следующие варианты:

Объединить (только для нескольких файлов) — позволяет объединять разные наборы данных из разных источников и типов файлов.
Загрузить — позволяет загружать данные непосредственно на лист Excel в виде таблицы, сводной таблицы или соединения.
Преобразование данных — открывает редактор Power Query, где вы можете выполнять различные преобразования, такие как фильтрация, разделение, группировка, поворот и т. д. Это вариант, который вы будете использовать чаще всего.
Отмена — позволяет отменить процесс импорта и закрыть диалоговое окно.

Если вы хотите изменить форму своих данных, Преобразование данных вариант является логическим следующим шагом.

Преобразование данных

Как только ваши данные будут загружены в Power Query, вы сможете уточнить их в соответствии со своими потребностями. Все преобразования данных выполняются в отдельном интерфейсе, называемом Редактор Power Query, где у вас есть четыре основные вкладки для изучения. Вы можете думать о преобразовании данных как о скульптуре. Вы начинаете с исходного материала, которому придаёте форму, соответствующую вашим целям и позволяющую создавать привлекательные отчёты и информационные панели.

Вот некоторые распространенные преобразования, которые можно выполнить с помощью Power Query:

Сортируйте и фильтруйте. Упорядочивайте данные в порядке возрастания или убывания и отфильтровывайте ненужные строки, чтобы сосредоточиться на наиболее важной информации.
Добавляйте, удаляйте или дублируйте столбцы. Измените свой набор данных, добавляя новые столбцы, удаляя ненужные или дублируя существующие столбцы, когда это необходимо.
Изменить тип данных. Вы можете указать тип данных в каждом столбце, например текст, число, дату, время и т. д.
Транспонировать строку в столбец и наоборот. Измените ориентацию данных, преобразуя строки в столбцы для лучшего анализа и визуализации.
Удалите повторяющиеся строки. Вы можете исключить любые строки с одинаковыми значениями во всех столбцах, чтобы уменьшить размер данных и избежать двойного учета.
Обрезать и почистить. Удалите начальные и конечные пробелы из текстовых значений, а также любые дополнительные символы, которые не являются частью данных, чтобы улучшить качество и читаемость вашего набора данных.
Извлечь значения. Извлекайте части текстовых значений на основе разделителя, положения или шаблона. Например, вы можете извлечь имя из столбца полного имени или имя домена из столбца адреса электронной почты.
Разделить столбец. Разделите столбец на несколько столбцов на основе разделителя или определенного шаблона. Например, вы можете разделить столбец даты на столбцы года, месяца и дня.
Замените или удалите значения или ошибки. Вы можете заменить любые значения или ошибки в данных другим значением, например пустым, нулевым или пользовательским значением. Вы также можете удалить любые строки, содержащие ошибки или пропущенные значения.
Добавьте условный столбец. Создавайте новые столбцы на основе заранее определенных условий, что позволяет осуществлять динамическую категоризацию данных. Например, вы можете создать столбец, который присваивает категорию на основе числового диапазона.
Добавьте столбец из примера. Используйте примеры, которые помогут Power Query создавать новые столбцы с нужными шаблонами данных. PQ попытается понять логику ваших примеров и применить ее к остальным строкам.
Группируйте и суммируйте данные. Объединяйте данные по группам, рассчитывайте суммы, средние значения или другие статистические показатели для более глубокого понимания.

Эти преобразования позволят вам превратить ваши данные в произведение искусства, которое будет не только функциональным, но также элегантным и проницательным. Подробные инструкции можно найти в этой статье: Как использовать Power Query в Excel — практические примеры.

Примечание. Все изменения, вносимые вами в редакторе, вместе образующие запрос, представляют собой новое представление исходных данных. При обновлении запроса каждый шаг выполняется автоматически, что устраняет необходимость вручную повторять одни и те же действия. Однако, чтобы использовать уточненные данные в Excel, вам необходимо загрузить его в свою книгу.

Объединить и объединить

Одна из наиболее полезных функций Power Query — возможность объединять данные из разных источников в одну таблицу. Есть два основных способа сделать это: добавить и объединить.

Добавление добавляет строки из одной таблицы в другую, если они имеют одинаковые столбцы. Например, вы можете добавить данные о продажах из разных регионов или месяцев в одну таблицу.
Слияние объединяет две таблицы на основе одного или нескольких общих столбцов (уникальных идентификаторов), как соединение SQL. Например, вы можете объединить данные студентов из разных курсов на основе имени или столбца идентификатора студента.

Чтобы объединить данные в редакторе Power Query, используйте Объединение запросов или Добавить запросы команда, на Дом вкладка, в Объединить группа.

Чтобы объединить данные, которых еще нет в Power Query, щелкните Получить данные > Объединить запросы на Данные вкладку ленты Excel, а затем выберите Объединить или Добавить.

Подробный комплексный пример объединения двух таблиц по общему столбцу см. в разделе Как объединить таблицы с помощью Power Query.

Написание формул в Power Query

Подобно использованию функций и формул в таблицах Excel, PQ также имеет собственный набор функций для выполнения различных вычислений. Эти формулы написаны на языке формул Power Query, также известном как M. А теперь хорошая новость: вам не нужно изучать тонкости синтаксиса языка M для создания собственных формул. В отличие от традиционного кодирования, Power Query делает его удивительно простым и удобным для пользователя благодаря визуальному интерфейсу.

Давайте возьмем пример. Представьте, что вы хотите создать столбец, который рассчитывает чистую сумму пожертвования после налогового вычета на основе значений в Пожертвование и Ставка налога столбцы. Шаги:

Перейти к Добавить столбец вкладку и нажмите Пользовательский столбец.
в Пользовательский столбец диалоговом окне введите «Чистое пожертвование» или любое имя, которое вы хотите для Новое имя столбца
Пользовательская формула столбца В поле вы пишете саму формулу. Итак, введите в него следующую формулу:
“=”[Donation] * (1 – [Tax Rate])
Чтобы обратиться к определенному столбцу в формуле, выберите его имя в разделе Доступные столбцы в правой части диалогового окна и нажмите Вставлятьили просто дважды щелкните имя столбца.
Когда вы вводите формулу, Power Query проверяет ее и, если ошибок не обнаружено, отображает сообщение «Синтаксическая ошибка не обнаружена». Если в вашей формуле есть ошибка, появится сообщение об ошибке с указанием типа ошибки.
Нажмите «ОК», и формула будет применена ко всем строкам нового столбца.

Вот и все! Вы успешно создали настраиваемый столбец с формулой в Power Query. В строке формул показан ее синтаксис:

Загрузить данные в Excel

Все преобразования, которые вы применяете к своим подключениям к данным, в совокупности составляют запрос, который представляет собой новое представление исходного (и неизмененного) источника данных. При обновлении запроса каждый шаг выполняется автоматически. Запросы заменяют необходимость вручную подключать и формировать данные в Excel.

После того как вы выполнили необходимые преобразования данных в Power Query, последним шагом будет загрузка полученных данных в книгу Excel. У вас есть два варианта:

В редакторе Power Query используйте Закрыть и загрузить команды на Дом вкладка.
Из Запросы и связи панель листа Excel — щелкните запрос правой кнопкой мыши и выберите Загрузить в.

Следующие шаги показывают, как это сделать из редактора Power Query:

в Запрос панели слева выберите запрос, который хотите загрузить.
На Дом вкладка, в Закрывать группу, щелкните стрелку рядом с Закрыть и загрузитьи выберите один из этих вариантов:
- Закрыть и загрузить – загружает запрос в виде таблицы на новый лист (настройки по умолчанию).
- Закрыть и загрузить в… – позволяет указать формат и расположение вывода.
Если вы выберете первый вариант, процесс загрузки уже завершен. Если вы выберете второй вариант, переходите к следующему шагу.
в Импортировать данные В диалоговом окне выберите способ импорта данных (таблицу, сводную таблицу или сводную диаграмму) и место их размещения (на новом или существующем листе). Затем нажмите ХОРОШО чтобы завершить процесс.

Обновление Excel Power Query

При работе с Power Query вам может потребоваться время от времени обновлять запросы, чтобы получать самые свежие данные. Вот несколько способов сделать это:

Обновить предварительный просмотр Power Query

Чтобы обновить данные, отображаемые в предварительном просмотре Power Query, просто щелкните значок Обновить предварительный просмотр кнопка на Дом вкладка редактора:

Обновить предварительный просмотр – обновляет результаты предварительного просмотра для текущего запроса.
Обновить все – обновляет результаты предварительного просмотра по всем запросам в редакторе.

Это перезагрузит данные из источника, чтобы отразить любые изменения, внесенные в исходные данные. Все изменения, внесенные вами в шаги запроса, будут автоматически применены к новым данным.

Обновление результатов Power Query в Excel

Чтобы обновить результаты Power Query, загруженные в книгу Excel, выполните одно из следующих действий:

Чтобы обновить конкретный запрос, щелкните правой кнопкой мыши имя запроса в Запросы и связи панель и выберите Обновить.
Чтобы обновить все запросы в книге, нажмите сочетание клавиш Ctrl + Alt + F5. Или нажмите кнопку Обновить все кнопка на Данные вкладка, в Запросы и связи

Как автоматически обновить Power Query, не открывая файл

Если вам нужно, чтобы Power Query в Excel обновлялся автоматически без необходимости открывать исходный файл, вы можете запланировать эти обновления через определенные промежутки времени. Вот как это настроить:

На Данные вкладка, нажмите на Запросы и связи. Это откроет Панель «Запросы и соединения» в правой части окна Excel.
На панели щелкните правой кнопкой мыши запрос, автоматическое обновление которого вы хотите запланировать, а затем выберите Характеристики.
в Свойства запроса диалоговое окно, перейдите в Применение вкладку и установите флажок с надписью Обновлять каждые … минут и введите количество минут.
Чтобы обновить запрос при открытии файла Excel, выберите значок Обновить данные при открытии файла флажок (по умолчанию не установлен).
Нажмите ОК, чтобы сохранить изменения.

Вот и все! С этого момента ваш запрос будет обновляться автоматически каждый раз, когда вы открываете книгу, или через указанный интервал времени, в зависимости от того, что наступит раньше. Таким образом, вы всегда сможете иметь самые последние данные в файле Excel, не обновляя их вручную.

Это основные принципы Excel Power Query — вашего секретного оружия, позволяющего заставить данные работать на вас. Теперь вы можете легко подключаться к различным источникам данных, преобразовывать, объединять и очищать данные, а затем загружать их в Excel или Power BI. И не забудьте о вишенке на торте — автоматических обновлениях, благодаря которым ваши данные всегда будут актуальными, даже когда вы не смотрите 🙂