Фильтрация данных (Power Query)

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

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Щелкните стрелку вниз рядом со столбцом, который требуется отфильтровать.

3. Снимите флажок (Выбрать все), чтобы снять выбор всех столбцов.

4. Выберите поле проверка значений столбцов, по которым требуется отфильтровать, а затем нажмите кнопку ОК.

Вы можете выполнить фильтрацию по определенному текстовому значению с помощью подменю "Фильтры текста ".

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

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

3. Выберите Текстовые фильтры, а затем выберите имя типа равенства Равно, Does Not Equal, Begins With, Не начинается с, Заканчивается на, Не заканчивается на, Содержит и Не содержит.

4. В диалоговом окне Фильтрация строк выполните следующие действия:

   • Используйте базовый режим для ввода или обновления двух операторов и значений.

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

5. Нажмите кнопку ОК.

Вы можете выполнять фильтрацию по числовой величине с помощью подменю Числовые фильтры .

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Щелкните стрелку вниз столбца, содержащего числовое значение, по которому требуется отфильтровать.

3. Выберите Фильтры чисел, а затем выберите имя типа равенства Равно, Не равно, Больше, Больше или Равно, Меньше, Меньше, Меньше или Равно или Между.

4. В диалоговом окне Фильтрация строк выполните следующие действия:

   • Используйте базовый режим для ввода или обновления двух операторов и значений.

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

5. Нажмите кнопку ОК.

Вы можете выполнять фильтрацию по значению даты и времени с помощью подменю Фильтров даты и времени.

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

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

3. Выберите Фильтры даты и времени, а затем выберите имя типа равенства: "Равенства", "До", "После", "Между", "Следующий", "В предыдущем", "Самое раннее", "Последнее", "Несамое раннее", "Не последнее" и "Настраиваемый фильтр".
   
   Совет    Вы можете упростить использование стандартных фильтров, выбрав Год, Квартал, Месяц, Неделя, День, Час, Минута и Секунда. Эти команды работают сразу.

4. В диалоговом окне Фильтрация строквыполните следующие действия:

   • Используйте базовый режим для ввода или обновления двух операторов и значений.

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

5. Нажмите кнопку ОК.

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

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

Значение NULL или пустое значение возникает, если ячейка не содержит ничего. Существует два метода удаления значений NULL или пустых значений:

Использование автофильтра

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Щелкните стрелку вниз рядом со столбцом, который требуется отфильтровать.

3. Снимите флажок (Выбрать все), чтобы отменить выбор всех столбцов.

4. Выберите Удалить пустую и нажмите кнопку ОК.

Этот метод проверяет каждое значение в столбце с помощью следующей формулы (для столбца "Имя"):

Table.SelectRows(#"Changed Type", each ([Name] <> null and [Name] <> ""))

Использование команды "Удалить пустые строки"

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Данные > запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Выберите Главная > Удалить строки > Удалить пустые строки.

Чтобы очистить этот фильтр, удалите соответствующий шаг в разделе Примененные шаги в разделе Параметры запроса.

Этот метод проверяет всю строку как запись с помощью следующей формулы:

Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

Фильтрация строк по позиции аналогична фильтрации строк по значению, за исключением того, что строки включаются или исключаются на основе их положения в данных запроса, а не по значениям.

Сохранение верхних строк

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Выберите Главная > Сохранить строки > Сохранить верхние строки.

3. В диалоговом окне Сохранение верхних строк введите число в поле Число строк.

4. Нажмите кнопку ОК.

Сохранение нижних строк

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Выберите Главная > Сохранить строки > Сохранить нижние строки.

3. В диалоговом окне Сохранение нижних строк введите число в поле Число строк.

4. Нажмите кнопку ОК.

Сохранение диапазона строк

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

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Данные > запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Выберите Главная > Сохранить строки > Сохранить диапазон строк.

3. В диалоговом окне Сохранить диапазон строк введите числа в полях Первая строка и Число строк. Чтобы следовать примеру, введите шесть в качестве первой строки и семь в качестве количества строк.

4. Нажмите кнопку ОК.

Удаление верхних строк

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Данные > запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Выберите Главная > Удалить строки > Удалить верхние строки.

3. В диалоговом окне Удаление верхних строк введите число в поле Число строк.

4. Нажмите кнопку ОК.

Удаление нижних строк

1. Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Данные > запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Выберите Главная > Удалить строки > Удалить нижние строки.

3. В диалоговом окне Удаление нижних строк введите число в поле Число строк.

4. Нажмите кнопку ОК.