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

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

Интервальный поиск в 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
Как говорится, делай как надо, а как не надо не делай.
Удачи в обучении.

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

Как правильно работать с API (2 видео от Chris Webb и @IlyaNazarov)
#ПолезныеСсылки

Всем добра.
Набрёл тут случайно на видео 4х летней давности от Криса Вебба про то как правильно использовать Web.Contents при работе с api. Сохраняю здесь. Если с английским не дружите, то запускайте в яндекс.браузере, он сносно переводит на русский в режиме реального времени.
Очень полезный видос.
Также добавляю супер инструкцию от @IlyaNazarov

Измерение скорости выполнения запросов
#ПолезныеСсылки
Всем привет!
Имеется небольшая надстройка, предназначенная для оценки скорости выполнения запросов PQ в среде MS Excel. Вместе со справкой сложена на гитхаб:
https://github.com/buchlotnik/Merka
Надеюсь, будет полезна.
Всех благ!
@buchlotnik

Навигация по каналу
#ПолезныеСсылки

Всем привет!
По просьбам читателей приступили к решению проблемы поиска информации на канале. Для этого взяли третьего. Прошу любить и жаловать - @PQfromtankbot!

Он только приступил к работе и знает ещё не всё, но что-то уже сейчас может подсказать.
Надеюсь, будет полезен.
Всех благ!!!
@buchlotnik

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

Источники мудроты.
#ПолезныеСсылки

Всем добра! Выпрыгнул из сумрака и привел к общему знаменателю старый пост (который удалил) чтобы источники лучше искались нашим ботом, а также добавил ссылочку на Бена нашего Грибаудо.
PQ:
01. Спецификация языка PowerQuery M - это фундамент, букварь, без него дальше тяжело;
01a. M language specification - оригинал этого фундамента на английском (@buchlotnik прям настаивает на чтении этой версии);
02. Справочник по языку формул Power Query M;
03. finalytics.pro простым языком для начинающих;
04. blog by Maxim Zelensky простым языком, но не для начинающих;
05. Товарищ Excel кратко, емко;
06. planetaexcel.ru обстоятельно и с примерами;
07. Книга "Скульптор данных в Excel с Power Query";
08. Кен Пульс и Мигель Эскобар. "Язык М для Power Query" русский перевод отличной книги;
09. Приручи данные с помощью Power Query в Excel и Power BI второе издание шикарной книги на русском от Кен Пульс и Мигель Эскобар.
10. BI блог Максима Уварова перевод статей Криса Вебба и разные коннекторы..;
11. The BIccountant грамотный бух рассказывает про BI;
12. Chris Webb's BI Blog Chris Webb's BI Blog Мега дядька рассказывает обо всем на свете. Просто кладезь!;
13. The Environment concept in M for Power Query and Power BI Desktop, Part 1 Концептуальный и обстоятельный разбор с погружением;
14. Промокоды от переводчика Александр Гинько на отличные книги;
15. Power Query Formatter Красота спасет мир... и мои глаза.

DAX:
01. Основные сведения о DAX в Power BI Desktop опять фундамент;
02. Articles - SQLBI Мега дядьки про все подряд;
03. Patterns – DAX Patterns Мега дядьки про расчеты стандартных показателей в аналитике. ABC и вот это вот все;
04. BI блог Антона Будуева. Разбор формул DAX по-русски;
05. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel;
06. Подробное руководство по DAX;
07. Шаблоны DAX;
08. DAX Formatter by SQLBI Красота спасет мир... и мозг тех, кто читает ваши формулы.

Производственный календарь.
#ПолезныеСсылки
Всем добра!
Со скуки запилил парсер производственного календаря за произвольный период (настраивается в параметрах) с сайта Консультант плюс. В телегу код не выкладываю - слишком громоздко, поэтому ссылочкой на форум. Всем желающим предлагаю для развития запилить функцию для выгрузки календаря за произвольный период. Выкладывайте в комментах и добавлю в основной пост. Если желающих не будет - сделаю сам, но позже.

Измерение скорости запросов - Мерка 1.7
#ПолезныеСсылки

Всем привет!
Вышла новая версия Мерки - 1.7
Добавлена возможность выбора числа холостых прогонов, в окно теперь выводится информация о запросе - тип, число диапазонов в книге, находится ли запрос в модели. Обновлена строка состояния - отображается процент выполнения, текущий запрос и номер итерации.
Как всегда, сложена на гитхаб.

Надеюсь, будет полезна.
Всех благ!
@buchlotnik

