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

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

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

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

  2. The Queries pane that you use to locate data sources and tables

  3. Контекстные меню, удобные для быстрого доступа к командам на ленте

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

  5. В области Параметры запроса со списком свойств и каждого шага запроса

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

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

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

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

Браузер не поддерживает видео.

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

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

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

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

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

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

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

  7. Чтобы переименовать столбцы, дважды щелкните каждый из них, а затем измените "Столбец1" на "Год", "Столбец4" на "Winner" и "Столбец5" на "Итоговая оценка".

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

Результат

Результаты по walkthrough (первые несколько строк)

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

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

Формула

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 a formula in the formula bar

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

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

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

    До: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    после:= 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". или "г-н", в зависимости от пола человека. Формула будет:

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

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

Переусортовка шага

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

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

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

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

Например, у вас есть Excel "Заказы" со столбцом 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]

    in
        Source

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

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

Результат:

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

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

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

  2. В редакторе Power Query выберите Главная > Расширенный редактор, который откроется с помощью выписки, созданной на этапе 2:Определение источника данных.

  3. В выражении let добавьте #"First Row as Header" и Table.PromoteHeaders следующим

    образом:
    let     
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3

        #"First Row as Header"

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

Результат:

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

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

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

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

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

    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. В левой области в области GLOBALвыберите Редактор Power Query.

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

Включить или отключить M Intellisense

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

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

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

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

См. также

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

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

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

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

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

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

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

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединение к программе предварительной оценки Майкрософт

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

Насколько вы удовлетворены качеством перевода?

Что повлияло на вашу оценку?

Добавите что-нибудь? Это необязательно

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

×