Как получить несколько значений в результате запроса Power Query

Эта статья о работе надстройки Power Query к Excel 2010 и Excel 2013, редактора запросов Power BI и  группы команд “Get & Transform” (“Получить и преобразовать”) в Excel 2016. Надеюсь, когда-нибудь эта чехарда закончится и мы сможем говорить просто Power Query.

Результатом вычисления запроса в Power Query является единственное значение. Как правило, речь идет о таблице, которую мы затем выгружаем на лист или в модель данных в Excel и Power BI. Это же требование относится также и к любым другим выражениям, вычисляемым Power Query, например, встроенным или пользовательским функциям, полям записей, записям в целом, и т.д.

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

Промежуточные и дополнительные результаты запросов

Представьте, что в процессе сложных преобразований запроса Query1 последним шагом под названием ResultTable  мы получили нужный результат (таблицу), которую мы хотим загрузить в модель данных. Одним из промежуточных шагов в нашем запросе был расчет какой-то величины ValueX , и мы хотели бы использовать ее в других выражениях или запросах:

Нам в итоге нужен и ValueX , и, конечно же, ResultTable .

У нас есть как минимум три способа это сделать: Continue Reading


Объединяем выбор на разных срезах в DAX: Динамический фильтр «ИЛИ»

В стандартном режиме несколько срезов в Power BI работают по принципу «И», то есть пересечения примененных фильтров. Мы выбираем на одном срезе «Яблоки», на другом цвет «Красный», и получаем меры, рассчитанные только для красных яблок. Зеленые яблоки будут проигнорированы.

Обычный срез: красные яблоки

Обычный срез: красные яблоки

Периодически (хоть и нечасто) возникает другая задача: как заставить срезы работать по принципу «ИЛИ», то есть учитывать в мере значения обоих срезов? Например, получить одновременно все зеленые фрукты и все яблоки? Все желтое или грушевидное?

Более практичный пример: товарная позиция может быть помечена в базе как складская (за это отвечает свойство «Складская»), одновременно она помечена как плановая (за это отвечает другое свойство, «Плановая»). Для расчетов нас интересуют позиции, которые могут являться, к примеру, складскими ИЛИ плановыми (то есть у них может быть установлено либо одно из этих свойств, либо оба).  Но при использовании двух обычных срезов отбор по свойству «Складская» = «Да» приведет к тому, что прочие строки будут отфильтрованы, даже если у них свойство «Плановая» тоже установлено срезом в значение «Да».

Стандартное решение

Множество интересных способов получить желаемый результат можно почерпнуть в статье гуру DAX Альберто Феррари и Марко Руссо («итальянцев»). Обычно примеры таких мер используют так называемые «прямые» фильтры – когда проводится сравнение с жестко заданным значением. Стандартное решение для таких мер следующее:

Continue Reading


Идеи усовершенствования Power Query на форумах Excel Uservoice и Power BI

Недавно я опубликовал пост о ловушке UsedRange, в которую вы можете попасть при импорте данных с листа Excel в Power Query (вы можете прочесть его тут).

Чтобы добавить возможность импорта в Power Query данных с заданных диапазонов на листе, я создал соответствующие темы на форумах excel.uservoice.com и https://ideas.powerbi.com

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

Спасибо!


Импорт данных в Power Query и Power BI из листа Excel: ловушка UsedRange

Если вы импортируете данные в Power Query или Power BI из файла Excel, обращаясь к листу целиком, будьте осторожны – вас может поджидать ловушка.

При подключении к стороннему файлу Excel нам доступны три варианта извлечения данных:

  • Таблица (форматированный как таблица диапазон ячеек на листе)
  • Именованный диапазон (диапазон ячеек, которому присвоено пользовательское имя)
  • Лист целиком

В первом случае объект «Таблица» представляет собой уже структурированные данные с заголовками столбцов, которые впоследствии автоматически трансформируются в таблицы. Во втором случае Power Query снабдит именованный диапазон автоматическими заголовками («Column1», «Column2» и так далее), и дальнейшая обработка не отличается от импортированных таблиц.

Однако очень часто нужные данные не находятся в форматированной таблице или именованном диапазоне, и преобразовать их в такой вид затруднительно. Причин может быть много, например, необходимо сохранить форматирование (объединение ячеек теряется при преобразовании в таблицу), либо файлов слишком много для ручного преобразования в нужный формат.

Данные с неразмеченного листа

К счастью, Power Query может получить данные с листа целиком. Для того чтобы получить данные из неразмеченного листа, никаких особых действий предпринимать не нужно: подключаемся к файлу, находим нужный лист (в столбце [Kind] он будет иметь значение «Sheet») и получаем данные путем обращения к его содержимому в столбце [Data]:

Листы Excel доступны в качестве источника наравне с таблицами и именованными диапазонами

Однако возникает вопрос, какие данные попадут в таблицу для этого листа? На листе Excel 17 179 869 184 ячеек (16 384 столбцов и 1 048 576 строк). Если бы Power Query пытался загрузить их все, это привело бы к безнадежным «тормозам» при импорте данных таким образом. Однако мы можем убедиться, что обычно количество строк и столбцов примерно соответствует заполненным.

Как же Power Query определяет нужный диапазон данных? Ответ может быть достаточно очевиден, если у вас есть достаточный опыт программирования на VBA и вы хорошо знакомы с объектной моделью Excel (и ответ вас не обрадует). Continue Reading