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

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

Как удалить все пустые столбцы с сохранением названий столбцов
#ВсякоРазно

Всем снова здрасьте.
Делюсь функцией, которая удаляет все пустые столбцы в таблице, и при этом не ломает заголовки столбцов. Оказывается это довольно востребованный функционал, особенно при обработке выгрузок из 1С или файлов, заполняемых шаловливыми ручками. Код приведен сразу с примером применения. Саму функцию публиковал тут.
let
FnRemoveEmptyColumns = (tab as table) =>
Table.RemoveColumns(
tab,
Table.SelectRows(
Table.Buffer( Table.Profile( tab ) ),
each [NullCount] = [Count] )[Column] ),
Source = #table( {"a","b","c"}, {{1,1,null},{2,2,null}} ),
Custom1 = FnRemoveEmptyColumns( Source )
in
Custom1

Функция удаления пустых столбцов
#АнатомияФункций - Table.Profile
Всем привет.
Делюсь решением достаточно часто возникающей задачки - а именно: на входе имеем таблицу, часть столбцов которой являются пустыми, соответственно нужно их удалить
Что ж, напишем функцию:
fnRemEmptyColumns = (tbl)=>
[ a = List.Sort(Table.ColumnNames(tbl)),
b = Table.Profile(tbl,{{"tmp", (x)=>true,List.NonNullCount}})[tmp],
c = List.PositionOf(b,0,Occurrence.All),
d = List.Transform(c,(x)=>a{x}),
e = Table.RemoveColumns(tbl,d)
][e]

Разберем по шагам:
a - получаем список имён столбцов и сортируем его по алфавиту
b - изюминка решения - используем Table.Profile(), но со вторым аргументом (более подробно про него - см. сцыль ниже). Т.е. добавляем дополнительную агрегацию "tmp" для всех столбцов ((x)=>true) с функцией List.NonNullCount. И забираем только этот столбец Table.Profile(...)[tmp]
с - в полученном списке определяем позиции пустых столбцов (обращаю внимание на Occurence.All - мощная штука)
d - номера позиций заменяем названиями (тоже приемчик интересный - как вытащить несколько позиций из списка)
e - удаляем - Вуаля! Задачка решена.

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

источник вдохновения:
https://ssbi-blog.de/blog/technical-topics-english/table-profile-and-its-unknown-second-parameter/

Зачем писать (x)=> вместо each _
#АнатомияФункций – основы, List.Transform
Возьмём простую задачку – имеем список текста

lst = {“1”,”2”,”3”} 

и хотим получить список чисел
Решение банальное:

to = List.Transform(lst, each Number.From(_))

Продвинутые могут сказать, что можно проще:

to = List.Transform(lst,Number.From)

И будут совершенно правы, ведь справка нам говорит
List.Transform(list as list, transform as function)
Т.е. вторым аргументом идёт на самом деле ФУНКЦИЯ – мы её указали, и всё заработало, логично.
В документации на язык M указано, что конструкция each _ является синтаксическим сахаром для (x)=> x; давайте попробуем:

to = List.Transform(lst, (x)=>Number.From(x))

Так тоже работает, но зачем нам это? А вот зачем:
Во-первых, можно сообразить, что List.Transform хочет от нас ЛЮБУЮ функцию, которая принимает на вход один аргумент x и что-то с ним делает

f=(x)=>Number.From(x)*8-11
to = List.Transform(lst,f)

т.е. пишем любую нужную нам функцию от одного аргумента, и далее просто её передаем – для отладки удобно, код более читаем.
А во-вторых, понимаем, что таким образом можно вкладывать одну функцию в другую.
Например задачка – из двух списков получить один (причем к элементам первого списка нужно добавить результат сцепки этого элемента с каждым элементом второго), попробуем решить:

let
    lst1 = {"1".."3"},
    lst2 = {"1".."9"},
    lst = List.Transform(
                        lst1,
                        (x)=>{x}&List.Transform(
                                                lst2,
                                                (y)=>x&y
                                                )
                        ),
    to = List.Combine(lst)
in
    to

Посмотрите на шаг lst – в функцию от (x)=> вложена функция от (y)=> . С each _ так сделать не получится (потому что во вложенных each _ от _ никак нельзя отличить))), а теперь мы получили (на самом деле всегда имели, но не использовали) возможность давать переменным осмысленные имена вместо _ и использовать их в нужных местах кода.
В целом синтаксис (x)=> является основным (ещё раз напоминаю – от нас обычно хотят ФУНКЦИЮ преобразования и мы пишем её в явном виде) и по хорошему ставит мозги на место, позволяя в конечном счёте уйти от наклацанных мышкой шагов к написанию кода на функциях.
Ну и пытливые умы конечно скажут – а можно ли в последнем примере из шага lst сделать функцию? Конечно, мы же теперь знаем, как обозначать переменные:

let
    f=(x,y)=>List.Transform(
                        x,
                        (i)=>{i}&List.Transform(
                                                y,
                                                (j)=>i&j
                                                )
                        ),
    lst1 = {"1".."3"},
    lst2 = {"1".."9"},
    lst = f(lst1,lst2),
    to = List.Combine(lst)
in
    to

Вроде не сложно, а главное прозрачно – именно поэтому я почти не использую each _ и предпочитаю (x)=>

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

