Одолели тормоза?
#ПолезныеСсылки
Если вдруг одолели тормоза при работе с pq здесь разбиралась одна из самых частых причин.

Рекурсия в DAX
#ПолезныеСсылки
Рекурсия в DAX возможна? На костылях да! Предлагаю к обозрению костыль от признанного гуру, mvp и просто хорошего человека @MaximZelensky:
Поясню на всякий случай свою мысль, может, кто-то будет искать по форуму, ну и самому полезно.
Проблема как обычно многосоставная:

Ваша формула в Excel при переносе в DAX подразумевает, что для одной и той же меры необходимо учитывать ее значения, рассчитанные ранее. Но, так как в DAX нет перезаписываемых переменных, в нем почти невозможно реализовать алгоритм Xi=X(i-1)+n, привычный по Excel.

Каждое значение в DAX рассчитывается индивидуально, независимо от расчета других значений, и отдельный расчёт не имеет представления о результатах других расчётов.

Соответственно, если мы хотим рассчитать что-то как сумму выражений по типу Xi = SUM(X1...X(i-1)), то в этом расчете нам надо повторить расчёт всех предыдущих X.

Исходя из этого есть практически единственный workaround (по крайней мере, мне ничего не пришло в голову другого):

Если у нас i известен и не очень большой, то можно создать формулу DAX, которая будет внутри себя создавать VAR X1, VAR X2 и так далее для всех возможных i.

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

Для ограниченного числа месяцев прогноза (например, 12), решение примерно такое:
Создаем два доп.столбца в календаре:

IsForecast = IF(MAX('Факт'[Дата])<'Календарь'[Date];1;0)

n =

VAR _RankTable =

CALCULATETABLE (

VALUES ( 'Календарь'[МММ-ГГГГ] );

'Календарь'[IsForecast] = 1;

ALL ( 'Календарь' )

)

VAR _n =

RANKX ( _RankTable; 'Календарь'[МММ-ГГГГ]; 'Календарь'[МММ-ГГГГ]; ASC )

RETURN

IF ( 'Календарь'[IsForecast] = 1; _n )


Далее вот такая мера - немного монстр:

Округленный прогноз =

VAR CurrentN = IF(HASONEVALUE('Календарь'[n]);MAX('Календарь'[n]))

VAR ForecastTable = CALCULATETABLE(VALUES('Календарь'[МММ-ГГГГ]); ALL('Календарь'); 'Календарь'[IsForecast]=1; 'Календарь'[n]<=__CurrentN)

VAR 1 = ROUND(CALCULATE(SUMX(ForecastTable;[Прогноз]); 'Календарь'[n]=1); 0)

VAR 2 = ROUND(CALCULATE(SUMX(ForecastTable;[Прогноз]); 'Календарь'[n]<=2) - 1; 0)

VAR 3 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=3) - 2 - 1; 0)

VAR 4 = ROUND(CALCULATE(SUMX(ForecastTable;[Прогноз]); 'Календарь'[n]<=4) - 3 - 2 - 1; 0)

VAR 5 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=5) - 4 - 3 - 2 - 1; 0)

VAR 6 = ROUND(CALCULATE(SUMX(ForecastTable;[Прогноз]); 'Календарь'[n]<=6) - 5 - 4 - 3 - 2 - 1; 0)

VAR 7 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=7) - 6 - 5 - 4 - 3 - 2 - 1; 0)

VAR 8 = ROUND(CALCULATE(SUMX(ForecastTable;[Прогноз]); 'Календарь'[n]<=8) - 7 - 6 - 5 - 4 - 3 - 2 - 1; 0)

VAR 9 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=9) - 8 - 7 - 6 - 5 - 4 - 3 - 2 - 1; 0)

VAR 10 = ROUND(CALCULATE(SUMX(ForecastTable;[Прогноз]); 'Календарь'[n]<=10) - 9 - 8 - 7 - 6 - 5 - 4 - 3 - 2 - 1; 0)

VAR 11 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=11) - 10 - 9 - 8 - 7 - 6 - 5 - 4 - 3 - 2 - 1; 0)

VAR 12 = ROUND(CALCULATE(SUMX(ForecastTable;[Прогноз]); 'Календарь'[n]<=12) - 11 - 10 - 9 - 8 - 7 - 6 - 5 - 4 - 3 - 2 - 1; 0)

