Decart. Beautiful join.
#ВсякоРазно
Как получить все сочетания элементов из двух столбцов, оно же декартово произведение в pq?
Есть несколько вариантов, но самый красивый на мой взгляд этот:
Table.Join( tab1,{},tab2,{})
Где tab1 и tab2 таблицы, состоящие из одного столбца.

DAX. Контекст вычисления меры. Квинтэссенция.
#ВсякоРазно
DAX. Контекст вычисления меры. В книгах от итальянцев Руссо и Феррари этому посвящены целые главы и куча статей. Но есть товарищи в русских селеньях которые умеют выразить квинтэссенцию понятия.
Вопрос от юзернейм:
какие формулы в каком контексте работают.
Ответ @MaximZelensky:
Любые формулы работают именно в том контексте, в который вы их поместите. Важно и то, что требует та или иная функция на вход в качестве аргумента(ов) - скалярное значение, столбец или таблицу. В основном все срубаются на этом.
Вообще с определением контекстов даже у мелкософта проблемы: вот тут их три, а вот тут их два. Но тем не менее, ссылки почитайте.
Формулы бывают в мерах и вычисляемых столбцах, а контекстов бывает несколько, все вместе они создают общий контекст вычисления.
Внешний, или контекст запроса: включает в себя все фильтры, заданные явно или неявно используемыми столбцами (полями) и фильтрами/срезами сводной (полями визуального элемента Power BI), в общем, все фильтры, которые приходят в формулу "снаружи" (должны примениться к формуле и существуют/установлены ДО её вычисления).
Контекст фильтра - полный набор всех фильтров, применяемых к набору данных, в том числе модифицированных (измененных, добавленных или удаленных) внутри самой формулы.
Контекст строки: работает в вычисляемых столбцах и функциях-итераторах (типа SUMX или FILTER) - о нем чуть ниже.
В вычисляемых столбцах есть контекст строки. Контекст строки - это значения каждого столбца текущей строки (если чуть упрощать). В этом контексте простое обращение к столбцу понимается как обращение к значению столбца в текущей строке (мы хотим получить конкретное скалярное значение). Однако если мы используем в вычисляемом столбце не просто формулу типа Таблица[Столбец1]+Таблица[Столбец2], а какую-то функцию, и эта функция на вход принимает не скалярное значение, а столбец, то и будет рассматриваться то, что запрошено: столбец таблицы целиком. Самый простой пример: функция SUM требует своим аргументом столбец, и при создании вычисляемого столбца с формулой =SUM(Таблица[Столбец]) она возьмет в рассмотрение весь столбец.
Если мы используем в формуле вычисляемого столбца функцию CALCULATE, то она преобразует контекст строки в контекст фильтра - значение КАЖДОГО столбца текущей строки становится самостоятельным фильтром для данных этой таблицы. То есть формула =CALCULATE(SUM(Таблица[Столбец])) посчитает сумму столбца только в текущей строке (или, если несколько строк дублируются полностью, посчитает сумму столбца по всем дублирующимся строкам).
Использование меры в расчете вычисляемого столбца равнозначно использованию CALCULATE.
Эта история называется перенос контекста.
Аналогично работает контекст строки и перенос контекста в виртуальных таблицах (например, внутри функции SUMX мы перебираем строки виртуальной таблицы и для каждой строки делаем какие-то вычисления - почти как вычисляемый столбец в обычной таблице).
Но вообще всё просто:
Есть какой-то набор строк (набор данных).
Формула читает (наружный по отношению к ней) набор внешних фильтров, которые создаются полями сводной таблицы или срезами (это внешний контекст). Если формула в вычисляемом столбце обычной таблицы, то набор внешних фильтров пустой - его нет, так как это же не сводная, а просто таблица.
Далее она при необходимости модифицирует его (например, фильтрующими аргументами внутри CALCULATE). Либо меняет фильтр на каком-то столбце, либо убирает его совсем, либо наоборот, добавляет. Так формируется контекст фильтра. И если у нас есть при этом контекст строки, то он может быть (если мы сказали это делать, при помощи CALCULATE) преобразован в такой же контекст фильтра.
В итоге весь получившийся набор фильтров - внешний, внутренний, перенесенный - миксуется и вычисляется, наконец, окончательное значение.
В DAX вообще всё просто: есть таблицы и есть примененные к ним фильтры, и больше, в общем, ничего там особенного нет. Другое дело, что удержать в голове все пересечения и модификации фильтров может быть тяжело без привычки.

Одолели тормоза 2
#ВсякоРазно
Кстати, для счастливых юзеров pq в Excel. Есть ещё одна частая проблема тормозов при выгрузке больших таблиц на лист. Это происходит из-за включенного по умолчанию для всех таблиц автоподбора ширины столбца. Лечится снятием чек-бокса под курсором на картинке.
Удачных вам запросов без тупняков.