Мерка 2.0 - теперь и формулы
#ПолезныеСсылки - Мерка

Всем привет!
Душа эксельщика страдала и наконец нашла успокоение. Новая версия - Мерка 2.0 - теперь содержит второй модуль для измерения скорости выполнения формул на листе. Всё как обычно сложено на гитхаб, и написан развернутый мануал - большая просьба ознакомиться перед использованием.
Собственно всё - юзайте на здоровье, а я спать )))

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Мерка 2.1 - теперь и диапазоны с формулами
#ПолезныеСсылки - Мерка

Всем привет!
Немножко допилил надстройку - новая версия с возможностью выбора - анализировать формулы в каждой ячейке отдельно в пределах указанных диапазонов, или анализировать перечисленные диапазоны целиком - удобно при сопоставлении диапазона ячеек с классическими формулами и одной формулы, выводящей динамический диапазон (пригодилось уже пару раз)
Всё как обычно сложено на GitHub, но поскольку выяснилось, что не у всех получается загрузить - дублирую в посте

Надеюсь, будет полезной.
Всех благ!
@buchlotnik

List.Combine+Table.FromList vs AddIndexColumn+AddColumn+Unpivot+Pivot + что там ещё можно наклацать мышкой
#Невсейогуртыодинаковополезны

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

Исходные данные - таблица вида:

#table({"1","2","3"},{{"01.02.22","02.02.22","03.02.22"},{100,200,300},{"04.02.22","05.02.22","06.02.22"},{500,700,900}})

Ее требуется привести к формату:

#table({"1","2"},{{"01.02.22",100},{"02.02.22",200},{"03.02.22",300},{"04.02.22",500},{"05.02.22",700},{"06.02.22",900}})

Предложение автора ролика в коде (исходный диапазон представлен в виде таблицы excel. Ее можно самостоятельно по примеру выше составить на листе):

    get_data = Excel.CurrentWorkbook(){[Name="Дата"]}[Content],
    tab_idx =Table.AddIndexColumn(get_data,"Индекс",0,1, Int64.Type),
    tab_mod =Table.AddColumn(tab_idx,"Остаток от деления",each Number.Mod([Индекс], 2),type number),
    tab_row_num =Table.AddColumn(tab_mod,"row_num",each if [Остаток от деления] = 0 then [Индекс] else [Индекс] - 1),
    tab_remove_cols = Table.RemoveColumns(tab_row_num, {"Индекс"}),
    tab_unpivot = Table.UnpivotOtherColumns(tab_remove_cols,{"Остаток от деления", "row_num"},"Атрибут","Значение"),
    tab_pivot = Table.Pivot(Table.TransformColumnTypes(tab_unpivot,{{"Остаток от деления", type text}},"ru-RU"),
    List.Distinct(Table.TransformColumnTypes(tab_unpivot,{{"Остаток от деления", type text}},"ru-RU")[#"Остаток от деления"]),"Остаток от деления","Значение"),
    cols_select = Table.SelectColumns(tab_pivot,{"0", "1"})

Подход в представленном коде: добавление столбца индекса, нахождение остатков по модулю и далее ресурсоемкие функции Pivot и Unpivot.
Данное решение имеет право на жизнь и ни в коем случае не оспаривается.
Есть ли альтернатива данному подходу?
Сходу решение:
from = Excel.CurrentWorkbook(){[Name="Дата"]}[Content],
lst1=List.Combine(Table.ToRows(Table.AlternateRows(from,1,1,1))),
lst2=List.Combine(Table.ToRows(Table.AlternateRows(from,0,1,1))),
zp=List.Zip({lst1,lst2}),
to=Table.FromList(zp,(x)=>x)
Логика кода: Берем четные и нечетные списки по строкам таблицы, комбайн и через ZIP определяем в Table.FromList.
Проверка скорости на Мерке показала, что альтернативное решение как минимум в 3 раза по скорости выше, чем код автора ролика на Youtube.

Решение было показано Михаилу (@buchlotnik) и от маэстро PQ последовал ответ в виде еще одного решения:

from = Excel.CurrentWorkbook(){[Name="Дата"]}[Content],
to = Table.FromList(List.Combine(List.Transform(List.Split(Table.ToList(from,(x)=>x),2),List.Zip)),(x)=>x)

Согласитесь, лаконично, логично и красиво.

Ожидаемо, что по скорости данное решение не оставило шансов предыдущим двум. Можете сами убедиться на 56k строк:

Запрос       Среднее      От         До     Общее
Buch           0,59       0,53       0,7    14,84
CubRoot        0,93       0,86       1,09   23,27
Youtube        2,84       2,67       3,14   71,02

О чем этот пост: Решения через "мышку" имеют право на жизнь, но если Вы хотите ощутить в полной мере, как дрожит двигатель М, берите в руки механику – используйте расширенный редактор, читайте Спецификацию и предлагайте свои оригинальные решения.
Вместе мы сделаем мир М чище )

Надеюсь было полезно
Спасибо Михаилу @buchlotnik за помощь в написании статьи.
@CubRoot

Мерка - теперь и на ютубе
#ПолезныеСсылки - Мерка

Всем привет!
Не созрел я пока до полноценного канала, но кое-что выкладывать буду.
Посему - мануал по Мерке:
https://www.youtube.com/watch?v=pScYcnbsG2c&t=5s
А там посмотрим, может и попишу код на камеру )))

