Power query excel как пользоваться

Что такое Power Query?

Power Query — это модуль преобразования данных и подсистемы подготовки данных. Power Query поставляется с графическим интерфейсом для получения данных из источников и Редактор Power Query для применения преобразований. Так как подсистема доступна во многих продуктах и службах, место хранения данных зависит от того, где использовался Power Query. С помощью Power Query можно выполнять обработку данных извлечения, преобразования и загрузки (ETL).

Схема с символичными источниками данных слева, передавая Power Query для преобразования в центре, а затем перейдя в четыре назначения справа: Microsoft Azure Data Lake служба хранилища, Microsoft Dataverse, Microsoft Excel и Microsoft Power BI.

Как Power Query помогает с получением данных

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

Существующий вызов	Как помогает Power Query?
Поиск и подключение к данным слишком сложно	Power Query позволяет подключаться к широкому спектру источников данных, включая данные всех размеров и фигур.
Возможности подключения к данным слишком фрагментированы	Согласованность возможностей запросов и четность возможностей запросов во всех источниках данных.
Данные часто необходимо изменить перед потреблением	Высоко интерактивный и интуитивно понятный интерфейс для быстрого и итеративного создания запросов по любому источнику данных.
Любая формирование является одноразовой и не повторяемой	При использовании Power Query для доступа к данным и преобразования вы определяете повторяемый процесс (запрос), который можно легко обновить в будущем, чтобы получить актуальные данные. В случае изменения процесса или запроса на учет базовых данных или изменений схемы можно использовать тот же интерактивный и интуитивно понятный интерфейс, который вы использовали при первоначальном определении запроса.
Объем (размеры данных), скорость (скорость изменения) и разнообразие (диапазон источников данных и фигур данных)	Power Query предлагает возможность работать с подмножеством всего набора данных для определения необходимых преобразований данных, что позволяет легко фильтровать и преобразовывать данные в управляемый размер. Запросы Power Query можно обновлять вручную или использовать возможности запланированного обновления в определенных продуктах (например, Power BI) или даже программно (с помощью объектной модели Excel). Так как Power Query обеспечивает подключение к сотням источников данных и более 350 различных типов преобразований данных для каждого из этих источников, вы можете работать с данными из любого источника и в любой форме.

Интерфейсы Power Query

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

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

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

В настоящее время доступны два интерфейса Power Query:

• Power Query Online — он находится в интеграции, таких как потоки данных Power BI, потоки данных Microsoft Power Platform, Фабрика данных Azure переупорядочение потоков данных и многое другое, предоставляющее возможности через веб-страницу.
• Power Query for Desktop — он найден в интеграции, например Power Query для Excel и Power BI Desktop.

Хотя два интерфейса Power Query существуют, оба они обеспечивают практически один и тот же интерфейс пользователя в каждом сценарии.

Преобразования

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

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

Потоки данных

Power Query можно использовать во многих продуктах, таких как Power BI и Excel. Однако использование Power Query в продукте ограничивает его использование только этим конкретным продуктом. Потоки данных — это версия службы, не зависящая от продукта, версия интерфейса Power Query, которая выполняется в облаке. С помощью потоков данных можно получить данные и преобразовать данные таким же образом, но вместо отправки выходных данных в Power BI или Excel можно хранить выходные данные в других вариантах хранения, таких как Dataverse или Azure Data Lake служба хранилища. Таким образом, вы можете использовать выходные данные потоков данных в других продуктах и службах.

Язык формул Power Query M

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

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

let Source = Exchange.Contents("xyz@contoso.com"), Mail1 = Source[Data], #"Expanded Sender" = Table.ExpandRecordColumn(Mail1, "Sender", , ), #"Filtered Rows" = Table.SelectRows(#"Expanded Sender", each ([HasAttachments] = true)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Subject] = "sample files for email PQ test") and ([Folder Path] = "\Inbox\")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",), #"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", , ), #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attachments", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Mail", each #"Transform File from Mail"([AttachmentContent])), #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", ), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Mail", Table.ColumnNames(#"Transform File from Mail"(#"Sample File"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",, , , , , , , , , >) in #"Changed Type" 

Где можно использовать Power Query?

В следующей таблице перечислены продукты и службы Майкрософт, где можно найти Power Query.

