Как сформировать таблицу на листе 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