Надеюсь, было полезно.
Всех благ!
@buchlotnik

PQ тизер курса
#ПолезныеСсылки - Код

Всем привет!
Осенью запускаем курс по pq - по ссылке анонс того, чем будем заниматься )))
https://www.youtube.com/watch?v=Rgy4vXWV1jU

Версия режиссерская, т.е. моя - тут мало про курс и много того, как я пишу, по мне - это самое интересное.
Маркетологи зарубили, а мне нравится, так что смотрите - может полезностей каких подсмотрите )))

Пы.Сы. я знаю, что там можно без FillDown сразу локальной группировкой, но всё в одно видео не вместить, вот )))

Надеюсь, было полезно.
Всех благ!
@buchlotnik

Производственный календарь 2
#АнатомияФункций – CustomFunctions

Всем привет!
Подкинули повод спарсить производственный календарь с Консультанта. Тема уже поднималась Лёхой, но код не был выложен в силу громоздкости.
Подумал, что надо бы исправить ситуацию )))

Посему функция consultant:

(optional year as nullable number) =>
   [path = "http://www.consultant.ru/law/ref/calendar/proizvodstvennye/",
    yr = year??Date.Year(DateTime.LocalNow()), 
    from = Text.FromBinary(Web.Contents(path&Text.From(yr)&(if yr = 2020 then "b" else ""))),
    splt = List.Skip(Text.Split(from,"#(tab,tab,tab)<td class=")),
    lst=List.Zip({{1..12},splt}),
    f=(x)=>[a=Text.Split(x{1},"<td class="),
            b=List.Select(a,(x)=>not Text.Contains(x,"inactively")),
            c=List.Transform(b,(x)=>List.Range(Text.SplitAny(x,"><"),0,2))][c],
    g=(x,y)=>{#date(yr,x{0},Number.From(y{1})),Text.Remove(y{0},"""")},
    tr=List.TransformMany(lst,f,g),
    tbl=Table.FromList(tr,(x)=>x,type table [Date=date,DateType=text])][tbl]

()=> Обращаем внимание – функция без обязательных аргументов
path – откуда тащим
yr – год, за который нужен календарь - если аргумент не передан – берём текущий год (у кого не работает ?? меняем выражение на if year is null then Date.Year(DateTime.LocalNow()) else year)
from – тащим информацию с сайта. Есть нюанс – 2020 был непростым годом, поэтому тогда календарь перезаливали и он получил адресацию 2020b, что собственно здесь и учитывается. В данном случае просто получаем текст со страницы
split – хитро делим текст – помесячные блоки отбиваются тройным табом – обращаю внимание как это записано, ну и первый кусок (до помесячных) отбрасываем
lst – соединяем блоки текста с номерами месяцев через List.Zip
tr – немножко перескочим – полученный список отправляем в List.TransformMany, для которой нам нужны две функции:
f – делит текстовый блок по "<td class=", оставляет фрагменты без "inactively" (у них на сайте месячные блоки стандартные 7 на 6, и так обозначены пустые), и их сплитуем по ">" и "<", оставив первые два фрагмента – статус и номер дня соответственно
g – собираем из полученного дату и статус даты без лишних кавычек

tbl – из полученного списка получаем таблицу, сразу задав типизацию
Как бы всё)))

Как этим пользоваться:

consultant() // календарь на текущий год
consultant(2024) //календарь на конкретный год
Table.Combine(List.Transform({2010..2024},consultant)) //календарь за период лет

Не думаю, что сообщил что-то прям новое, но синтаксически выглядит прикольно.

Надеюсь, было полезно.
Всех благ!
@buchlotnik