Продукт	Двигатель M 1	Power Query Настольный компьютер 2	Power Query Online 3	Потоки данных 4
Excel для Windows	Да	Да	No	No
Excel для Mac	Да	Да	No	No
Power BI	Да	Да	Да	Да
Power Apps	Да	No	Да	Да
Power Automate	Да	No	Да	Нет
Сервер отчетов Power BI	Да	Да	No	No
Azure Data Factory	Да	No	Да	Да
Фабрика данных в Microsoft Fabric	Да	No	Да	Да
SQL Server Integration Services	Да	No	No	No
SQL Server Analysis Services	Да	Да	No	No
Dynamics 365 Customer Insights	Да	No	Да	Да

1 модуль M	Базовый модуль выполнения запросов, который выполняет запросы, выраженные на языке формул Power Query («M»).
2 Power Query Desktop	Интерфейс Power Query, найденный в классических приложениях.
3 Power Query Online	Интерфейс Power Query, найденный в веб-браузерах.
4 потока данных	Power Query как услуга, которая выполняется в облаке и не зависит от продукта. Сохраненный результат можно использовать в других приложениях в качестве служб.

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

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

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

Четыре этапа Power Query

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

1. Подключения Подключение к данным в облаке, в службе или локально
2. Преобразования Формирование данных в соответствии с вашими потребностями, в то время как исходный источник остается неизменным
3. Объединить Интеграция данных из нескольких источников для получения уникального представления в данные
4. Нагрузки Завершите запрос, загрузите его на лист или в модель данных и периодически обновляйте его.

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

Подключение

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

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

Преобразовать

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

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

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

Редактор Power Query отслеживает все, что вы делаете с данными, записывая и помечая каждое преобразование или шаг, применяемые к данным. Независимо от того, является ли преобразование соединением данных, удалением столбца, слиянием или изменением типа данных, каждое преобразование можно просмотреть и изменить в разделе ПРИМЕНЕННЫЕ ШАГИ области Параметры запроса .

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

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

Объединить

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

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

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

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

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

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

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

Поддержка приложений для Power Query в Excel

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

Журнал Power Query в Excel

Интеграция get & transform Data (теперь называется Power Query) в Excel за прошедшие годы пережила ряд изменений.

Excel 2010 и 2013 для Windows

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

Microsoft 365

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

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

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

Excel 2016 и 2019 для Windows

Мы добавили тот же интерфейс Get & Transform Data на основе технологии 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 2016 и Excel 2019 для Mac отсутствует.

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

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

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

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

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

Прекращение использования соединителя данных Facebook

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

Критические обновления Excel для Windows

С июня 2023 г. для Power Query в Excel для Windows требуются следующие компоненты:

• Power Query в Excel для Windows использует платформу .NET Framework, но требуется версия 4.7.2 или более поздняя. Дополнительные сведения см. в разделе Обновление платформа .NET Framework.
• Power Query в Excel для Windows требуется, чтобы среда выполнения WebView2 продолжала поддерживать веб-соединитель данных (получение данных из Интернета). Дополнительные сведения см. в разделе Скачивание среды выполнения WebView2.

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

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

Об интеграции Power Query с Excel

Знать, в какой среде вы сейчас? 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 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.

Создание запроса

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

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

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

1. Импорт некоторых данных. Дополнительные сведения см. в том, как импортировать данные из внешних источников.
2. Выберем ячейку в данных и выберите запрос>изменить.

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

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

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

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

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

• Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как>«Получить данные» на Excel ленте.
• Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как>последние источники на Excel ленте.
• Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.

Загрузка запроса

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

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

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

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

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

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

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

1. В Excel выберите Запросы>запросы & Connections, а затем выберите вкладку Запросы.
2. В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите загрузить в. Появится диалоговое окно Импорт данных.
3. Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).

Редактирование запроса на этом или вехе

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

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

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

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

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

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

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

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

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

Изменение запроса таблицы в модели данных

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

1. Чтобы открыть модель данных, выберите Power Pivot >Управление.
2. В нижней части окна Power Pivot выберите вкладку нужной таблицы.

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

Загрузка запроса в модель данных занимает необычно много времени

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

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

Настройка параметров загрузки запроса

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

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

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

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

1. В редакторе Power Query выберите Файл >параметры и параметры >параметры запроса.
2. В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.
3. В разделе Загрузка Параметры запроса по умолчанию сделайте следующее:
   • Выберите Использовать стандартные параметры загрузки.
   • Выберите Указать настраиваемые параметры загрузкипо умолчанию, а затем выберите или отобирать параметр Загрузить на таблицу или Загрузить в модель данных.

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

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

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

