Таблица измерений в текстовом файле: решение Power Query

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

В первую среду каждого месяца проводится испытание DAX , а в третью среду — испытание power query .

Это отличная возможность изучить, открыть и узнать что-то новое об этих языках, которые вам нужно использовать в LuckyTemplates.

Вы найдете категорию «Проблема недели» на форуме LuckyTemplates.

Основное внимание уделяется процессу, а не столько результату.

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

Мелисса де Корте

Ранее Брайан говорил о технике, называемой отладкой резиновой утки . Если вы пропустили это, обязательно посмотрите его видео. Это может помочь вам, когда вы застряли на этих проблемах.

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

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

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

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

Оглавление

• Сводные таблицы в Excel
• Использование Power Query для создания таблицы измерений
• Пользовательская функция очистки текста
• Сведение данных
• Заключение

Сводные таблицы в Excel

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

Затем вы можете разделить это еще больше, перетащив поля в раздел столбца.

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

А на следующей странице я создал пустую сводную таблицу.

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

Я собираюсь перетащить свой сегмент в строки.

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

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

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

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

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

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

Использование Power Query для создания таблицы измерений

Давайте рассмотрим запрос мощности и рассмотрим мое решение.

Лично я считаю, что пользовательский интерфейс отлично справляется с написанием большей части M-кода за нас. Поэтому я стараюсь разрабатывать свои запросы, используя пользовательский интерфейс, насколько это возможно.

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

Это моя базовая группа, созданная с использованием пользовательского интерфейса.

Вы можете видеть, что в применяемых шагах с правой стороны много шагов.

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

Откроем расширенный редактор.

Мы видим, что этот запрос имеет 31 шаг.

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

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

Здесь много комментариев, но в этом посте я хочу выделить только две вещи. Во-первых, это пользовательская функция для очистки текста .

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

Пользовательская функция очистки текста

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

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

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

Давайте посмотрим на М-код для функции очистки текста.

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

С таким результатом я выполнил все преобразования, которые мне были нужны. Некоторые из М-функций, которые я использовал здесь, были предоставлены пользовательским интерфейсом, когда я создавал свой первоначальный запрос, например Text. Функция обрезки. Однако другие функции не использовались.

Если вы не знакомы с ними, вы можете найти все функции M в онлайн-руководстве по формуле M. Это ссылка , по которой вам нужно перейти.

Вы найдете раздел, посвященный силовым запросам и функциям.

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

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

Сведение данных

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

Я начал с добавления index. Я обновил этот индекс, чтобы правильно сегментировать блоки данных. Я сделал это, вернув номер индекса для каждой строки, где в столбце 1 была текстовая страна, а затем я заполнил это значение.

Все, что нам нужно сделать, это повернуть сами данные. На вкладке преобразования вы найдете сводную колонку. Выбрав столбец 1, нажмите «Сводной столбец».

Он будет использовать значения столбца 1 в качестве имени нового столбца. Он также хочет знать, где находятся значения для этих имен полей. Они находятся в нашей объединенной колонке.

Если Excel может работать с текстовыми значениями, Power Query также может это сделать благодаря своим дополнительным параметрам. Все, что нам нужно сделать, это выбрать «Не агрегировать» , чтобы он мог обрабатывать текстовые значения.

Как только мы нажмем OK, мы увидим, что наши данные были свернуты.

Заключение

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

Если вам понравился этот пост в блоге, подпишитесь на канал LuckyTemplates, чтобы не пропустить новый контент.

Надеюсь увидеть вас всех в будущих задачах «Проблема недели».

Мелисса