#PQ, #pbi
Всем добра! Сегодня покажу как синхронно разворачивать списки из соседних ячеек.
Дано. Получили из API некий JSON который на выходе выдал структуру со вложенными списками как в таблице tbl (см. в коде ниже). Надо развернуть содержимое списков в новые строки, но если мы это сделаем стандартным мышиным способом с разворачиванием списков в строки по очереди то получится декартово произведение, не такое красивое как это но тоже ничего. Что же делать? А на самом деле все довольно просто: надо не разворачивать столбцы, а при помощи функции Table.FromColumns собрать из них готовую таблицу в новом столбце:
let
 tbl = Table.FromRecords (
   {
     [ Company = 1, Phone = { "1".."9" }, Mail = { "1".."6", "", "8".."9" } ],
     [ Company = 2, Phone = { "1".."5" }, Mail = { "2".."6" } ]
   }
 ),
 gentabs = Table.AddColumn(
   tbl,
   "tabs",
   each Table.FromColumns(
     {List.Repeat({[Company]}, List.Count([Phone])),[Phone],[Mail]},
     Table.ColumnNames(tbl)
   )
 ),
 tabs = Table.Combine(gentabs[tabs])
in
   tabs
Но может еще случиться, что столбцов с простым содержимым (не списки) будет много, и тогда использование List.Repeat станет узким местом с точки зрения производительности, да и код сильно потеряет свою наглядность. Но для этого случая можно применить мою любимую функцию Table.FillDown до объединения таблиц и получим точно такой же результат.
let
 tbl = Table.FromRecords (
   {
     [ Company = 1, Phone = { "1".."9" }, Mail = { "1".."6", "", "8".."9" } ],
     [ Company = 2, Phone = { "1".."5" }, Mail = { "2".."6" } ]
   }
 ),
 gentabs = Table.AddColumn(
   tbl,
   "tabs",
   each Table.FromColumns(
     {{[Company]},[Phone],[Mail]},
     Table.ColumnNames(tbl)
   )
 ),
   filled = Table.AddColumn(gentabs, "tabs_", each Table.FillDown([tabs],{"Company"})),
   tabs = Table.Combine(filled[tabs_])
in
   tabs

Зачем нужен [ ][ ] когда есть let in
#АнатомияФункций - основы
Возьмем абстрактный как сферический конь в вакууме запрос:

let
    a = 8,
    b = 16,
    c  = a*b,
    d = a/b
In
    d

На выходе, разумеется, получим 0,5.
Фишка в том, что в недрах спецификации на язык написано «Выражение let можно рассматривать как синтаксический сахар для неявного выражения записи».
Давайте проверим:

[   a = 8,
    b = 16,
    c  = a*b,
    d = a/b ][d]

Выдаст то же самое.
Почему? Запись – это набор полей (её ещё представляют в виде строки таблицы), но кто сказал, что не бывает вычисляемых полей? Пример выше как раз и показывает, что вполне себе бывают.
Соответственно, конструкция [ ][d] – это обращение к конкретному полю записи (аналогично in d).
Зачем нам всё это? Ну для начала – а если мы хотим вывести и произведение, и частное:

[   a = 8,
    b = 16,
    c  = a*b,
    d = a/b ][[c],[d]]

Сразу даст [c=128, d=0,5]
На мой взгляд это несколько проще и понятнее, чем

let
    a = 8,
    b = 16,
    c  = a*b,
    d = a/b
in
    [c=c, d=d]

Хотя эффект будет тем же.
А дальше – вычисления бывают разной степени сложности, иногда отдельный этап требует нескольких связанных вычислений и тогда приходится, либо вкладывать let in друг в друга:

let
    f=(x)=>let 
                a=Text.From(x),
                b=Text.ToList(a),
                c=List.Distinct(b),
                d=b=c 
            in  
                d,
    from = List.Numbers(0,1024),
    to = List.Select(from,f)
in
    to

либо можно упаковать связанные шаги в запись:

let
    f=(x)=>[a=Text.From(x), b=Text.ToList(a), c=List.Distinct(b), d=b=c][d],
    from = List.Numbers(0,1024),
    to = List.Select(from,f)
in
    to

По мне второй вариант проще, хотя это дело вкуса.
Синтаксис через записи обычно более компактный и позволяет визуально локализовать связанные вычисления внутри [ ].
На скорости никак не сказывается, поэтому как удобнее - решать вам.

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

