Как настроить power query excel 2016



Сведения о Power Query в Excel

С помощью Power Query (в Excel это называется Get & Transform) можно импортировать или подключиться к внешним данным, а затем сформировать эти данные, например удалить столбец, изменить тип данных или объединить таблицы в соответствии со своими потребностями. Затем можно загрузить запрос в Excel для создания диаграмм и отчетов. Периодически можно обновлять данные, чтобы сделать их актуальными. Power Query доступны в трех приложениях Excel: Excel для Windows, Excel для Mac и Excel для Интернета. Общие сведения о всех разделах справки Power Query см. в Power Query справки Excel.

Существует четыре этапа использования Power Query.

Подключения Подключение к данным в облаке, в службе или локально

Преобразования Формирование данных в соответствии со своими потребностями, в то время как исходный источник остается неизменным

Объединить Интеграция данных из нескольких источников для получения уникального представления в данных

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

В следующих разделах каждый этап рассматривается более подробно.

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

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

Преобразование данных означает их изменение в соответствии с требованиями к анализу данных. Например, можно удалить столбец, изменить тип данных или отфильтровать строки. Каждая из этих операций представляет собой преобразование данных. Этот процесс применения преобразований (и объединения) к одному или нескольким наборам данных также называется формированием данных.

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

Power Query использует выделенное окно с именем Редактор Power Query для упрощения и отображения преобразований данных. Чтобы открыть Редактор Power Query, выберите команду «Запустить Редактор запросов» в группе «Получение данных& Преобразование данных», но она также откроется при подключении к источнику данных, создании запроса или загрузке запроса.

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

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

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

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

Добавить Операция добавления создает новый запрос, содержащий все строки из первого запроса, за которым следуют все строки из второго запроса. Можно выполнить два типа операций добавления:

Промежуточное добавление Создает новый запрос для каждой операции добавления.

Встроенная надстройка Добавляет данные в существующий запрос, пока не будет достигнут окончательный результат.

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

Существует два основных способа загрузки запросов в книгу:

В Редактор Power Query можно использовать команды «Закрыть» и «Загрузить» в группе «Закрыть» на вкладке «Главная«.

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

Читайте также:  Как настроить жпс на андроид магнитоле

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

Более десяти лет Power Query в Excel для Windows. Теперь Excel расширяет возможности Power Query в Excel для Mac и добавляет поддержку в Excel для Интернета. Это означает, что мы Power Query на трех основных платформах и демонстрируем популярность и функциональность Power Query среди клиентов Excel. Следите за будущими объявлениями о стратегии развития Microsoft 365и новых возможностях Excel для Microsoft 365.

Интеграция Get & Transform Data (теперь называется Power Query) в Excel прошла через несколько лет изменений.

Excel 2010 и 2013 для Windows

В Excel 2010 для Windows мы впервые представили Power Query и она была доступна в виде бесплатной надстройки, которую можно скачать здесь: скачайте надстройку Power Query надстройку. После включения Power Query функции были доступны на Power Query на ленте.

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

Этот интерфейс включает расширенные функции импорта данных, переупорядоченные команды на вкладке «Данные», новую панель «Запросы & Подключение», а также возможность эффективного формирования данных путем сортировки, изменения типов данных, разделения столбцов, агрегирования данных и т. д.

Этот новый интерфейс также заменил старые мастера импорта устаревших данных в команде «Данные » в группе «Получение внешних данных «. Однако доступ к ним по-прежнему можно получить в диалоговом окне «Параметры Excel» (выберите «Параметры файлов >»> « > Показать устаревшие мастера импорта данных)».

Excel 2016 2019 для Windows

Мы добавили то же & преобразования данных на основе Power Query технологии, что и в Microsoft 365.

Excel для Microsoft 365 для Mac

В 2019 году мы начали процесс поддержки Power Query в Excel для Mac. С тех пор мы добавили возможность обновлять Power Query из файлов TXT, CSV, XLSX, JSON и XML. Мы также добавили возможность обновлять данные из SQL Server и из таблиц & диапазонов в текущей книге.

В октябре 2019 г. мы добавили возможность обновлять существующие Power Query и использовать VBA для создания и изменения новых запросов.

В январе 2021 г. мы добавили поддержку обновления Power Query запросов из источников OData и SharePoint.

Дополнительные сведения см. в Power Query в Excel для Mac.

Примечание В Power Query Excel 2019 для Mac Excel 2016 не поддерживается.

Каталог данных устаревания

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

1 августа 2018 г. мы перестали подключать новых клиентов к Каталог данных.

3 декабря 2018 г. пользователям не удалось поделиться новыми или обновленными запросами в Каталог данных.

4 марта 2019 г. работа Каталог данных прекращена. После этой даты мы рекомендуем скачать общие запросы, чтобы вы могли продолжать использовать их за пределами Каталог данных, используя параметр «Открыть» в области задач «Мои Каталог данных Запросы».

