Довольно часто возникает ситуация, когда исходные данные представлены в виде плоского списка в текстовом файле или даже файле формата Excel, и требуется преобразовать этот список в таблицу.
Конечно, можно использовать стандартный инструмент PIVOT из арсенала Power Query. Но при отсутствии ключевого столбца процесс усложняется.
Рассмотрим, как можно выполнить требуемое преобразование стандартными средствами Power Query.
Задача: имея исходный csv-файл подобного вида
требуется получить таблицу:
Для начала загрузим данные в редактор запросов:
Чтобы преобразовать данные из столбцов в строки, попробуйте использовать стандартный инструмент PIVOT. Продвинутые пользователи могут использовать PIVOT с агрегацией.
Перебрали все варианты? Надоело? Читаем дальше!
Для начала немного очистим данные: удалим пустые строки и первую ненужную нам строку с заголовками:
Для применения преобразования PIVOT нам нужен индексный столбец. Причем в нашем наборе индекс нужен для каждого блока данных:
Воспользуемся стандартными инструментами Power Query, добавим столбец индексов в нашу таблицу:
Результат выполнения команды:
Если у нас блоки содержат одинаковое количество строк, то для дальнейших операций можно было бы использовать деление. Но, рассмотрим более универсальный вариант, на случай, если количество строк в блоках может отличаться.
Добавим условный столбец для первых строк набора данных:
Получим результат:
Получили уникальное значение индекса для каждого блока данных. Заполним этим значением строки ниже:
Результат применения команды:
Столбец с индексами нам больше не нужен, удалим его:
На этом подготовительные действия закончены. Теперь для преобразования строк в столбцы можем применить PIVOT:
Параметры вызова функции должны быть такие:
Хотя созданный нами столбец с индексами блоков в параметрах не указан, он используется внутри функции для обработки данных.
В получившейся таблице первый столбец нам больше не нужен, его можно удалить:
В результате всех проделанных действий получаем таблицу требуемого вида:
Статья подготовлена по материалам блога RADACAD.
Чистых данных и информативных дашбордов!