List.Generate, осмысленный и пощадный
#АнатомияФункций – List.Generate
Думаю несложно догадаться, что для начала отправлю читать спецификацию, ибо там сказано:
List.Generate( initial as function,
condition as function,
next as function,
optional selector as nullable function)
Итак, функция, у которой в качестве аргументов выступают четыре функции. Уже страшно.
Для прозрачности далее буду использовать синтаксис с (x)=> (подробнее читаем тут).
Для начала сгенерим список от 1 до 10 (если что я знаю про {1..10} но у нас тут про другое:

List.Generate(  ()=>[i=1],
                (x)=>x[i]<=10,
                (x)=>[i=x[i]+1],
                (x)=>x[i])

initial - просто даёт нам начальное значение 1, но в более сложных случаях мы будем использовать промежуточные вычисления, там нам потребуются вычисляемые поля, поэтому сразу пишем как надо – т.е. через запись
condition – это функция, вопрос в том от чего? Как ни странно, от текущего состояния. Таким образом, x[i] – это текущее состояние счётчика и мы проверяем не превысило ли оно 10
next – а эта функция говорит, что необходимо сделать с текущим состоянием. Поскольку на входе у нас была запись [i=…], на выходе тоже будет она [i=x[i]+1] – т.е. в поле i записали текущее, увеличенное на единицу
selector – нужен, чтобы выводить только интересующие нас поля, в данном случае x[i].
Да, немножко громоздко, но теперь давайте выведем не просто числа от 1 до 10, а накопленную сумму (собственно, для этой задачки функция обычно и используется):

List.Generate(  ()=>[i=1, s= i],
                (x)=>x[i]<=10,
                (x)=>[i=x[i]+1, s=x[s]+i],
                (x)=>x[s])

Теперь
initial – это уже связанные поля [i,s], причем s вычисляется на основе i (про [ ] можно почитать тут)
condition – такой же
next – теперь в записи i вычисляется на основе предыдущего значения прибавлением единицы, а s – прибавлением i к предыдущему значению
selector – на выходе нас интересует именно s, так что счётчик мы используем, но не выводим.
Ну и что-нибудь похожее на боевой пример:

let
    tbl = Table.FromRecords({[a="Вася", b = 2],
                    [a="Коля", b = 4],
                    [a="Петя", b = 8],
                    [a="Евлампий", b = 16]}),
    lst = List.Buffer(tbl[b]),
    n = List.Count(lst),
    gen = List.Generate(()=>[i=0,s=lst{i}],
                        (x)=>x[i]<n,
                        (x)=>[i=x[i]+1,s=x[s]+lst{i}],
                        (x)=>x[s]),
    to = Table.FromColumns(Table.ToColumns(tbl)&{gen},Table.ColumnNames(tbl)&{"cusum"})
in
    to

Разбираем по шагам
tbl - на вход поступила таблица, допустим нам нужна накопленная сумма по столбцу b
lst – получаем столбец в виде списка (обращаю внимание на List.Buffer – здесь оно нужно, потому как к этому списку мы будем многократно обращаться
n – посчитали число элементов в списке (нам же нужно ограничение для счётчика)
gen – генерация столбца, фактически как в примере выше, но с оговорками:
initial - i=0 – потому как нумерация элементов в списке начинается с нуля, s=lst{i} – в сумму идёт i-ый элемент списка lst
condition – здесь условие x[i]<n, потому как нумерация была с нуля и <= даст нам на один элемент больше, чем надо
next – увеличиваем счётчик на 1, к сумме прибавляем i-ый элемент
selector – нас интересует только сумма
to – а вот теперь нужно прилепить новый столбец к таблице, для этого :
Table.ToColumns(tbl)&{gen} - получаем список имеющихся столбцов и добавляем к нему сгенерированный
Table.ColumnNames(tbl)&{"cusum"} – получаем список имен столбцов и добавляем к нему нужно е имя
Ну и упаковываем это всё через Table.FromColumns
Вуаля – столбец накопленной суммы добавлен!
@buchlotnik

Я знаю, что примеры в справке попроще и написаны через each, и код выше можно было оформить проще, но в общем виде оно выглядит именно так – запись на входе, функции от текущего состояния – одна для проверки, вторая для изменения, и функция вывода (которая вообще не обязательная, но вот почему-то в реальности почти всегда используется).

Надеюсь было полезно, всех благ!

Замыкания, или что за зверь (y)=>(x)=>…
#АнатомияФункций – основы
Всем привет, с оказией хочу разобрать такую штуку, как замыкания (closure).
Суть вот в чём. Напишем простой пример:

let
    f=(x,y)=>x/y,
     a=2,
    b=5,
    to=f(a,b),
in
    to

Очевидно, на выходе получим 0,4
Но можно написать и по-другому:

let
    f=(x)=>(y)=>x/y,
     a=2,
    b=5,
    to=f(a)(b)
in
    to

На выходе получим то же самое. Но в этой ситуации мы передали аргументы функции последовательно. И разрыв мозга наступает здесь:

let
    f=(x)=>(y)=>x/y,
    a=2,
    b=5,
    c=f(a),
    to = c(b)
in
    to

Тоже выдаст 0,4! Теперь давайте разбираться:
c – передали функции f только один аргумент, a подставилось вместо x и на выходе мы получили… с=(y)=>2/y, т.е. ФУНКЦИЮ
to – этой новой функции передали b, и уже она выдала конечный результат.
Таким образом, замыкание (кложура!) – это упаковка функции в функцию (это упрощение - да простят меня функциональные теоретики). Думаю, у вас один вопрос – зачем оно надо?! А вот зачем. Разберем немного упрощенный боевой пример:

let
    from = Table.FromRecords({  [n="Вася",a=90,b=70,c=50],
                                [n="Коля",a=120,b=130,c=110],
                                [n="Петя",a=75,b=80,c=130]}),
    nms= Table.ColumnNames(from),
    lst=List.Skip(nms),
    f=(y)=>(x)=>[   a = Record.Field(x,y) , 
                    b = if a < 80 
                        then "ниже предела" 
                        else    if a > 120 
                                then "выше предела" 
                                else "ок"][b],
    g=(x)=>"Проверка "&x,
    acc = List.Accumulate(lst,from,(s,c)=>Table.AddColumn(s,g(c),f(c))),
    to = Table.SelectColumns(acc,{nms{0}}&List.Combine(List.Transform(lst,(x)=>{x,g(x)})))
in
    to

from – на вход подана таблица с несколькими столбцами числовых значений, для каждого столбца нужно добавить столбец с оценкой (проверить нахождение значения в диапазоне от 80 до 120). (а самый жесткий вариант у меня был - 34 столбца и ABC-анализ)
nms – получили все имена,
lst – получен список имён столбцов, по которым нужна оценка
acc – ну самое разумное пройтись по списку lst - List.Accumulate(lst… и добавить для каждого оценку Table.AddColumn(…; только есть проблема – для функции Table.AddColumn мы должны передать функцию от одного аргумента, а именно – от записи (текущей строки) в таблице. А нам нужно обращаться к разным полям, вот так и возникает конструкция

f=(y)=>(x)=>[   a = Record.Field(x,y) , b = if a < 80 then "ниже предела" else    if a > 120 then "выше предела" else "ок"][b],

Т.е. сначала мы функции f будем передавать y – и это будет имя нужного поля записи, и на выходе получать функцию для обработки именно этого поля, что мы и используем в аккумуляторе:
(s,c)=>Table.AddColumn(s,g(c),f(c))
Т.е. добавить к таблице s оценку по полю c - f(c) и назвать столбец g(c)
Зачем надо было писать ещё и функцию g? Да просто преобразование это нужно ещё и в последнем шаге – так немножко короче. Мы же хотим оценку получить рядом, а не в конце таблицы, потому-то на шаге to мы и выбираем столбцы в интересующем нас порядке.
Собственно, всё. Замыкания встречаются нечасто, но бывают просто незаменимы.

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

Коннектор к папке OneDrive Personal
#ВсякиеКоннекторы
Всем добра!
К новому году небольшой подарочек. С наступлением на наши компьютеры ОС Windows 10/11 все больше граждан получают в свое распоряжение облачный сервис OneDrive Personal. В отличие от такого же облака для бизнеса для него отсутствует встроенный в PQ коннектор подключения к папке, чтобы динамически обновлять данные из добавляемых в неё файлов. Тем не менее сама возможность такого забора со стороны Microsoft предусмотрена при помощи API. Ниже прилагаю код функции, которая по аналогии с функцией Folder.Files точно также показывает вам какие файлы имеются в папке на облаке и позволяет работать с их содержимым. Как использовать? Заходим в облако, тыкаем правой кнопкой по папке и выбираем пункт "поделиться", в появившемся меню жмем "копировать ссылку" и уже эту ссылку скармливаем моей функции. Тип авторизации выбираем анонимный. Пользуйтесь на здоровье.
OneDriveFolderFiles = (url)=>
let
     //Функция перекодирования ссылки в понятный формат для API
  fx = (t)=> Binary.ToText( Text.ToBinary( t, TextEncoding.Utf8 ), BinaryEncoding.Base64 ),
API_URL = "https://api.onedrive.com/v1.0/shares/"
     //тащим путь из параметра с адресом к общей папке из облака Onedrive
  FolderUrl = url,
     //преобразовываем ссылку для получения токена для API
  UrlToBase64 = fx( FolderUrl ),
     //заменяем всякое согласно инструкции по ссылке:
     //https://docs.microsoft.com/ru-ru/onedrive/developer/rest-api/api/shares_get?view=odsp-graph-online#encoding-sharing-urls
  Replaced = Text.Replace( Text.Replace( Text.TrimEnd( UrlToBase64, "=" ), "/", "_" ), "+", "-" ),
     //формируем итоговый текстовый параметр для передачи в RelativePath
  EncodedPath = "u!" & Replaced & "/root/children",
     //тащим содержимое папки из API
  Source = Json.Document(Web.Contents( API_URL, [RelativePath = EncodedPath] ) ),
     //преобразовываем полученный JSON в табличку с содержимым папки
  TableFromRecords = Table.FromRecords( Source[value] )[[name],[webUrl]],
     //добавляем столбец с текстовыми параметрами для передачи в RelativePath
  AddColEncodedPaths = Table.AddColumn(
     TableFromRecords,
     "GetRelativePath",
     each "u!" & fx([webUrl]) & "/root/content"
  ),
     //достаем бинарники по сформированным ссылкам, дальше по аналогии как с обычными файлами с диска
  GetBinaries = Table.AddColumn(
     AddColEncodedPaths,
     "Content",
     each Web.Contents(API_URL, [RelativePath = [GetRelativePath]] ),
     Binary.Type
  )
in
  GetBinaries
З.Ы. Если для работы нужны всякие дополнительные свойства файлов или еще какие-то атрибуты, то достаточно в шаге TableFromRecords удалить вот это в квадратных скобках: [[name],[webUrl]]. Тогда увидите все, что может показать API про вашу папку.

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

Евлампий, С Новым Годом!!! или как объединить текстовые значения
#АнатомияФункций – Text.*

Всем привет! С наступающим!
По этому поводу небольшая лёгкая заметка )))
Возьмем простую задачку - есть имя и есть текст поздравления и надо их как-то объединить.
Сразу накидаем вариантов:

let
name = "Евлампий",
congratulation = "С Новым Годом !!!",
lst = {"Акакий","Евлампий","Глафира"},

out1 = name & ", " & congratulation,
out2 = Text.Combine({name,congratulation},", "),
out3 = Text.Format("#{0}, #{1}",{name,congratulation}),
out3a = Text.Format("Глубокоуважаемый и горячо любимый #{0}, в этот знаменательный день пишу Вам, чтобы сказать - #{1}. Желаю здоровья, счастья и успехов!",{name,congratulation}),
out4 = Text.Insert(congratulation,0,name&", "),
out5 = Text.ReplaceRange(congratulation,0,0,name&", "),

fnComb = (x,y)=> Text.ReplaceRange(y,0,0,x&", "),
out6 = fnComb(name,congratulation),
fnClojureComb=(x)=>(y)=>Text.Insert(x,0,y&", "),
out7 = List.Transform(lst,fnClojureComb(congratulation))
in
out7

И разберем -
out1 - простейший вариант через амперсанд
out2 - то же, но через Text.Combine - полезно, когда соединяем несколько значений из списка (обращаю внимание - в список можно объединять результаты разных шагов запроса)
out3 - не самый очевидный вариант, но бывает полезным, когда у вас есть достаточно большой текст, по которому нужно раскидать переменные - как в варианте out3a (обращаем внимание, что переменные опять же передаем списком, хотя есть и вариант передавать их записью)
out4 - текст не обязательно комбинировать, можно же и запихнуть один в другой
out5 - причем запихивать можно и через замену - посмотрите, просто заменяем текст нулевой длины - зачем так? да незачем - просто не бывает какого-то одного правильного варианта, их всегда несколько и вы можете выбирать наиболее удобный и понятный для себя
fnComb - и раз уж мы про функции - любой такой шаг можно превратить в функцию, всё то же самое, просто конкретные названия шагов меняем на абстрактные переменные
out6 - а при использовании функции передаем то, что необходимо
fnClojureComb - ну и куда ж без замыканий - мало ли текст поздравления будет меняться, да и список может быть разным
out7 - пример использования (особо не распространяюсь - подробности были тут)

К чему это всё? Да просто показать, что на одну и ту же задачу можно смотреть очень по-разному, а какой путь выбрать - решение каждого.
Поэтому в Новом Году желаю каждому найти свою функцию и успешно ее применить 😉

Ещё раз с наступающим! Всех благ! 🥳🥳🥳
@buchlotnik

Всем добра из Краснодара! И с наступающим! Не прошло и полгода, а тут уже столько народу.
Всем вам спасибо хочу сказать я, а особенно
@buchlotnik за науку и помощь в развитии канала
@sboy_ko за помощь и чат PQ
@MaximZelensky фактически мой сенсей
@InkognitoUser, @LebedevDmitry и @kkadikin за помощь и чат PBI
Всем подписчикам развития и побольше практики в новом году!
У меня все. Ушел в салат.😊

(x,y)=>x[y] или как правильно обращаться к полям в функции
#АнатомияФункций - Table.Column, Record.Field
Всем привет!
Продолжим рассмотрения вопроса написания пользовательских функций. Сегодня на повестке обращение к элементам и полям.
Начнём с простого:
let
lst = {1,2,3,4,5},
f = (x,y)=>x{y},
to = f(lst,2)
in
to
Вернёт нам 3. Здесь логика, думаю, понятна – нас интересует y-ый элемент списка x. Нумерация с нуля, поэтому 2 обозначает третий))). Зачем такое пихать в функцию? Ну например, чтобы можно было писать вот так:
f = (x,y)=>x{y-1},
to = f(lst,3)
что также вернёт нам 3, но только в этот раз мы явно попросили третий элемент и не думали как там на самом деле идет нумерация. Так бывает удобно.
Но что же с полями записи или таблицы?
let
rec = [a=1,b=2,c=3],
tbl = Table.FromRecords({rec}),
f = (x,y)=>x[y],
to1 = f(rec,"b"),
to2 = f(tbl,"b")
in
to2
Упс…
to1 - «поле y записи не найдено».
to2 – «столбец y таблицы не найден»
Делаем вывод – так не работает (хотя в спецификации на язык явно используется конструкция x[y], но там под y подразумевается именно название поля, а не некое вычисляемое значение).
Решение есть – использовать функции, причем для записей и таблиц они разные – Record.Field и Table.Column соответственно:
let
rec = [a=1,b=2,c=3],
tbl = Table.FromRecords({rec}),
f = (x,y)=>Record.Field(x,y),
g = (x,y)=>Table.Column(x,y),
to1 = f(rec,"b"),
to2 = g(tbl,"b")
in
to2
И теперь всё корректно.
Закономерный вопрос – а зачем вообще нужно запихивать обращение к полям в функции? Рассмотрим пример с группировкой таблицы:
let
tbl = Table.FromRecords({ [n="Вася",a=1,b=2,c=3,d=4],
[n="Антон",a=1,b=3,c=5,d=7],
[n="Вася",a=5,b=6,c=7,d=8],
[n="Антон",a=2,b=4,c=6,d=8]}),
group = Table.Group(tbl, {"n"}, {{"сумма по a", each List.Sum([a]), type number}})
in
group
Так группировка, разумеется, работает. Но что если задача у нас будет найти суммы по всем столбцам, кроме первого? Ладно когда их 4, а если пара десятков? А если их список динамически меняется?
Для начала обратим внимание на эту конструкцию -
{"сумма по a", each List.Sum([a]), type number}
- для каждого столбца нам нужно получить список из трёх параметров {название,функция,тип} (хотя можно обойтись только парой {название,функция})
Далее внимательно смотрим на
each List.Sum([a])
, вспоминаем, что это то же самое, что и (x)=>List.Sum(x[a]) (кому не понятно - читаем тут )
Осталось только сообразить, что под x в данном случае подразумевается таблица (та самая, которую вы получаете выбрав при группировке "все строки").
Теперь мы готовы воспользоваться вышеуказанным приёмом:
let
tbl = Table.FromRecords({ [n="Вася",a=1,b=2,c=3,d=4],
[n="Антон",a=1,b=3,c=5,d=7],
[n="Вася",a=5,b=6,c=7,d=8],
[n="Антон",a=2,b=4,c=6,d=8]}),
nms = Table.ColumnNames(tbl),
lst = List.Transform(List.Skip(nms),(x)=>{"Сумма по "&x,(y)=>List.Sum(Table.Column(y,x)),Int64.Type}),
group = Table.Group(tbl, nms{0}, lst)
in
group