RETURN

SWITCH(CurrentN; 1;__1; 2;__2; 3;__3; 4;__4; 5;__5; 6;__6; 7;__7; 8;__8; 9;__9; 10;__10; 11;__11; 12;__12)

Лайфхаки по языку М
#ПолезныеСсылки
Хотите реализовать в pq алгоритм расчётов основанный на порядке строк в таблице или элементов в списке? Будьте осторожны! Чтобы получить результат, за который не будет стыдно, предлагаю ознакомиться с обсуждением на планетеэксель разных аспектов стриминговой семантики языка М. И здесь снова нам помогает тот же @MaximZelensky.

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 вообще всё просто: есть таблицы и есть примененные к ним фильтры, и больше, в общем, ничего там особенного нет. Другое дело, что удержать в голове все пересечения и модификации фильтров может быть тяжело без привычки.

DISTINCT/DISTINCTCOUNT. Быстрее. Выше. Сильнее.
#ПолезныеСсылки
DAX. Когда нужно на серьезном массиве в 10ки млн строк посчитать уникальные значения, сталкиваешься с медлительностью DISTINCT/DISTINCTCOUNT.
Специалистам по ссылке ниже пришлось считать уники на массивах в миллиарды строк. Как они вышли из этой ситуации читайте здесь.
Тема с предварительными расчетами проверена и работает на ура не только с униками, если столкнулись с тормозами, настоятельно рекомендую ознакомиться.

Попытка намбер ван.
#ВсякиеКоннекторы
Всем добра.
Потребовалось по работе лазить в API к одному из интернет-магазинов, и с радостью обнаружил что в #shared ничего вообще нет на тему кодирования хэша для формирования сигнатуры запроса к ресурсу. А вот у ресурса сигнатура имеется, и надо что-то делать. После некоторых изысканий выяснил что кое-что в PQ на эту тему все таки есть, но не в стандарте, а в наборе функций для создания собственных коннекторов. Засучив рукава, поставил VS с нужными надстройками и собрал вот такое чудо. Если кому надо, пользуйтесь, как собрать такой коннектор в инете и на сайте МС инструкций навалом.
Коннектор протестирован на боевом API все отлично выгружается. При установке шлюза в стандартном режиме коннектор отлично работает и после публикации отчета в облаке.
Если тема интересна, то обращайтесь в личку, объясню что там да как.

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

Уважаемые телегочитатели к нам добавился новый автор. Создатель Мерки 1.5, и просто хороший человек Михаил. Как только к нему снизойдет вдохновение, он также порадует вас всяким полезным.

Интервальный поиск в 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

Как тащить курсы валют из cbr.ru используя API
#ПолезныеСсылки
Всем добра.
Потребовалось мне вытащить динамику курса евры. Полез в Яндекс за ссылкой, а там в первой десятке все упорно предлагают тащить курс валюты через парсинг страницы сайта cbr.ru. Но я то помню что за последние 3 года там 2 раза менялась верстка, и один раз менялся формат таблицы, а значит запрос придётся переделывать. Такая динамика нам не нужна. Поэтому рассказываю как правильно. Лезем на cbr.ru и сразу же спускаемся в подвал, там есть ссылка Технические ресурсы. Провалившись в неё нужно ткнуть в ссылку Получение данных, используя XML. Вот это и есть то самое. Динамику там можно найти в Example 2. Ссылку на коды валют можно взять в Example 1
Как говорится, делай как надо, а как не надо не делай.
Удачи в обучении.

Web.Contents и динамические ссылки
#НеВсеЙогуртыОдинаковоПолезны
Всем добра.
Что-то меня работой привалило малость. Но я выбрался и снова научу вас наносить добро и причинять пользу себе и окружающим.
В моем самом втором посте я кое чего обещал, вот сдерживаю слово.
Как формировать веб ссылку с изменяемым параметром чтобы облачная служба PBI не ругалась на динамическое содержимое:

let
// ВОТ ТАК СЕРВИС БУДЕТ РУГАТЬСЯ, если ссылку формировать из динамических параметров в текстовую строку
Source = Xml.Tables(Web.Contents( "http://www.cbr.ru/scripts/XML_daily.asp?date_req=02/03/2002" )),

