https://www.myonlinetraininghub.com/remove-blank-rows-and-columns-from-tables-in-power-query
Автор оригинала: Philip Treacy
Когда вы импортируете данные в Power Query, нет ничего необычного в том, что в итоге получается таблица, содержащая пустые строки и столбцы, как показано ниже:
Есть несколько способов избавиться от этих "пустых" строк и столбцов, некоторые из них лучше, чем другие.
Смотрите видео:
Скачать пример рабочей книги Excel:
Это работает в Power BI и в Excel!
Показанный здесь M-код можно использовать в Power Query, как в Power BI, так и в Excel.
Открепление и поворот
Первый метод включает в себя отмену привязки, а затем поворот данных. Это работает, потому что при отмене привязки Power Query автоматически удаляет все нулевые значения.
Начните с добавления столбца индекса:
Выберите столбец индекса, щелкните правой кнопкой мыши заголовок столбца и затем выберите Unpivot Other Columns:
Затем выберите столбец атрибута и на ленте поверните этот столбец:
В параметрах сводного столбца выберите столбец значений в качестве значений. В разделе Дополнительные параметры выберите не агрегировать:
Выдаю эту таблицу в качестве результата (я удалил столбец индекса).
Не все "пустые" строки и столбцы были удалены, я скоро вернусь к этому. Но сначала я хочу отметить, что, используя этот метод, вы теряете все изменения типа данных, которые были внесены до unpivot/pivot.
Перед откреплением/разворотом (unpivot/pivot)
Почему не удалены все "пустые" строки и столбцы?
Потому что они содержат что-то отличное от null. Unpivot / Pivot удалит все нулевые значения, но если у вас есть такие вещи, как пробелы, пустые строки или непечатаемые символы, такие как табуляция или возврат каретки, все это останется без изменений.
Присмотревшись к таблице исходных данных, вы можете увидеть, что первые 3 строки в столбце Init содержат пробелы (поверьте мне), а строки 4, 5, 6 и 7 содержат непечатаемые символы. CHAR(9) - это табуляция, а CHAR(13) - возврат каретки.
Для работы с этими типами символов необходим другой подход.
Написание M-кода
Хорошо, итак, лучший подход требует написания некоторого M-кода. Извини, но тебе от этого никуда не деться. Но если вы не хотите писать необходимый код (или даже изучать, как это работает), то вы можете просто загрузить пример рабочей книги Excel, которую я создал, и использовать функцию, которую я написал. Не нужно знать, как это работает, просто используйте это.
Удаление пустых строк
Для начала я собираюсь использовать ленту в Power Query для удаления пустых строк (удаление столбцов произойдет позже).
Это приводит к новому шагу с этим кодом (я отформатировал его так, чтобы его было легче читать).
Этот код нуждается в некоторых дополнениях для работы с пробелами и непечатаемыми символами.
Следующий код является результатом этих дополнений. Я закодировал его цветом и отформатировал, чтобы было легче понять мое объяснение того, что он делает. Вы также можете посмотреть видео с моим объяснением.
1. Record.FieldValues передают значения в каждой строке в List.Remove совпадающие элементы
2. List.RemoveMatchingItems удаляет пустые строки "" и нулевые значения
3. List.Transform использует Text.Clean и Text.Обрезать, чтобы удалить пробелы и непечатаемые символы из элементов списка, созданного на шаге 2. Текст.Очистить и текст.Trim заменяет значения, которые они удаляют, пустой строкой.
4. List.RemoveMatchingItems удаляет пустые строки "", созданные на шаге 3
5 и 6. Выберите строки из таблицы, которые не являются пустыми
Удаление пустых столбцов
Этот код написан от руки и показан на рисунке ниже.
1 и 2. Table.Column проходит через каждый столбец (выбранный таблицей.Имена столбцов) и передает значения в каждом столбце в List.Преобразовать
3. List.Transform использует Text.Clean и Text.Обрезать, чтобы удалить пробелы и непечатаемые символы из элементов списка (в каждом столбце). Если текст.Очистить и текст.Обрежьте результат в пустую строку, замените ее на null.
4. List.NonNullCount подсчитывает количество ненулевых элементов в списке (столбце)
5. List.Select использует результаты из списка.NonNullCount для создания списка имен столбцов, содержащих данные
6. Table.SelectColumns использует этот список для выбора только тех столбцов в новой таблице
В результате получается вот это:
Создаем функцию из этого кода
Хотя писать все это было бы забавно каждый раз, когда вам это нужно, было бы проще использовать функцию для очистки ваших таблиц.
Чтобы создать функцию из этого кода, прежде всего продублируйте запрос и назовите его чем-нибудь полезным, например fxRemoveBlanks.
Откройте запрос функции в расширенном редакторе, и вы увидите это.
Изменения, необходимые для превращения этого кода в функцию, заключаются в следующем:
1. Добавьте объявление функции и передайте единственный параметр с именем Source, который будет содержать таблицу
2. Удалите первые 2 строки кода
3. Измените таблицу, над которой ведется работа на шаге #"Removed Blank Rows", на Source
В результате этого:
Использование функции
Если у вас есть запрос, подобный этому, где Source загружается на первом шаге:
Просто вызовите функцию, передав код, который загружает таблицу в качестве параметра функции.
Или, в любой точке вашего кода, вы можете вызвать функцию до тех пор, пока вы передаете таблицу в качестве параметра.