nms – получили имена всех столбцов
lst – получаем список преобразований - для всех столбцов, кроме первого (List.Skip), превращаем каждое название столбца в тройку {название,функция,тип}. Не запутайтесь – x – это название исходного столбца, от него пишем исходное преобразование, "Сумма по "&x – название столбца после группировки; (y)=>List.Sum(Table.Column(y,x)) – вложенная функция, поэтому использована другая переменная y, запись означает «в таблице y возьми столбец x и найди сумму значений»
group – группируем таблицу по первому столбцу (nms{0}) используя список преобразований lst

Собственно, всё (есть дополнение). Столбцы проанализированы пачкой. И это одна из наиболее простых и очевидных задач, решаемых подобным способом.

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

Как правильно обращаться к полям в функции (дополнение)
#АнатомияФункций

Дополнение, которое не влезло в объем основного поста:
1. для конструкций record[[ ],[ ],[ ]] и table[[ ],[ ],[ ]] используйте Record.SelectFields и Table.SelectColumns соответственно
2. для большей прозрачности кода хорошо использовать замыкания (подробнее про них тут):

let
    tbl = Table.FromRecords({   [n="Вася",a=1,b=2,c=3,d=4],
                                [n="Антон",a=1,b=3,c=5,d=7],
                                [n="Вася",a=5,b=6,c=7,d=8],
                                [n="Антон",a=2,b=4,c=6,d=8]}),
    nms = Table.ColumnNames(tbl),
    f=(x)=>(y)=>List.Sum(Table.Column(y,x)),
    lst = List.Transform(List.Skip(nms),(x)=>{"Сумма по "&x,f(x),Int64.Type}),
    to = Table.Group(tbl, nms{0}, lst)
