Как удалить пустые строки в power bi
Перейти к содержимому

Как удалить пустые строки в power bi

  • автор:

Как удалить пустые строки в вычисляемой таблице Power BI?

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

Team | Meeting | Report aaa | 1/1/2018 | 9/1/2018 aaa | 1/1/2018 | 7/1/2018 bbb | 1/1/2018 | 1/2/2018 bbb | 1/1/2018 | ccc | 1/1/2018 | 3/3/2018 aaa | 1/1/2018 | 

Это моя функция для средних дней

CALCULATE( AVERAGEX(Planning,Planning[Report]-Planning[Meeting]), FILTER(Planning,NOT(ISBLANK(Planning[Report]))) ) 
Team | average aaa | 7 (14/2) bbb | 31 (31/1) ccc | 61 (61/1) 

Функция кажется рабочей, но я немного параноик по поводу моего (отсутствия) понимания вычисления и фильтрации, чем я могу что-то сделать неправильно?

Поделиться Источник 14 марта 2018 в 17:02

1 ответ

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

Кстати, для этой конструкции не обязательно нужен FILTER , вы можете просто написать следующее, так как CALCULATE поддерживает основную фильтрацию:

Average = CALCULATE(AVERAGEX(Planning, Planning[Report] - Planning[Meeting]), NOT(ISBLANK(Planning[Report]))) 

Другой способ сделать это — использовать FILTER внутри AVERAGEX вместо использования CALCULATE :

Average = AVERAGEX(FILTER(Planning, NOT(ISBLANK(Planning[Report]))), Planning[Report] - Planning[Meeting]) 

ALLNOBLANKROW

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

Синтаксис

ALLNOBLANKROW( | [, [, [,…]]]> ) 

Параметры

Термин Определение
table Таблица, по которой удаляются все фильтры контекста.
столбец Столбец, по которому удаляются все фильтры контекста.

Необходимо передать только один параметр; параметр — это таблица или столбец.

Возвращаемое значение

Таблица, когда переданный параметр был таблицей или столбцом значений, когда переданный параметр был столбцом.

Замечания

  • Функция ALLNOBLANKROW фильтрует только пустую строку, которую родительская таблица в связи показывает, когда в дочерней таблице есть одна или несколько строк, не соответствующих значениям родительского столбца. Подробные объяснения см. в приведенном ниже примере.
  • В следующей таблице приведены варианты ALL, предоставляемые в DAX, и их различия.
Функция и использование Description
ALL(Column) Удаляет все фильтры из указанного столбца в таблице; все остальные фильтры в таблице, по-прежнему применяются к другим столбцам.
ALL(Table) Удаляет все фильтры из указанной таблицы.
ALLEXCEPT(Table,Col1,Col2. ) Переопределяет все фильтры контекста в таблице, кроме указанных столбцов.
ALLNOBLANK(table|column) Из родительской таблицы связи возвращает все строки, но пустую строку или все отдельные значения столбца, но пустую строку, и игнорирует все фильтры контекста, которые могут существовать.

Пример

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

Шаг 1. Проверка несвязанных данных

Откройте окно PowerPivot, а затем выберите таблицу ResellerSales_USD. В столбце ProductKey отфильтруйте пустые значения. Одна строка останется. В этой строке все значения столбцов должны быть пустыми, за исключением SalesOrderLineNumber.

Шаг 2. Создание сводной таблицы

Создайте новую сводную таблицу, а затем перетащите столбец, datetime. [Календарный год], на панель меток строк. В следующей таблице показаны ожидаемые результаты.

Метки строк
2005
2006
2007
2008
Общий итог

Обратите внимание на пустую метку между 2008 и Grand Total. Эта пустая метка представляет собой элемент Unknown, который является специальной группой, которая создается для учета любых значений в дочерней таблице, не имеющих соответствующего значения в родительской таблице, в этом примере datetime. Столбец [календарный год].

При отображении этой пустой метки в сводной таблице вы знаете, что в некоторых таблицах, связанных с столбцом, datetime. [Календарный год], имеются пустые значения или не соответствующие значения. Родительская таблица — это та, которая отображает пустую метку, но строки, которые не совпадают, находятся в одной или нескольких дочерних таблицах.

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

Шаг 3. Подсчет строк с помощью ALL и ALLNOBLANK

Добавьте следующие два меры в таблицу datetime, чтобы подсчитать строки таблицы: Countrows ALLNOBLANK datetime, Countrows ALL of datetime. Формулы, которые можно использовать для определения этих мер:

// Countrows ALLNOBLANK of datetime = COUNTROWS(ALLNOBLANKROW('DateTime')) // Countrows ALL of datetime = COUNTROWS(ALL('DateTime')) // Countrows ALLNOBLANKROW of ResellerSales_USD = COUNTROWS(ALLNOBLANKROW('ResellerSales_USD')) // Countrows ALL of ResellerSales_USD = COUNTROWS(ALL('ResellerSales_USD')) 

В пустой сводной таблице добавьте дату и время. Столбец [Календарный год] в метки строк, а затем добавьте только что созданные меры. Результаты должны выглядеть следующим образом:

Метки строк Countrows ALLNOBLANK даты и времени Countrows ALL of datetime
2005 1280 1281
2006 1280 1281
2007 1280 1281
2008 1280 1281
1280 1281
Общий итог 1280 1281

