Упорядочивайте данные с помощью Power Query

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

В чем дело?

Представьте, что у HR-отдела компании есть определенный бюджет на дополнительные льготы для сотрудников — курсы, тренинги, массажи и т. Д. Чтобы отслеживать фактическую стоимость и анализировать использование этих льгот, они получают справку в следующей форме:

Он содержит имя сотрудника (выделено синим цветом), какие преимущества он использовал (Услуга), сколько раз (Число), сколько минут было общей продолжительностью (Время) (минуты) и сколько это стоило компании ( Сумма BGN).

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

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

Читать также:  Какие данные хранит Linkedin для каждого пользователя

Путь от первой (неструктурированной) таблицы ко второй (нормализованной) проходит через множество проблем:

  • Создайте столбец с именем каждого сотрудника против используемых им льгот.
  • Каждый сотрудник получил разное количество льгот. Например, Пешо использовал четыре, а Гошо — шесть.
  • Но самая интересная проблема заключается в том, что у нас есть две таблицы с льготами для каждого сотрудника, которые находятся рядом друг с другом.

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

Расставьте столы одна под другой.

Как и в большинстве случаев в Excel, у этой проблемы есть несколько решений, но я думаю, что это наиболее эффективное:

1. Загрузите данные в Power Query.

2. Объедините первые 4 столбца в один..

Отмечаем их и переходим в Преобразовать столбцы слияния. Цель этого действия — объединить эти четыре столбца в один

В появившемся меню нам нужно установить разделитель между данными из четырех столбцов (Separator), а под ним — имя нового столбца. По умолчанию Power Query назначает имя объединенному столбцу, и нам не нужно его менять..

Читать также:  Исправьте нечитаемый текст с помощью Power Query

Щелкните стрелку в поле разделителя и выберите Обычай

Появляется новое поле, в котором мы должны указать, каким должен быть разделитель. Это может быть что угодно — точка, запятая, числа, буквы, символы, даже целые слова и фразы. Хитрость здесь в том, чтобы выбрать разделитель, которого нет в тексте. Это необходимо, потому что через несколько шагов мы снова будем использовать этот разделитель для разделения столбцов, и мы не хотим повредить данные. Обычно крайне маловероятно, что текст содержит этот символ — | и поэтому я его использовал. На всякий случай можем заранее проверить это с помощью инструмента Найти замену в Excel..

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

3. Объедините вторые 4 столбца в один.

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

4. Отметьте два новых столбца и…. мы их объединяем

Вам это может показаться нелогичным, но смысл станет понятен через мгновение. Важная деталь заключается в том, что на этот раз мы используем какой-то другой разделитель, чтобы число работало. В данном случае я использовал «хижину» — ^

Читать также:  5 причин изучить Power Query в 2020 году

И результат — вот этот столбец

5. Расположите один под другим.

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

Затем выбираем:

  • Пользовательский разделитель и установить хижину
  • Каждое вхождение разделителя, т.е. для разделения везде, где встречается «^» в качестве разделителя. Хотя на этом самом этапе это не имеет значения
  • Наконец в Расширенные настройки мы выбрали Рядов. Это сообщает Power Query, что мы хотим, чтобы данные во втором столбце располагались под строками первого столбца.

Смотрите, что происходит:

6. Окончательная договоренность

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

Конечный результат — именно то, что мы хотим — все использованные преимущества собраны в одной таблице с четырьмя столбцами..

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?