in
    to

@buchlotnik

List.Accumulate – а надо ли?
#АнатомияФункций - List.Accumulate
Всем привет!
По просьбам трудящихся разберем функцию List.Accumulate. Читаем справку:

List.Accumulate(list as list, seed as any, accumulator as function)

list – наш список, seed - опорное значение, accumulator – функция.
accumulator – функция от двух переменных- (state,current)=> где state- текущее накопленное значение (на первой итерации это seed), current – текущий элемент списка.
Для чего используется функция? В сети можно найти много вариантов, доказывающих «универсальность» функции. Тут вам и сумма

List.Accumulate({1..10},0,(s,c)=>s+c)

И преобразование списков

List.Accumulate({1..10},{},(s,c)=>s&{“Column”&Text.From(c)})

И даже работа с таблицами

List.Accumulate({1..9},Table.FromColumns({{1..9}},{"n"}),(s,c)=>Table.AddColumn(s,Text.From(c),(r)=>Record.Field(r,"n")*c))

Проблема в том, что сумму гораздо проще найти через List.Sum, список проще (и быстрее) преобразовать через List.Transform. Это к тому, что функцию List.Accumulate примотать можно к большому числу задач, но это не будет самым простым и быстрым решением. По случаю порылся в архивах и предлагаю пару примеров, где реально в первом приближении без Accumulate не обойтись.