Результаты показывают разницу в 1 строке в счетчике строк таблицы. Однако если открыть окно Power Pivot и выбрать таблицу datetime, в таблице не удается найти пустую строку, так как специальная пустая строка, упоминание здесь находится неизвестный элемент.

Шаг 4. Убедитесь, что счетчик является точным

Чтобы доказать, что ALLNOBLANKROW не подсчитывает действительно пустые строки и обрабатывает только специальную пустую строку только в родительской таблице, добавьте следующие два меры в таблицу ResellerSales_USD: Countrows ALLNOBLANKROW ResellerSales_USD, Countrows ALL из ResellerSales_USD.

Создайте сводную таблицу и перетащите столбец, datetime. [Календарный год], на панель меток строк. Теперь добавьте только что созданные меры. Результаты должны выглядеть следующим образом:

Метки строк Countrows ALLNOBLANKROW of ResellerSales_USD Countrows ALL of ResellerSales_USD
2005 60856 60856
2006 60856 60856
2007 60856 60856
2008 60856 60856
60856 60856
Общий итог 60856 60856

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

Пустые и пустые точки данных в диаграммах отчетов с разбивкой на страницы (Power BI построитель отчетов)

При отображении полей с пустыми значениями или значением NULL на диаграмме в отчете с разбивкой на страницы диаграмма может выглядеть неправильно. В зависимости от типа диаграммы пустые значения могут обрабатываться по-разному.

  • Если диаграмма имеет линейный тип (линейчатая диаграмма, гистограмма, точечная, линейная, диаграмма с областями, диаграмма диапазонов), то пустые значения изображаются как пустые пространства или пробелы. Чтобы обозначить пустые точки, необходимо добавить заполнители. Дополнительные сведения см. в разделе «Добавление пустых точек в диаграмму» (Power BI построитель отчетов).
  • Если диаграмма имеет непрерывный линейный тип (с областями, линейчатая, гистограмма, линия, точечная), то для поддержания непрерывности ряда в диаграмму добавляются пустые точки данных.
  • Если диаграмма имеет нелинейный тип (полярная диаграмма, круговая диаграмма, воронкообразная диаграмма или пирамидальная диаграмма), то пустые значения опускаются из отображения.
  • В фигурных диаграммах значения NULL опускаются.

Удаление пустых или пустых значений

Чтобы не скрыть важные данные, подумайте об удалении пустых значений из набора данных. Чтобы отфильтровать значения NULL, используйте в запросе предложение NOT IS NULL. Или же добавьте критерий фильтра, который задает отображение значений, не равных нулю. Дополнительные сведения см. в разделе «Добавление фильтров набора данных», «Фильтры регионов данных» и «Фильтры групп» (Power BI построитель отчетов).

Поля без значений на диаграмме

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

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

Связанный контент

  • Диаграммы
  • Форматирование диаграммы
  • Добавление диаграммы в отчет
  • Устранение неполадок с диаграммами

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

Стрелка фильтра

рядом со столбцом, а затем выберите Очистить фильтр.

Фильтрация с помощью автофильтра

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

Стрелка фильтра

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

Выделение столбца

Фильтрация с помощью текстовых фильтров

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

Стрелка фильтра

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

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

Фильтрация с помощью числных фильтров

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

Стрелка фильтра

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

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

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

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

Стрелка фильтра

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

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

Фильтрация нескольких столбцов

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

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

Результат фильтра

Фильтрация по пустым или пустым значениям

Пустое или пустое значение имеет значение NULL, если ячейка не имеет в ней ничего. Удалить пустые или пустые значения можно двумя способами:

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

Стрелка фильтра

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

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

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(_), )))

Фильтрация по позиции строки

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

Примечание: При указании диапазона или шаблона первая строка данных в таблице имеет нулевое значение (0), а не строка 1 (1). Вы можете создать индексный столбец, чтобы отобразить позиции строк перед указанием строк. Дополнительные сведения см. в статье Добавление столбца индекса.

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

  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. Нажмите кнопку ОК.

Фильтрация путем удаления чередующихся строк

Вы можете фильтровать по чередуются строки и даже определять их шаблон. Например, таблица имеет строку при комментариев после каждой строки данных. Вы хотите сохранить нечетные строки (1, 3, 5 и так далее), но удалить четные строки (2, 4, 6 и так далее).

  1. Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберите ячейку в данных, а затем выберите Данные>запроса>изменить. Дополнительные сведения см. в этойExcel.
  2. Выберите Главная >удалить строки >Удалить чередуются строки.
  3. В диалоговом окне Удаление чередуются строки введите следующее:
    • Первая строка для удаления Начните подсчет в этой строке. Если ввести 2, первая строка будет сохранена, а вторая будет удалена.
    • Количество строк, которые нужно удалить Определите начало шаблона. Если ввести 1, одна строка будет удалена одновременно.
    • Количество строк, которые нужно сохранить Определите конец шаблона. Если ввести 1, продолжите работу с следующей строкой ( третьей строкой).
  4. Нажмите кнопку ОК.

В Power Query есть шаблон для всех строк. В этом примере удаляются нечетные строки и хранятся четные строки.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *