Отмена сводных столбцов
В Power Query столбцы можно преобразовать в пары атрибут-значение, где столбцы становятся строками.
Схема, показывающая левую таблицу с пустым столбцом и строками, а также значениями Атрибутов A1, A2 и A3 в качестве заголовков столбцов. В этой таблице столбец A1 содержит значения V1, V4 и V7. Столбец A2 содержит значения V2, V5 и V8. Столбец A3 содержит значения V3, V6 и V9. Когда столбцы развёрнуты, в правой таблице диаграммы содержится пустой столбец и строки, столбец "Атрибуты" с девятью строками, где A1, A2 и A3 повторяются по три раза, и столбец "Значения" со значениями V1 по V9.
Например, учитывая таблицу, например следующую, где строки страны и столбцы дат создают матрицу значений, сложно проанализировать данные масштабируемым способом.
Снимок экрана: таблица, содержащая столбец "Страна", заданного в типе текстовых данных, и три столбца с датами 1 июня 2023 г., 1 июля 2023 г. и 1 августа 2023 г. в качестве типа данных "Весь номер". Столбец "Страна" содержит США в строке 1, Канада в строке 2, и Панама в строке 3.
Вместо этого можно преобразовать таблицу в таблицу с несводных столбцов, как показано на следующем рисунке. В преобразованной таблице проще использовать дату в качестве атрибута для фильтрации.
Снимок экрана: таблица, содержащая столбец Country, заданный как тип текстовых данных, столбец Атрибута, заданный в качестве типа текстовых данных, и столбец "Значение", заданный как тип данных "Целое число". Столбец "Страна" содержит США в первых трех строках, Канаду в следующих трех строках и Панаму в последних трех строках. Столбец "Атрибут" содержит дату 1 июня 2023 года в первых, вперед и седьмой строках, дату 1 июля 2023 года во второй, пятой и восьмой строках, а также дату 1 августа 2023 года в третьей, шестой и девятой строках.
Ключ в этом преобразовании заключается в том, что набор дат в таблице должен быть частью одного столбца. Соответствующее значение для каждой даты и страны должно находиться в другом столбце, эффективно создавая пару атрибут-значение.
Power Query всегда создает пару "атрибут-значение" с помощью двух столбцов:
- атрибут: название заголовков столбцов, которые были развёрнуты.
- значение: значения, которые были под каждым из заголовков несводных столбцов.
В пользовательском интерфейсе есть несколько мест, где можно найти развернутые столбцы. Вы можете щелкнуть правой кнопкой по столбцам, которые вы хотите развернуть, или выбрать команду на вкладке Преобразование на ленте.
Существует три способа развернуть сводные столбцы в таблице:
- Отмена сводных столбцов
- Ротация других столбцов
- Разворачивание только выбранных столбцов
Отмена сводных столбцов
Для описанного выше сценария сначала необходимо выбрать столбцы, которые нужно отменить. Вы можете выбрать ctrl, чтобы выбрать столько столбцов, сколько вам нужно. Для этого сценария необходимо выбрать все столбцы, кроме одного с именем Country. После выбора столбцов щелкните правой кнопкой мыши по любому из выбранных столбцов и выберите Развернуть столбцы.
Снимок экрана таблицы с выделенными столбцами 1 июня 2023 г., 1 июля 2023 г. и 1 августа 2023 г., и командой "Развернуть столбцы", выбранной в контекстном меню.
Результат этой операции дает результат, показанный на следующем рисунке.
Особые соображения
После создания запроса из предыдущих шагов представьте, что начальная таблица обновляется так, чтобы выглядеть на следующем снимке экрана.
Снимок экрана: таблица с теми же исходными столбцами, страной, датами 1 июня 2023 г., 1 июля 2023 г. и 1 августа 2023 г., с добавлением столбца с датой 1 сентября 2023 г. Столбец "Страна" по-прежнему содержит значения США, Канады и Панамы, но и Великобритания добавила в четвертую строку, а Мексика добавила к пятой строке.
Обратите внимание, что вы добавляете новый столбец на дату 1 сентября 2023 г. (9.1.2023) и две новые строки для стран и регионов Великобритании и Мексики.
При обновлении запроса обратите внимание, что операция выполняется в обновленном столбце, но не влияет на столбец, который изначально не был выбран (в этом примерестрана). Это означает, что любой новый столбец, добавленный в исходную таблицу, также не своден.
На следующем рисунке показано, как выглядит запрос после обновления с новой обновленной исходной таблицей.
Снимок экрана: таблица со столбцами "Страна", "Атрибут" и "Значение". Первые четыре строки столбца Country содержат США, вторая четыре строки содержит Канаду, третья четыре строки содержит Панаму, четвертое четыре строки содержит Великобританию, а пятая четыре строки содержит Мексику. Столбец "Атрибут" содержит даты 1 июня 2023 г., 1 июля 2023 г. и августа 2023 г. в первых четырех строках, которые повторяются для каждой страны.
Преобразование других столбцов в неповоротную форму
Вы можете выбрать столбцы, которые не хотите развести, и выполнить разведение остальных столбцов в таблице. Эта операция, где Разворачивание других столбцов вступает в действие.
Результат этой операции дает точно такой же результат, как и результат, полученный из развёртывания столбцов.
Снимок экрана: таблица, содержащая столбец Country, заданный как тип текстовых данных, столбец Атрибута, заданный в качестве типа текстовых данных, и столбец "Значение", заданный как тип данных "Целое число". Столбец Country содержит США в первых трех строках, Канаду в следующих трех строках и Панаму в последних трех строках. Столбец "Атрибут" содержит дату 1 июня 2023 года в первых, вперед и седьмой строках, дату 1 июля 2023 года во второй, пятой и восьмой строках, а также дату 1 августа 2023 года в третьей, шестой и девятой строках.
Заметка
Это преобразование имеет решающее значение для запросов, имеющих неизвестное количество столбцов. Операция распакует все столбцы из вашей таблицы, за исключением выбранных. Это идеальное решение, если в результате обновления в источнике данных вашего сценария появились новые столбцы с датами, так как они будут обнаружены и трансформированы из столбцов в строки.
Особые соображения
Аналогично операции Отмена сводных столбцов, если запрос обновляется и из источника данных выбирается больше данных, все столбцы не сводятся, кроме выбранных ранее.
Чтобы проиллюстрировать этот процесс, предположим, что у вас есть новая таблица, как в следующем изображении.
Снимок экрана: таблица с столбцами Country, 1 июня 2023 г., 1 июля 2023 г., 1 августа 2023 г. и 1 сентября 2023 г. со всеми столбцами, заданными для типа текстовых данных. Столбец "Страна" содержит, в порядке сверху вниз: США, Канада, Панама, Великобритания и Мексика.
Вы можете выбрать столбец
Снимок экрана: таблица со столбцами "Страна", "Атрибут" и "Значение". Столбцы "Страна" и "Атрибут" задаются для типа текстовых данных. Столбец "Значение" настроен на тип данных "Целое число". Первые четыре строки столбца Country содержат США, вторая четыре строки содержит Канаду, третья четыре строки содержит Панаму, четвертое четыре строки содержит Великобританию, а пятая четыре строки содержит Мексику. Столбец "Атрибут" содержит 1 июня 2023 г., 1 июля 2023 г., 1 августа 2023 г. и 1 сентября 2023 г. в первых четырех строках, которые повторяются для каждой страны.
Разворачивать только выбранные столбцы
Этот последний параметр предназначен только для разворота конкретных столбцов в вашей таблице. Этот параметр важен для сценариев, когда вы работаете с неизвестным количеством столбцов из источника данных и хотите развернуть только выбранные столбцы.
Чтобы выполнить эту операцию, выберите столбцы для разворота, которые в этом примере включают все столбцы, кроме столбца Country. Затем щелкните правой кнопкой мыши любой из выбранных столбцов, а затем выберите Отменить сводку только выбранных столбцов.
Обратите внимание, что эта операция дает те же выходные данные, что и предыдущие примеры.
Снимок экрана: таблица, содержащая столбец Country, заданный как тип текстовых данных, столбец Атрибута, заданный в качестве типа текстовых данных, и столбец "Значение", заданный как тип данных "Целое число". Столбец «Страна» содержит США в первых трех строках, Канаду в следующих трех строках и Панаму в последних трех строках. Столбец "Атрибут" содержит дату 1 июня 2023 года в первых, вперед и седьмой строках, дату 1 июля 2023 года во второй, пятой и восьмой строках, а также 1 августа 2023 года в третьей, шестой и девятой строках.
Особые соображения
После обновления, если в исходной таблице появляется новый столбец 9/1/2020 и появляются новые строки для Великобритании и Мексики, выходные данные запроса отличаются от предыдущих примеров. Предположим, что исходная таблица после обновления изменится на таблицу на следующем рисунке.
Выходные данные запроса выглядят следующим образом.
Это выглядит так, так как операция антиповорота была применена только к столбцам 6/1/2020, 7/1/2020и 8/1/2020, поэтому столбец с заголовком 9/1/2020 остается неизменным.