let
    f=(x,y)=>[a=Text.Split(y,":"), b= Record.AddField(x,a{0},a{1})][b],
    g=(x)=>List.Accumulate(Text.Split(x,";"),[],f),

    from = {"название:коробка;высота:11;ширина:120;длина:54",
            "название:бак;ширина:50;высота:120",
            "название:ящик;длина:123;ширина:12"},
    tr = List.Transform(from,g),
    to = Table.FromRecords(tr,null,MissingField.UseNull)
in
    to

from – на вход подана некая выгрузка, в которой присутствуют пары «параметр:значение», разделенные через точку с запятой – а надо собрать таблицу
tr – преобразуем список в список записей (нам же нужно сохранить информацию где поле, где значение)
to - ну и собираем таблицу (второй аргумент null – поскольку набор полей нам заранее не известен, третий - MissingField.UseNull – если в отдельной записи поля не будет вместо ошибки выдаст null – удобно, берем на заметку)
Теперь по функциям:
g – функция от одного аргумента, потому что отвечает за преобразование элемента списка; её работа состоит в разделении текста по «;» и агрегации полученного списка в запись, поэтому в качестве seed – [ ] (пустая запись), а аккумулятор – f
f – функция от двух аргументов (это те же state и current – но использованы x и y дабы показать, что название переменной не имеет значения!) – здесь всё просто: a – поделили текст по «:», b – добавляем к записи новое поле (можно было и через let, но я так не люблю, объяснял тут)
Собственно задача решена. Мне проще было через Accumulate, есть альтернативная точка зрения – использовать рекурсию (Poohkrd оцени, эксклюзивно для тебя):

let
   f=(x,y)=>[a=Text.Split(y,":"), b= Record.AddField(x,a{0},a{1})][b],
   g=(x,y)=>if Text.Contains(y,";") then @g(f(x,Text.BeforeDelimiter(y,";")),Text.AfterDelimiter(y,";")) else f(x,y),

    from = {"название:коробка;высота:11;ширина:120;длина:54",
            "название:бак;ширина:50;высота:120",
            "название:ящик;длина:123;ширина:12;высота:1"},
    tr = List.Transform(from,(x)=>g([],x)),
    to = Table.FromRecords(tr,null,MissingField.UseNull)
in
    to

Смотрите кому как удобнее, по скорости одинаково. Просто помните, что рекурсия – это не быстро, Accumulate выигрыша по скорости не даст, а значит использовать этот приём стоит только в тех ситуациях, когда нет прямой альтернативы или альтернативный путь тернист для написания.

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

Нормализация таблицы с повторяющимися заголовками
#АнатомияФункций - List.Accumulate
Пы.Сы. (основной пост)
там же в архивах нашлась другая, весьма специфическая задачка, также весьма просто решаемая аккумулятором, но на мой вкус это скорее учебный пример – не надо так данные изначально организовывать:

