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

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

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

Части редактора запросов

  1. Лента Редактор Power Query, используемая для формирования данных

  2. Область "Запросы", используемая для поиска источников данных и таблиц

  3. Контекстные меню, удобные сочетания клавиш для команд на ленте

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

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

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

Пример формулы в редакторе запросов

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

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

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

Последовательность действий

  1. Чтобы импортировать данные, выберите Данные > из Интернета, введите "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" в поле URL-адрес и нажмите кнопку ОК.

  2. В диалоговом окне Навигатор выберите таблицу Результаты [Изменить] слева, а затем выберите Преобразовать данные внизу. Откроется редактор Power Query.

  3. Чтобы изменить имя запроса по умолчанию, в области Параметры запроса в разделе Свойства удалите "Результаты [Изменить]" и введите "UEFA champs".

  4. Чтобы удалить ненужные столбцы, выделите первый, четвертый и пятый столбцы, а затем выберите Главная > Удалить столбец > Удалить другие столбцы.

  5. Чтобы удалить нежелательные значения, выберите Столбец1, выберите Главная > Заменить значения, введите "details" в поле Значения для поиска, а затем нажмите кнопку ОК.

  6. Чтобы удалить строки со словом "Год", щелкните стрелку фильтра в столбце Column1, очистите поле проверка рядом с полем "Год", а затем нажмите кнопку ОК.

  7. Чтобы переименовать заголовки столбцов, дважды щелкните каждый из них, а затем измените "Column1" на "Year", "Column4" на "Winner" и "Column5" на "Final Score".

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

Result (Результат)

Результаты пошагового руководства — первые несколько строк

В следующей таблице приведена сводка по каждому примененным шагам и соответствующая формула.

Шаг и задача запроса

Формула

Source

Подключение к веб-источнику данных

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigation

Выбор таблицы

=Source{2}[Data]

Changed Type

Изменение типов данных (что Power Query выполняется автоматически)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

Удалены другие столбцы

Удаление ненужных столбцов

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Замененное значение

Замена значений для очистки значений в выбранном столбце

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Отфильтрованные строки

Фильтрация значений в столбце

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Переименованные столбцы

Изменены заголовки столбцов, чтобы они были значимыми

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Важно    Внимательно изменяйте шаги Источник, Навигация  и Измененный тип, так как они создаются Power Query для определения и настройки источника данных.

Отображение или скрытие строки формул

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

  • Выберите Вид > макет > строке формул.

Edit формула в строке формул

  1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

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

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

    Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Щелкните значок Ввод Значок ВВОД слева от строки формул в Power Query или нажмите клавишу ВВОД, чтобы просмотреть новые результаты, отображаемые в режиме предварительного просмотра данных.

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

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

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

  1. Чтобы открыть пустой запрос, в Excel выберите Данные > Получить данные > из других источников > пустой запрос. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

  2. В строке формул введите=Text.Proper("text value"), а затем щелкните значок ВВОД Значок ВВОД слева от строки формул в Power Query или нажмите клавишу ВВОД.

    Результаты отображаются в режиме предварительного просмотра данных .

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

Результат:

Текстовое значение

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

Важно    Внимательно изменяйте шаги Источник, Навигация  и Измененный тип, так как они создаются Power Query для определения и настройки источника данных.

Изменение формулы с помощью диалогового окна

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

  1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

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

  3. В диалоговом окне внесите изменения и нажмите кнопку ОК.

Вставка шага

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

  1. В области Параметры запроса в разделе Примененные шаги выберите шаг, который нужно сразу же перед новым шагом и соответствующей формулой.

  2. Щелкните значок Добавить шаг Значок функции слева от строки формул. Кроме того, щелкните шаг правой кнопкой мыши и выберите команду Вставить шаг после. Создается новая формула в формате :

    = <nameOfTheStepToReference>, например =Production.WorkOrder.

  3. Введите новую формулу в формате:

    =Class.Function(ReferenceStep[,otherparameters])

    Например, предположим, что у вас есть таблица со столбцом Gender и вы хотите добавить столбец со значением "Ms". или "Mr.", в зависимости от пола человека. Формула будет:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Пример формулы

