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

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

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

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

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

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

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

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

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

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

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

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. В строке формул найдите и измените значения параметров, а затем щелкните значок ВВОД или нажмите клавишу ВВОД. Например, измените эту формулу, чтобы сохранить столбец2:
   
   Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

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

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

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

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

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

2. В строке формул введите=Text.Proper("text value"), а затем щелкните значок ВВОД или нажмите клавишу ВВОД.
   
   Результаты отображаются в режиме предварительного просмотра данных .

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, который необходимо преобразовать в правильный регистр. 

До:

После:

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

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

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

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

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

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

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

1. Создайте выражение let с помощью функции Excel.CurrentWorkbook следующим образом:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]#x2
   
       
   
   #x4Source

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

Результат:

Этап 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. Чтобы загрузить запрос на лист, нажмите кнопку Готово, а затем выберите Главная > Закрыть & Загрузить > Закрыть & Загрузить.

Результат:

Этап 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. Чтобы загрузить запрос на лист, нажмите кнопку Готово, а затем выберите Главная > Закрыть & Загрузить > Закрыть & Загрузить.

Результат:

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

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

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

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

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

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

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

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

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

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