Power Query Modulo: как обрабатывать стекированные данные

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

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

Итак, давайте перейдем к Power Query и увидим необработанные данные. Давайте сохраним необработанные данные и просто сошлемся на них. Мы назовем этот справочный файл TestData .

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

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

Мы собираемся повернуть их в столбцах, но нам нужен способ, чтобы поворот знал, какой элемент входит в какой столбец. Вот тут-то и появляется Power Query Modulo.

Оглавление

• Обработка данных в стеке с использованием модуля Power Query
• Заключение

Обработка данных в стеке с использованием модуля Power Query

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

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

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

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

Другой способ сделать это, когда мы можем все сразу очистить и сохранить некоторые шаги, — это использовать функцию с именем Text.Remove . Сначала мы добавляем настраиваемый столбец, а затем перечисляем все значения, от которых хотим избавиться.

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

Теперь мы можем сделать наш поворот чисто. Мы берем Modulo, идем в Transform, а затем в Pivot Column, и мы собираемся сделать поворот на Custom, но мы не хотим, чтобы он агрегировался. Итак, мы выберем «Не объединять» и нажмем «ОК».

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

Чтобы исправить это, мы можем взять эти столбцы, а затем выполнить Fill Up .

И теперь у нас есть эти дополнительные строки из-за повторяющегося шаблона по модулю.

Мы можем легко избавиться от этого, удалив альтернативные строки. Мы можем сделать это в опции «Удалить строки».

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

Теперь вы можете видеть, что мы хорошо очистили все правильные данные, и у нас все еще есть 28 исходных адресов электронной почты.

Теперь мы можем избавиться от столбца Index и соответствующим образом переименовать заголовки столбцов.

Функции LuckyTemplates по модулю и целочисленному делению DAX
Рекомендации Power Query для вашей модели данных
Power Query M: чередующийся шаблон 0-1 в таблице дат

Заключение

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

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

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

Всего наилучшего!