Power Query нерекомендуемой надстройки

В начале 2019 года мы официально не рекомендуем использовать надстройку Power Query, которая требуется для Excel 2010 и 2013 для Windows. В качестве предоставленного решения вы по-прежнему можете использовать надстройку, но это может измениться позже.

Отключен соединитель данных Facebook

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

Источник

Создание, загрузка и изменение запроса в Excel (Power Query)

Power Query предлагает несколько способов создания и загрузки power querys в книгу. Вы также можете настроить параметры загрузки запроса по умолчанию в окне Параметры запроса.

Совет Чтобы упросить, сформированы ли данные на этом плане с помощью Power Query, выберите ячейку с данными, а если появится вкладка Лента контекстного запроса, данные загружаются из Power Query.

Читайте также:  Как настроить вибрацию на апл вотч

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

Знакомые Excel, лента и сетка

Лента редактора Power Query и предварительный просмотр данных

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

Переименование я вкладок на таблицах Ямы лучше переименовывать по своему смыслу, особенно если их много. Особенно важно пояснить разницу между данными и данными, загруженными из редактора Power Query. Даже если у вас всего два листа: с таблицей Excel «Лист1»и запросом, созданным путем импорта таблицы Excel Таблица1,ее легко запутать. Всегда имеет смысл изменить названия ярлыков по умолчанию на более понятное. Например, переименуйте Лист1 в Таблицу данных и Таблицу1 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.

Вы можете создать запрос из импортируемых данных или пустой запрос.

Создание запроса из импортируемых данных

Это самый распространенный способ создания запроса.

Выберем ячейку в данных и выберите запрос> изменить.

Создание пустого запроса

Вы можете начать с нуля. Это можно сделать двумя способами.

Выберите Данные> Получить данные > из других источников > пустой запрос.

Выберите Данные> получить данные > запуск редактора Power Query.

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

Кроме того, можно выбрать команду На главная в группе Новый запрос. Выполните одно из указанных ниже действий.

Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> «Получить данные» на Excel ленте.

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

Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.

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

Загрузка запроса из редактора Power Query

В редакторе Power Query сделайте следующее:

Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузить.

Чтобы загрузить данные в модель данных, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузка.

В диалоговом окне Импорт данных выберите добавить эти данные в модель данных.

Совет Иногда команда «Загрузить в» неатривна или отключена. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку Закрыть & Загрузить, на новом > запросы данных & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите загрузить в . Кроме того, на ленте редактора Power Query выберите запрос> Загрузить в.

Загрузка запроса из области «Запросы и подключения»

В Excel может потребоваться загрузить запрос на другой таблицу или в модель данных.

В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.

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

Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).

Существует несколько способов изменить запрос, загруженный на таблицу.

Изменение запроса на Excel данных

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

Изменение запроса в области «Запросы & подключения»

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

В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.

В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.

Изменение запроса в диалоговом окне «Свойства запроса»

В Excel выберите Data> Data & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства ,выберите вкладку Определение в диалоговом окне Свойства и нажмите кнопку Изменить запрос.

Совет Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос.

Читайте также:  Как настроить себя на лагерь

Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.

Чтобы открыть модель данных, выберите Power Pivot > Управление.

В нижней части окна Power Pivot выберите вкладку нужной таблицы.

Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.

Обратите внимание на имя таблицы.

Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.

Выберите > подключения & свойства > Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.

Завершив внесение изменений в редакторе Power Query, выберите файл > Закрыть & загрузить.

Запрос на этом и в таблице в модели данных обновляются.

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

Корпорация Майкрософт знает об этой проблеме и находится в стадии исследования.

Вы можете загрузить Power Query:

На один из них. В редакторе Power Query выберите Home> Close & Load > Close & Load.

В модель данных. В редакторе Power Query выберите Home> Close & Load > Close & Load To.

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

Кроме того, динамически переопределять стандартные параметры запроса можно в диалоговом окне Импорт, которое отображается после выбора параметра & Загрузить в.

Глобальные параметры, которые применяются во всех книгах

В редакторе Power Query выберите Файл > параметры и параметры > параметры запроса.

В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.

В разделе Загрузка Параметры запроса по умолчанию сделайте следующее:

Выберите Использовать стандартные параметры загрузки.

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

Совет В нижней части диалогового окна можно выбрать восстановить параметры по умолчанию, чтобы вернуться к настройкам по умолчанию.

Параметры книги, которые применяются только к текущей книге

В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.

Выполните одно или несколько из указанных ниже действий.

В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.

По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.

В области Связивыберите или отоберем создать связи между таблицами при первом добавлении в модель данных.

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

В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.

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

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

По умолчанию предварительный просмотр данных загружается в фоновом режиме. Если вы хотите сразу же видеть все данные, отобираем этот параметр.

Источник

Поделиться с друзьями
Инструкции360