// ВОТ ТАК ругаться не будет, если Url это константа, а не элемент списка/записи
// при этом в параметр Query можно пихать все что хочется, лишь бы API вас понял
Url = "http://www.cbr.ru/scripts/XML_daily.asp",
Query = [Query = [date_req="02/03/2002"]],
Source1 = Xml.Tables(Web.Contents( Url, Query )),

// ВОТ ТАК тоже ругаться не будет, если Url это константа, а не элемент списка/записи
// но при этом оба элемента записи Query2 можно использовать как динамические параметры
Url2 = "http://www.cbr.ru/scripts/",
Query2 = [ RelativePath = "XML_daily.asp",
Query = [date_req="02/03/2002"]
],
Source2 = Xml.Tables(Web.Contents( Url2, Query2 ))
in
Source2
Здесь продублировал, чтобы удобнее код было на комп переносить.

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

Попытка намбер Ту
#ВсякиеКоннекторы
Всем бобра!
Итак, по просьбам трудящихся собрал ещё один коннектор, который на входе получает текстовую строку, а на выходе возвращает ее хэш при помощи алгоритма sha256. Использовать его очень легко:
положили в папку согласно инструкции. Запускаем pbi и находим его в разделе other.
Вызов осуществляется такой формулой

=GetHashSHA256.Contents( "text" )

Пока от единственного пользователя поступил отзыв что запрос к api с полученным хэшем работает в редакторе запросов, но не пашет при попытке загрузить результат запроса в модель.
Если кто желает потестировать. Велкам. Архив тут.
З.Ы. На подходе аналогичный коннектор для получения HMAC на базе того же sha256.

Попытка намбер три
#ВсякиеКоннекторы
Всем добра!
Как и обещал, выкладываю последний коннектор из нужных для подключения к API с хэшированием ключей через Power BI. Сие творение используется для формирования HMAC (hash-based message authentication code) на основе двух аргументов, а именно текст запроса и ключа авторизации.
Для использования по инструкции из предыдущего поста кладем файл в нужную папку и наблюдаем новый коннектор в PBI Desktop в разделе others.
Для вызова функции используем код:
= GetHMAC.Contents( "text", "key" )
На выходе получаем хэш созданный на базе этих двух аргументов. Далее скармливаем его вашему API согласно приведенной к нему документации.
Функция протестирована, на API Ламоды - работает отлично. В теории должна работать и в DataFlows, но пока не тестировал.
Кстати, перевыложил предыдущий коннектор, исправил в нем блок для прохождения проверки авторизации при настройке подключения через шлюз.
Заранее предупреждаю, что с точки зрения безопасности коннектор не оптимален, т.к. подразумевает передачу логина и ключа в код в открытом виде. В планах исправить данную ситуацию и использовать креды для хранения конфиденциальной информации. Просто пока я так не умею, но обязательно научусь.
Удачных подключений!

Как сформировать таблицу на листе Excel с помощью формул DAX 2
#ПолезныеСсылки
Всем добра!
В продолжение темы вывода таблиц из модели данных на лист Экселя.
На днях "наше все" @MaximZelensky показал на пальцах как при помощи функции DAX EVALUATE() можно формировать справочники в таблицах на листе, после чего данные попадают в модель данных.
Таким образом, получаем возможность формирования справочников из таблиц фактов без многократного изнасилования их при помощи pq. Такое нельзя потерять, поэтому вот.

Релизная версия коннекторов для хэширования текстовых значений на лету
#ВсякиеКоннекторы
Всем добра.
Итак, завершаю свою эпопею с пользовательскими коннекторами и хэшированием всякого. Допилил оба коннектора для возможности передачи в них данных через учётные данные, залил двуязычную справку по использованию и залил исходники на гит.
Уже имеется как минимум 2 благодарных пользователя, у которых получилось даже с моими черновыми версиями. Это уже релиз.
https://github.com/PooHkrd/GetHashSHA256
https://github.com/PooHkrd/GetHMACwithCreds
Надеюсь кому-то ещё поможет. Инструкции по установке в репозиториях по ссылкам выше.
Все вопросы по использованию направляйте в личку, либо на планету, либо на гитхаб.