Изменение порядка шага

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

Удаление шага

  • Щелкните значок Удалить Удаление шага слева от шага или щелкните его правой кнопкой мыши, а затем выберите Удалить или Удалить до конца. Значок Удалить Удаление шага также доступен слева от строки формул.

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

Например, у вас есть таблица Excel с именем Orders со столбцом ProductName, который необходимо преобразовать в правильный регистр. 

До:

Перед

После:

Шаг 4. Результат

При создании расширенного запроса создается ряд шагов формулы запроса на основе выражения let. Используйте выражение let для назначения имен и вычисления значений, на которые затем ссылается предложение in , определяющее шаг. В этом примере возвращается тот же результат, что и в разделе "Создание формулы в строке формул".

let  
    Source = Text.Proper("hello world")
in  
    Source  

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

Этап 1. Открытие Расширенный редактор

  1. В Excel выберите Данные > Получить данные > другие источники > пустой запрос. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

  2. В Редактор Power Query выберите Главная > Расширенный редактор, который откроется с шаблоном выражения let.

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

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

  1. Создайте выражение let с помощью функции Excel.CurrentWorkbook следующим образом:

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]#x2

        

    #x4Source

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

Результат:

Шаг 1. Результат

Этап 3. Повышение уровня первой строки до заголовков

  1. Чтобы открыть запрос, на листе выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel (Power Query).

  2. В Редактор Power Query выберите Главная > Расширенный редактор, который откроется с инструкцией, созданной в разделе Этап 2. Определение источника данных.

  3. В выражении let добавьте #"Первая строка в качестве заголовка" и функцию Table.PromoteHeaders следующим образом:

    let
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
    #x4#"First Row as Header" = Table.PromoteHeaders(Source)#x3

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

Результат:

Шаг 3. Результат

Этап 4. Изменение каждого значения в столбце в правильном регистре

  1. Чтобы открыть запрос, на листе выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

  2. В Редактор Power Query выберите Главная > Расширенный редактор, который откроется с инструкцией, созданной на этапе 3. Повышение уровня первой строки до заголовков.

  3. В выражении let преобразуйте каждое значение столбца ProductName в правильный текст с помощью функции Table.TransformColumns, ссылаясь на предыдущий шаг формулы запроса "Первая строка как заголовок", добавив #"Прописные буквы Каждый Word" в источник данных, а затем назначив результату #"Прописные буквы каждый Word".

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
    in
        #"Capitalized Each Word"

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

Результат:

Шаг 4. Результат

Вы можете управлять поведением строки формул в Редактор Power Query для всех книг.

Отображение или скрытие строки формул

  1. Выберите Параметры> файла и Параметры > Параметры запроса.

  2. В области слева в разделе Глобальный выберите Редактор Power Query.

  3. В области справа в разделе Макет выберите или снимите флажок Отображение строки формул.

Включение или отключение M Intellisense

  1. Выберите Параметры > файлов и Параметры > Параметры запроса .

  2. В области слева в разделе Глобальный выберите Редактор Power Query.

  3. В области справа в разделе Формула выберите или снимите флажок Включить Intellisense M в строке формул, расширенном редакторе и диалоговом окне настраиваемого столбца.

Примечание    Изменение этого параметра вступит в силу при следующем открытии окна Редактор Power Query.

См. также

Справка по Power Query для Excel

Создание и вызов пользовательской функции

Использование списка примененных шагов (docs.com)

Использование пользовательских функций (docs.com)

формулы M Power Query (docs.com)

Работа с ошибками (docs.com)

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?
После нажатия кнопки "Отправить" ваш отзыв будет использован для улучшения продуктов и служб Майкрософт. Эти данные будут доступны для сбора ИТ-администратору. Заявление о конфиденциальности.

Спасибо за ваш отзыв!

×