let
    from = Table.FromRecords({
                            [n="Вася",#"a.1"=1,#"b.1"=2,#"c.1"=3,#"a.2"=4,#"b.2"=5,#"c.2"=6,#"a.3"=7,#"b.3"=8,#"c.3"=9],
                            [n="Петя",#"a.1"=1,#"b.1"=3,#"c.1"=5,#"a.2"=7,#"b.2"=9,#"c.2"=11,#"a.3"=13,#"b.3"=15,#"c.3"=17],
                            [n="Коля",#"a.1"=2,#"b.1"=4,#"c.1"=6,#"a.2"=8,#"b.2"=10,#"c.2"=12,#"a.3"=14,#"b.3"=16,#"c.3"=18]}),
    lst = Table.ColumnNames(from),
    nms = List.Distinct(List.Transform(List.Skip(lst),(x)=>Text.BeforeDelimiter(x,"."))),
    cmb = List.Zip({nms,List.Transform(nms, (x)=>List.Select(lst,(y)=>Text.Contains(y,x)))}),
    tbl = Table.TransformColumnTypes(from,List.Transform(List.Skip(lst),(x)=>{x,Text.Type})),
    to = List.Accumulate(cmb,tbl,(s,c)=>Table.CombineColumns(s,c{1},(x)=>Text.Combine(x," "),c{0}))
in
    to

@buchlotnik

List.Accumulate WITH recursion – а что, так можно было?
#АнатомияФункций – List.Accumulate
Всем привет!
Чтобы окончательно закрыть вопрос с аккумулятором и рекурсией (начали тут) давайте разберем ещё один пример. Итак, например, при парсинге сайта, вы получили некий список значений, а вам нужны уникальные. Ну ОК – элементарный код:

let
    lst = {1,2,3,1,2,3,1,2,3},
    to = List.Distinct(lst)
in
    to

Но если б всё было так просто, не было бы статьи. Далеко не всегда значения идут по одному:

let
    lst = {1,2,3,{1,4,5},2,{3,6,7},1,2,3},
    acc = List.Accumulate(lst,{},(s,c)=> s & (if Value.Is(c,List.Type) then c else {c})),
    to = List.Distinct(acc)
in
    to

Здесь на шаге acc мы реализуем следующую логику: «если очередной элемент – список, сделай конкатенацию списков s & c, иначе добавь к списку элемент s &{c}». Тоже вроде просто. Немного усложню код:

let
    lst = {1,2,3,{1,4,5},2,{3,6,7},1,2,3},
    f=(x)=>List.Accumulate(x,{},(s,c)=> s & (if c is list then c else {c})),
    to = List.Distinct(f(lst))
in
    to

обратите внимание, что шаг запроса превращён в функцию (заодно if then записан немножко по-другому - может так кому понятнее или удобнее будет), всё остальное то же самое. Но зачем это надо? А вот зачем – кто сказал, что будут только списки, а не списки списков или списки списков списков… (чувствуете как рекурсией запахло)?

let
    lst = {1,2,3,{1,4,{5,8}},2,{3,{6,9},7},1,2,3},
    f=(x)=>List.Accumulate(x,{},(s,c)=> s & (if c is list then @f(c) else {c})),
    to = List.Distinct(f(lst))
in
    to

А вот это уже гемор. И в этой ситуации, независимо от числа уровней вложенности, мы решаем поставленную задачку с помощью @f(), т.е. рекурсивного вызова нашего аккумулятора. На мой вкус очень просто и элегантно.

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

Table.FromList vs Table.AddColumn – кто кого?
#АнатомияФункций – Table.FromList, Table.AddColumn

Всем привет!
Один известный вам медведь отправил меня читать таких же упоротых, как и я. Ну и подсмотрел идейку. Итак, на входе имеем таблицу с несколькими числовыми столбцами и нужно добавить по каждой строке агрегации – сумму, среднее и медиану. Делов-то:

let
    from = Table.FromRecords({
                                [n="Вася",jan=1,feb=2,mar=3,apr=4,may=5,jun=6],
                                [n="Коля",jan=1,feb=3,mar=5,apr=7,may=9,jun=9],
                                [n="Петя",jan=2,feb=4,mar=6,apr=8,may=8,jun=6]}),
    tbl = Table.Buffer(Table.Repeat(from,99999)),
    add = Table.AddColumn(tbl,"сумма",(r)=>List.Sum(List.Skip(Record.ToList(r)))),
    add1 = Table.AddColumn(tbl,"среднее",(r)=>List.Average(List.Range(Record.ToList(r),1,6))),  
    to = Table.AddColumn(tbl,"медиана",(r)=>List.Median(List.Range(Record.ToList(r),1,6)))
in
    to

Что смущает в приведенном коде – обилие шагов, необходимость на каждом шаге вычислять требуемый диапазон для расчёта, ну и Table.Buffer - если забудете поставить, вычисляться запрос будет крайне долго, а так на моей машине потребовалось 13 секунд. Мысль с добавлением сразу всех агрегаций в виде записи с последующим Table.ExpandRecordColumn отметайте - это дорогая операция, особенно на больших таблицах – получилось вообще 17 секунд.
Но что же делать? Чуйка подсказывает, что со списками работа шустрее, чем на записях. Что ж, пробуем:

let
    f=(x)=>x&[a=List.Skip(x),b={List.Sum(a),List.Average(a),List.Median(a)}][b],
    
    from = Table.FromRecords({
                                [n="Вася",jan=1,feb=2,mar=3,apr=4,may=5,jun=6],
                                [n="Коля",jan=1,feb=3,mar=5,apr=7,may=9,jun=9],
                                [n="Петя",jan=2,feb=4,mar=6,apr=8,may=8,jun=6]}),
    tbl = Table.Repeat(from,99999),
    lst = Table.ToRows(tbl),
    tr = List.Transform(lst,f),
    nms = Table.ColumnNames(from)&{"сумма","среднее","медиана"},
    to = Table.FromRows(tr,nms)
in
    to

Вроде выглядит неплохо: lst – разбили на строки, tr – преобразовали строки списком, f - функция, добавляющая к списку его сумму, среднее и медиану, nms – список столбцов новой таблицы, to- собрали таблицу из строк. Обращаю внимание – буферить не потребовалось, НО скорость та же - 13 секунд. Мдя. Собственно, с этой мыслью я и жил до вчера. Но тут напомнили, что в Table.FromList можно передавать не только Splitter:

let
    f=(x)=>x&[a=List.Skip(x),b={List.Sum(a),List.Average(a),List.Median(a)}][b],
    
    from = Table.FromRecords({
                                [n="Вася",jan=1,feb=2,mar=3,apr=4,may=5,jun=6],
                                [n="Коля",jan=1,feb=3,mar=5,apr=7,may=9,jun=9],
                                [n="Петя",jan=2,feb=4,mar=6,apr=8,may=8,jun=6]}),
    tbl = Table.Repeat(from,99999),
    lst = Table.ToRows(tbl),
    nms = Table.ColumnNames(from)&{"сумма","среднее","медиана"},
    to = Table.FromList(lst,f,nms)
in
    to

Что поменялось? Да почти ничего – просто вместо двух шагов (tr и to) остался один; функция f та же, только скорость – 10 секунд! - 25% по производительности на пустом месте. Вот так, мир не рухнул – на списках всё-таки быстрее 😉

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

источник вдохновения - в комментариях от Lossev

Splitter.SplitTextByPositions или плач по регуляркам
#АнатомияФункций - Splitter.SplitTextByPositions
Всем привет! Разберем сегодняшний сабж по разделению текста. Имеем:

let
    from = Table.FromRecords({
        [name = "Вася",txt = "1. Текст 1-1. Текст с пробелами 1-2. текст с числами 123 2. ещё текст"],
        [name = "Коля", txt = "3. текст. 4. а текст-то бывает разный 5. например с числами 2.5"],
        [name = "Евлампий", txt ="12. и номеров много 123. очень много 1234. прям совсем"]
    }),
    tr = Table.TransformColumns(from,{"txt",SplitByAnyChars({"0".."9","-"},{"."})}),
    to = Table.ExpandListColumn(tr, "txt")
in
    to

Текст в табличке нужно разделить на пункты, причем: сами номера пунктов нужно сохранить; номер пункта – это набор цифр переменной длины (возможны подпункты через дефис) и обязательно точка в конце; в тексте могут встречаться и цифры, и дефисы, и точки.
Мдя, задачка для регулярок, которых в M толком и не завезли. Но как видим проблема элегантно решается вызовом функции SplitByAnyChars, только есть проблема – такой функции на самом деле нет и её нужно написать )))
Не претендую на оптимальность, но думаю, что логика решения может оказаться небезынтересной. Поехали:

SplitByAnyChars = (chars as list, last as list)=>(txt as text)=>
let
    lst = Text.ToList(txt),
    tbl = Table.FromList(lst,
                        (x)=>[ a = List.Contains(chars,x),b = List.Contains(last,x),c = a or b,d = {a,b,c}][d],
                        {"chars","last","all"}),
    index = Table.AddIndexColumn(tbl, "i", 0, 1),
    group = Table.Group(index, "all", {{"n", each List.Min([i])},
                                        {"l", each List.Last([last])},
                                        {"c", each List.First([chars])}},
                                        GroupKind.Local),
    filtr = Table.SelectRows(group, each ([l] = true) and ([c] = true)),
    to = Splitter.SplitTextByPositions(filtr[n])(txt)
in
    to

Во-первых, аргументы – chars – список символов, которые могут присутствовать в разделителе, last – список символов, на которые должен заканчиваться разделитель, txt – разделяемый текст (обращаю внимание, что аргументы разделены на две группы – так проще вызывать функцию и не нужно каждый раз городить (x)=>Split… Это называется замыкание и описано тут).
lst – преобразовали наш текст в список символов
tbl – первая изюминка решения – вместо списка символов получаем таблицу из трех столбцов: «chars» - относится ли символ к тем, которые должны быть в разделителе; «last»-относится ли к конечным; «all» - относится ли к одной из этих двух категорий
index – добавляем индексный столбец с нумерацией с нуля, как в списках
group – вторая изюминка – таблица нам была нужна, чтобы сделать группировку по столбцу «all», причем с параметром GroupKind.Local – в этой ситуации строки будут группироваться последовательно и каждая группа символов, удовлетворяющих условию, получит свою строку. При этом мы добавляем столбцы: n – минимальный индекс, т.е. с какой позиции начинается группа; l – относится ли последний символ в группе к тем, на которые должен заканчиваться разделитель; с – относится ли первый символ в группе к тем, которые составляют тело разделителя.
filtr – теперь оставляем только те строки, для которых вышеуказанные условия выполняются
to – ну и вишенка – в полученной таблице в индексном столбце содержатся ровно те позиции, по которым нужно разделить текст. Позиции передаем в Splitter.SplitTextByPositions – думаю из названия понятно, что делает данная функция. Вот только у сплиттера нет аргумента, отвечающего за сам разделяемый текст, я даже от кого-то слышал, что его вообще надо использовать только в составе других функций… С другой стороны, мы же знаем про замыкания – берем и насильно передаем текст во вторых скобках… и оно работает!
Как-то так. Скорость не заоблачная, но вполне терпимая.
Надеюсь, было полезно.

Всех благ!
@buchlotnik