Интервальный поиск в PQ
#ВсякоРазно
И снова всем добра. Довольно часто граждане по работе встречаются с задачей сопоставления цен из прайса с фактической таблицей продаж, но в прайсе есть только данные за некоторые периоды, а в фактах идут сквозные даты, т.е. Table.Join нам тут совсем не товарищ. И вот пару лет назад один замечательный человек с планетыэксель Андрей VG научил меня хорошему, а я делюсь с вами. Указанная задача в Excel решается с помощью банальной ВПР в режиме интервального поиска, ну а в PQ этот поиск можно реализовать по такой схеме:
- сначала комбайним (Table.Combine или &) таблицу фактов и справочник,
- сортируем их по дате,
- заполняем вниз/вверх по вкусу столбец с ценами из справочника
- убираем не нужные строки, наводим красоту.
Данный способ не единственный для решения такого типа задач, можно размножить строки справочника, так чтобы список дат в нем покрывал весь диапазон дат из фактов, а потом джойнить это все. Но из всех мною опробированных интервальный поиск - самый шустрый.
З.Ы. Добавлю одно пояснение: т.к. при сортировке по полю дата порядок строк по остальным полям может меняться (разбиралось здесь) к одной из объединяемых таблиц я добавил столбец с единичками и задействовал его в сортировке.
По ссылке есть файлик с примером.
А здесь более сложный пример с группировкой для изоляции номенклатур друг от друга. Там же коллега показал как он изменил предложенный мной код для ещё более быстрого выполнения запроса на большом массиве.
Удачи в обучении!😉
let
to = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица_для_заполнения"]}[Content],{{"Дата", type date}}),
from = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Таблица_источник"]}[Content],{{"Дата", type date}}),
combine = Table.AddColumn(from, "ДатаИсточник", each [Дата], Date.Type) & Table.AddColumn(to, "tab", each 1, Int64.Type),
sorted = Table.Sort(combine,{{"Продавец", Order.Ascending}, {"Дата", Order.Ascending}, {"tab", Order.Ascending}}),
filledDown = Table.FillDown(sorted,{"Выручка", "ДатаИсточник"}),
filtered = Table.SelectRows(filledDown, each ([tab] = 1)),
removed = Table.RemoveColumns(filtered,{"tab"})
in
removed

Как сформировать таблицу на листе Excel с помощью формул DAX
#ВсякоРазно
Всем добра. И снова граждане интересуются странным, а я их учу нехорошему. Расскажу и вам.
Сценарий такой:
- Хочу увидеть на листе Excel таблицу из модели данных.
- Ну, раз хотите...
Итак, можно всегда сослаться на любую таблицу из модели, вот пример. Добавляйте любые столбцы в модели данных с формулами DAX, они тут же появятся в таблице на листе. Хотя таблица в модель вставлена просто из буфера и вообще не имеет внешних источников.
Небольшой гайд как такое сделать:
Если источником таблицы является запрос PQ, тогда просто в меню Загрузить в... кроме галочки о загрузки в модель данных, нужно одновременно выбрать вариант выгрузки еще и в таблицу.
Допустим источник - не запрос PQ,
1. Имеем в модели данных некую таблицу, к которой хотим подключить таблицу с листа и видеть все изменения, допустим в модели она называется Продажи
2. создаем пустой запрос с названием не совпадающим ни с одной из таблиц в модели данных, содержимое запроса не имеет значения, это может быть одна ячейка без значений.
3. результат загружаем и в модель и в таблицу на лист
4. тыкаем ПКМ по выгруженной таблице и выбираем пункт изменить DAX как на картинке
5. указываем имя нужной таблицы из модели
6. удаляем теперь не нужный запрос (он вместе с собой удалит и загруженную ненужную таблицу из модели данных)
7. пользуемся и радуемся.
З.Ы. На все вопросы, типа:
- Алексей, если это такое ненужное и странное, то откуда ты сам про такое знаешь?
Я отвечу:
- Я и сам был когда-то странным, а теперь я странный уж совсем.😜
Пользуйтесь на здоровье. Все картинки и файлы с примерами упомянутые в тексте можно поглядеть пройдя по ссылке.

Ковыряемся в XML структуре файлов xlsx/xlsm/docx
#ВсякоРазно

Всем добра!
До сих пор часто задают вопросы: а может ли PQ вытянуть из книги эксель что-то кроме данных с листа/таблицы/именованного диапазона? Например формат ячейки, или только отфильтрованные пользователем строки? Обычно стандартный ответ: нет. Но это не совсем так, если файл имеет расширение xlsx/xlsm, то по сути своей он представляет собой zip-архив, который можно расковырять и покопаться во внутренней xml-структуре файла. А уж оттуда можно при должном желании и усидчивости вытащить вообще все что угодно. В общем все вышеизложенное для кого-то, наверняка уже не новость. Зато я предлагаю вашему вниманию подборку уже готовых решений, которые могут пригодиться в нашем замечательном ремесле:
- Достаем свойства ячеек от Камрада XL
- Достаем иерархию (вложенность) строк от Максима Зеленского
- Добываем числовые форматы ячеек от Максима Зеленского
- Вытаскиваем только отфильтрованные строки с листа от Михаил L
- Вытаскиваем таблички из docx.
- Сопоставляем какие таблицы/именованные диапазоны на каких листах книги расположены от вашего скромного слуги.

Как удалить все пустые столбцы с сохранением названий столбцов
#ВсякоРазно

Всем снова здрасьте.
Делюсь функцией, которая удаляет все пустые столбцы в таблице, и при этом не ломает заголовки столбцов. Оказывается это довольно востребованный функционал, особенно при обработке выгрузок из 1С или файлов, заполняемых шаловливыми ручками. Код приведен сразу с примером применения. Саму функцию публиковал тут.
let
FnRemoveEmptyColumns = (tab as table) =>
Table.RemoveColumns(
tab,
Table.SelectRows(
Table.Buffer( Table.Profile( tab ) ),
each [NullCount] = [Count] )[Column] ),
Source = #table( {"a","b","c"}, {{1,1,null},{2,2,null}} ),
Custom1 = FnRemoveEmptyColumns( Source )
in
Custom1