Всем здрасьте, задали сегодня вопрос: а где твой блог? А у меня нету. 😜
Посему решил сделать для себя канал со ссылками на всякое полезное, про что часто спрашивают.

Folder.Files, PBI service и все, все, все...
#НеВсеЙогуртыОдинаковоПолезны
Всем добра! Начнём с Folder.Files. Сегодня в чудесном чате задали вопрос: "а чойта я тащу файлы из папки, а pbi service мне ругается про динамический контент?" А вся проблема в том что для обработки файлов в папке товарищ использовал функцию File.Contents в каждой строке таблицы со списком файлов, и этой функции он скармливал составное имя типа [Folder path]&[File name]. Вот именно на эту конструкцию и ругался сервис.
Что же ему не понравилось? А вот что: при каждом вызове File.Contents служба pbi запрашивает данные для установления уровня конфиденциальности для конкретного пути к источнику, и тут обнаруживается что конкретного пути то и нету, есть составное из переменных, оно же динамическое.
Как же быть? Все очень просто: на выходе функции Folder.Files мы получаем таблицу со списком файлов в папке, а слева имеется такой столбец [Content]. В нем содержатся готовое к обработке бинарное содержимое файлов, вот именно с ним и нужно дальше работать, чтобы не ловить такие ошибки.
З.Ы. аналогичную ошибку часто ловят при обращении к сайтам при помощи Web.Contents, но это уже совсем другая история.
Удачи в освоении.

Одолели тормоза?
#ПолезныеСсылки
Если вдруг одолели тормоза при работе с 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.