Создание формул Power Query в Excel

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

Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.

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

Давайте создадим сначала простую формулу, а затем сложную.

• Создание простой формулы
• Создание сложной формулы

Создание простой формулы

В этом примере мы с помощью формулы Text.Proper() преобразуем текстовое значение, сделав первые буквы слов прописными.

1. На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.
2. В строке формул редактора запросов введите = Text.Proper(«text value») , а затем нажмите клавишу ВВОД или щелкните значок «Ввод».
3. Power Query отобразит результаты в области результатов формул.
4. Чтобы увидеть результат на листе Excel, нажмите кнопку Закрыть и загрузить.

Результат будет выглядеть на листе вот так:

В редакторе запросов можно также создавать сложные формулы.

Создание сложной формулы

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

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

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

Исходная таблица выглядит так:

Результирующая таблица должна иметь такой вид:

Рассмотрим шаги формулы запроса, с помощью которых можно изменить исходную таблицу и сделать первые буквы слов в столбце ProductName прописными.

Пример составления сложного запроса с помощью расширенного редактора

Чтобы очистить исходную таблицу, создайте шаги формулы запроса с помощью расширенного редактора. Создадим каждый шаг формулы запроса, чтобы показать, как можно построить сложный запрос. Все шаги формулы запроса перечислены ниже. При создании сложного запроса сделайте следующее:

• Создайте последовательность шагов формулы запроса, которая начинается с оператора let. Обратите внимание: в языке формул Power Query учитывается регистр.
• Каждый шаг формулы запроса строится на основе одного из предыдущих шагов (ссылается на него по имени).
• Результат шага формулы запроса выводится с помощью оператора in. Обычно последний шаг запроса используется как окончательный результат оператора in для набора данных.

Шаг 1. Открытие расширенного редактора

1. На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.
2. В редакторе запросов выберите команду Расширенный редактор.
3. Откроется окно Расширенный редактор.

Шаг 2. Определение исходного источника

В расширенном редакторе сделайте следующее:

1. Используйте оператор let, определяющий формулу Источник = Excel.CurrentWorkbook(). В результате источником данных станет таблица Excel. Дополнительные сведения о формуле Excel.CurrentWorkbook() см. в статье Excel.CurrentWorkbook.
2. Назначьте имя шага Источник результату оператора in.

let Source = Excel.CurrentWorkbook()[Content] in Source

Сложный запрос будет выглядеть в расширенном редакторе следующим образом:

Вот как можно просмотреть результаты на листе:

1. Нажмите кнопку Готово.
2. На ленте редактора запросов нажмите кнопку Закрыть и загрузить.

На листе результат будет выглядеть вот так:

Шаг 3. Преобразование первой строки в заголовки

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

1. Добавьте к шагам формулы запроса формулу #»Использовать первую строку в качестве заголовков» = Table.PromoteHeaders() и сошлитесь на «Источник» как на источник данных. Дополнительные сведения о формуле Table.PromoteHeaders() см. в статье Table.PromoteHeaders.
2. Назначьте имя шага #»Использовать первую строку в качестве заголовков» результату оператора in.

let Source = Excel.CurrentWorkbook()[Content], #"First Row as Header" = Table.PromoteHeaders(Source) in #"First Row as Header"

На листе результат будет выглядеть вот так:

Шаг 4. Изменение каждого значения в столбце (замена первых букв в словах прописными)

Чтобы преобразовать каждое значение столбца ProductName, сделав первые буквы слов прописными, воспользуйтесь формулой Table.TransformColumns() и сошлитесь на шаг формулы запроса «Использовать первую строку в качестве заголовков». Для этого воспользуйтесь расширенным редактором.

1. Добавьте к шагам формулы запроса формулу #»Выполнена капитализация каждого слова» = Table.TransformColumns() и сошлитесь на шаг #»Использовать первую строку в качестве заголовков» как на источник данных. Дополнительные сведения о формуле Table.TransformColumns() см. в статье Table.TransformColumns.
2. Назначьте имя шага #»Выполнена капитализация каждого слова» результату оператора in.

let Source = Excel.CurrentWorkbook()[Content], #"First Row as Header" = Table.PromoteHeaders(Source), #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",>) in #"Capitalized Each Word"

В окончательном результате каждое значение столбца ProductName будет изменено (первые буквы слов станут прописными). На листе это будет выглядеть так:

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