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

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
Здесь продублировал, чтобы удобнее код было на комп переносить.

Зачем нужны вспомогательные запросы при объединении файлов из папки?
#НеВсеЙогуртыОдинаковоПолезны
Всем добра! Регулярно то тут, то там всплывает проблема по сборке данных из кучи файлов, которые сначала нужно причесать.
Сегодня расскажу про механизм автоматической генерации запросов интерфейсом редактора.
Итак, когда делаете запрос к папке и разворачиваете структуру бинарников как на картинке☝️, то автоматически генерится вот такая структура из запросов:
где
123 - это собственно запрос к папке с названием "123"
Запрос "Пример файла" - это запрос, который по заданному в нем алгоритму выбирает тот единственный файл, из папки по образцу которого будет формироваться шаблонный запрос обработки и функция на его основе.
Запарос "Параметр1" - это собственно параметр, значением которого является бинарное содержимое файла из запроса "Пример файла"
Запрос "Преобразовать пример файла" - это шаблонный запрос, в который в качестве параметра передается бинарник из предыдущего шага и в нем же формируется алгоритм обработки единичного файла из папки перед сборкой в единый массив
Функция "Преобразовать файл" - это функция, которая связана с запросом "Преобразовать пример файла", Что это значит? Это значит что код функции берется напрямую из этого запроса, т.е. если изменить запрос-шаблон, то автоматически изменится и функция. Эта самая функция как раз и вызывается для обработки бинарников в запросе "123".
Что из вышеизложенного следует? Лезем в этот самый запрос-шаблон (он же "Преобразовать пример файла") и в нем производим причесывание по выбранному вами алгоритму, а далее, когда будем разворачивать содержимое файлов в единый массив, там уже будут все нужные вам строки и столбцы. Такая обработка всегда работает гораздо быстрее.
Зачем все это надо? Для обеспечения юзерам возможности легкого редактирования шаблонного запроса после подключения к папке: нет мороки с написанием этого запроса вручную, с переделыванием ее в функцию, с отлавливанием багов, если ошибка скрывается именно в этой функции, и много чего еще.
Продолжение следует...

UnFill или как учесть значения из предыдущих или последующих строк
#АнатомияФункций – Table.ToColumns, Table.FromColumns

Всем привет! В чате подкинули задачку – сделать «FillDown наоборот». Сложность задачи состоит в том, что при преобразовании столбца нам необходимо учесть значения в предыдущих строках. Напрямую M так не умеет, решения через индексы – это в категорию #НеВсеЙогуртыОдинаковоПолезны; но есть приём, который и используем.
Сразу код:

let 
   UnFill =(tbl,col,optional up)=>
    [   a = Table.ColumnNames(tbl),
        b = List.PositionOf(a,col),
        c = Table.ToColumns(tbl),
        d = c{b},
        e = if up is null or up = 0 then {null}&List.RemoveLastN(d,1) else List.Skip(d),
        f = List.Transform(List.Zip({d,e}),(x)=>if x{0}=x{1} then null else x{0}),
        g = List.ReplaceRange(c,b,1,{f}),
        h = Table.FromColumns(g,a)
    ][h],
    
    from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("xdExCoAwDAXQu3R2aNJUoVdRF6tOxa2TeHcxESq4qENcPh1+8qBpV9NlS0BHOss5lqSB394EruF8KWAZIcloKq4RcEph4pQ9YAJslTaJ+uSSU9JX3VtSar6Qgp2Dw2OY/oK//POPNsTbXfGB5HWYWodpdBi987DU7w=="),1))),
    unfilldown = UnFill(from,"Данные"),
    unfillup = UnFill(from,"Данные",1),
    unfilldown2 = UnFill(from,"Данные",0) 
in 
    unfilldown2

Видим функцию UnFill – я немного обобщил задачу, поэтому функция принимает анализируемую таблицу, столбец для преобразования и опциональный аргумент, который отвечает за выбор FillDown или FillUp мы хотим «отменить».
По шагам:
a – получили список имен столбцов
b – нашли номер интересующего нас столбца
c – получили список списков – список, где каждый элемент – это список значений конкретного столбца
d – взяли интересующий нас столбец
e – а вот и начинается магия: если у нас Down – мы берем пустой элемент и добавляем к нему наш список без последнего значения, т.е. получаем список предыдущих значений; если Up – наоборот, удаляем первый элемент и получаем список последующих значений
f – попарно объединяем элементы исходного и преобразованного списков и сравниваем их – если одинаковые – возвращаем null, иначе сохраняем элемент исходного списка
g – теперь дело за малым – подсовываем результат наших преобразований на место исходного столбца (обратите внимание на конструкцию {f} – нам нужно передать элемент списка)
h – ну и собираем таблицу обратно

Всё, задача решена. unfilldown, unfillup, unfilldown2 – просто примеры корректного синтаксиса при использовании.
Вот так, курите списки, чтобы шаманить таблицы 😉

Надеюсь, было полезно.
Всех благ!
@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