Как использовать Power Query в Excel с примерами

В этом руководстве рассматриваются практические реальные сценарии использования Power Query в Excel.

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

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

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

Обрезать и очистить

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

Удалить повторяющиеся строки

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

Удаление дубликатов без учета регистра

Имейте в виду, что эта стандартная операция «Удалить дубликаты» удалит только те строки, которые идентичны во всех отношениях, включая регистр букв. Для дедупликации без учета регистра необходимо изменить M-код запроса. Вот как:

1. Удалите дубликаты с учетом регистра, как описано выше. Альтернативно вы можете выбрать и щелкнуть правой кнопкой мыши столбец, который хотите дедупировать, а затем выбрать Удалить дубликаты из контекстного меню.
2. В строке формул добавьте Сравнить.OrdinalIgnoreCase в качестве критерия сравнения со вторым аргументом Таблица.Различные функция.

В нашем случае после выполнения стандартной операции удаления дубликатов функция Table.Distinct выглядела так:

=Table.Distinct(#”Очищенный текст”, {“Полное имя”})

Он успешно удалил все строки с абсолютно одинаковыми именами в столбце A, но оставил некоторые записи с разными регистрами букв, как показано на снимке экрана ниже:

Чтобы устранить эту проблему, вы можете добавить в функцию критерий Comparer.OrdinalIgnoreCase следующим образом:

=Table.Distinct(#”Очищенный текст”, {“Полное имя”, Comparer.OrdinalIgnoreCase})

Это удалит все строки, содержащие дубликаты в столбце A, игнорируя регистр букв.

Пользователи с продвинутыми навыками работы с Excel могут выполнить эту операцию в Расширенном редакторе, изменив строку «Удаленные дубликаты» на следующий формат:

Table.Distinct(PreviousStep, {“Имя столбца”, Comparer.OrdinalIgnoreCase})

Примечание. Иногда вам может потребоваться просмотреть несколько столбцов, чтобы выявить повторяющиеся записи. Например, если у человека разные варианты имени, например «Джонсон, Билл» и «Джонсон, Уильям», вы также можете проверить столбец «Адрес» на наличие дубликатов.

Изменить тип данных

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

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

• На Дом вкладка, выберите Тип данных > Дата из ленты.
• Щелкните правой кнопкой мыши заголовок столбца и выберите Тип изменения > Дата.

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

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

1. На Добавить столбец вкладка, в Общий группу, нажмите Пользовательский столбец.
2. в Пользовательский столбец В диалоговом окне введите в соответствующем поле имя нового столбца, например «Дата в произвольном формате».
3. в Пользовательская формула столбца поле, введите ДатаВремя.ToText функция с двумя аргументами: исходным столбцом даты и кодом пользовательского формата.
   • Чтобы добавить исходный столбец даты к первому аргументу, выберите его в разделе Доступные столбцы справа и нажмите Вставлятьили дважды щелкните имя столбца.
   • В качестве второго аргумента введите желаемый формат даты, например «дд-МММ-гг» или любой другой код формата.

     Полная формула принимает такой вид:

     =ДатаВремя.ТоТекст([Registration date]”дд-МММ-гг”)

4. Нажмите «ОК», и в вашу таблицу будет добавлен новый столбец с произвольным форматом даты.

В строке формул отобразится полная формула на языке M, которая будет выглядеть примерно так:

=Table.AddColumn(#”Предыдущий шаг”, “Дата в произвольном формате”, каждый DateTime.ToText([Registration date]”дд-МММ-гг”))

Примечание.

• Выходные данные этой функции представляют собой текстовое значение, а не значение даты и времени.
• Для пользовательских форматов даты и времени вы можете использовать те же коды форматов, что и в Excel. Единственное отличие состоит в том, что в Power Query строчная буква «m» означает минуты, а прописная «M» — месяцы.

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

Разделить столбец

Чтобы разделить столбец на два или более столбца по определенному разделителю, вы можете использовать команду Разделить столбец функция. Например, чтобы разделить столбец «Полное имя» на «Имя» и «Фамилия», выполните следующие действия:

1. Выберите столбец «Полное имя».
2. Перейдите к Трансформировать вкладке и в Текстовый столбец группу, нажмите > Разделить столбец > По разделителю.
3. Выберите разделитель из раскрывающегося списка. Если вашего разделителя нет среди предопределенных вариантов, выберите Обычай и введите нужные символы в поле ниже (в данном примере запятая и пробел «,»).
4. Решите, при каком появлении разделителя столбец будет разделяться: крайний левый, крайний правый или каждый разделитель. Если ячейка имеет только один разделитель, подойдет любой вариант. Но если в ячейке более одного разделителя, выбирать придется внимательно.
5. Когда закончите, нажмите ОК.
6. Щелкните правой кнопкой мыши заголовок каждого нового столбца и выберите Переименовать из контекстного меню, чтобы дать им соответствующие имена (например, «Имя» и «Фамилия»).

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

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

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

Например, давайте посмотрим, как извлечь название страны из столбца «Адрес»:

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

3. В появившемся диалоговом окне введите разделитель (запятая и пробел «,» в нашем наборе данных).
4. Развернуть Расширенный вариант раздел и выберите поиск разделителя с конца ввода, поскольку название страны идет после последней запятой в ячейке. Если вам нужно извлечь значение из середины строки, укажите, сколько разделителей нужно пропустить.
5. Когда закончите, нажмите ХОРОШО.

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

Добавить столбец из примера

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

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

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

1. Выберите столбец, содержащий исходные данные (столбец «Адрес» в нашем примере).
2. На Добавить столбец вкладка, нажмите Столбец из примеров > Из выбора.
3. В первой строке нового столбца введите название страны, соответствующее первому адресу. Power Query попытается определить шаблон и заполнить остальные значения на основе вашего примера. Если некоторые ячейки пусты или заполнены неверными значениями, укажите другой пример во второй строке или в любой другой строке, пока Power Query не выполнит все правильно.
4. Когда все ячейки заполнены правильными значениями, нажмите Ctrl + Enter, чтобы применить изменения.

Теперь у вас появится новый столбец, который извлекает названия стран из адресов.

Заменить пропущенные значения

В Power Query замена пропущенных значений, часто представленных как нулевойпредставляет собой простой процесс:

1. Выберите столбцы, в которых вы хотите обработать пропущенные значения.
2. На Дом вкладка, в Трансформировать группу, нажмите Заменить значения.
3. в Заменить значения диалоговое окно, заполните два поля:
   • Значение, которое нужно найти: нулевой
   • Заменить: введите значение замены, соответствующее вашему типу данных (например, «0» для числовых столбцов или «Н/Д» для текстовых столбцов).
4. Нажмите «ОК», и Power Query применит замену ко всем выбранным столбцам.

Добавить условный столбец

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

1. Выберите любой столбец в наборе данных.
2. На Добавить столбец вкладка, нажмите на Условный столбец.
3. В открывшемся диалоговом окне введите Уровень донора в качестве имени нового столбца. Затем укажите следующие правила:

   | Если пожертвование | больше или равно | 4000 | тогда | Платина |

   | Если пожертвование | больше или равно | 3000 | тогда | Золото |

   | Если пожертвование | больше или равно | 2000 | тогда | Серебро |

   | Остальное | Бронза |

4. Нажмите кнопку ОК, чтобы создать новый столбец.

Эта функция аналогична написанию вложенного оператора IF в Excel, но ее гораздо проще и удобнее использовать.

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

Заменить или удалить ошибки

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

1. Выберите столбец, в котором вы хотите обрабатывать ошибки.
2. Щелкните правой кнопкой мыши заголовок столбца.
3. В контекстном меню вы найдете две ключевые опции для обработки ошибок:
   • Удалить ошибки удалит все строки, содержащие ошибки в выбранных столбцах, поэтому будьте осторожны с этой опцией.
   • Заменить ошибки попросит вас указать стоимость замены. Для числовых столбцов это должно быть число, например 0. Для текстовых столбцов можно указать любое текстовое значение, включая пустую ячейку.

Группировать и агрегировать

Power Query предлагает мощные возможности для суммирования или агрегирования данных по определенным группам. Группа по функция.

Например, чтобы рассчитать общую сумму пожертвования по стране и уровню донора, вам нужно сделать следующее:

1. На Дом вкладка, в Трансформировать группу, нажмите на Группа по.
2. В открывшемся диалоговом окне выберите Передовой Затем выполните следующие действия:
   • Под Группа повыберите «Страна» и «Уровень донора» в качестве столбцов для группировки.
   • Под Новый столбец name, введите «Общее пожертвование» в качестве имени нового столбца.
   • Под Операциявыбирать Сумма как функция агрегирования.
   • Под Столбецвыбирать Пожертвование в качестве столбца для агрегирования.
3. Нажмите ОК, чтобы применить изменения.

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

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

Кончик. После внесения необходимых изменений в редакторе Power Query не забудьте загрузить результаты на лист.

Вот как можно использовать Power Query в Excel. Теперь, когда вы знаете основы, раскройте еще больше секретов преобразования данных, чтобы произвести впечатление на своего начальника, коллег и клиентов своим мастерством работы с данными 🙂

Рабочая тетрадь для скачивания

Использование Excel Power Query — примеры (файл .xlsx)

Дополнительные примеры Power Query