Анатомия Функций 001: Зачем писать (x)=> вместо each _
Зачем писать (x)=> вместо each _
(Анатомия Функций – основы)
Возьмём простую задачку – имеем список текста
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)=>
Надеюсь было полезно, всех благ!
Анатомия Функций 002: Зачем нужен [ ][ ] когда есть let in
Зачем нужен [ ][ ] когда есть 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
По мне второй вариант проще, хотя это дело вкуса.
Синтаксис через записи обычно более компактный и позволяет визуально локализовать связанные вычисления внутри [ ].
На скорости никак не сказывается, поэтому как удобнее - решать вам.
Надеюсь было полезно, всех благ!
Анатомия Функций 003: List.Generate, осмысленный и пощадный
List.Generate, осмысленный и пощадный
(Анатомия Функций – List.Generate)
Всем привет.
Думаю несложно догадаться, что для начала отправлю читать спецификацию, ибо там сказано:
List.Generate( initial as function,
condition as function,
next as function,
optional selector as nullable function
)
Итак, функция, у которой в качестве аргументов выступают четыре функции. Уже страшно.
Для прозрачности далее буду использовать синтаксис с (x)=>
(подробнее читаем Анатомия Функций 001).
Для начала сгенерим список от 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
(про [] можно почитать -
Анатомия Функций 002)
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
Вуаля – столбец накопленной суммы добавлен!
Я знаю, что примеры в справке попроще и написаны через each, и код выше можно было оформить проще, но в общем виде оно выглядит именно так – запись на входе, функции от текущего состояния – одна для проверки, вторая для изменения, и функция вывода (которая вообще не обязательная, но вот почему-то в реальности почти всегда используется).
Надеюсь было полезно, всех благ!
Анатомия Функций 004: Table.Profile, Функция удаления пустых столбцов
Функция удаления пустых столбцов
(Анатомия Функций – 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 - удаляем - Вуаля! Задачка решена.
Надеюсь было полезно. Всех благ!
Анатомия Функций 005: Замыкания, или что за зверь (y)=>(x)=>…
Замыкания, или что за зверь (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 мы и выбираем столбцы в интересующем нас порядке.
Собственно, всё. Замыкания встречаются нечасто, но бывают просто незаменимы.
Надеюсь было полезно, всех благ!
Анатомия Функций 006: Text.*
Как объединить текстовые значения.
Анатомия Функций 006
Всем привет!
Возьмем простую задачку - есть имя и есть текст поздравления и надо их как-то объединить.
Сразу накидаем вариантов:
let
name = " Евлампий ",
congratulation = С Новым Годом !!! ",
lst = {" Акакий "," Евлампий "," Глафира "},
out1 = name & ", " & congratulation,
out2 = Text.Combine ({name,congratulation},", "),
out3 = Text.Format ("#{0}, #{1}",{name,congratulation}),
out3a = Text.Forma (" Глубокоуважаемый и горячо любимый #{0}
, в этот знаменательный день пишу Вам, чтобы сказать - #{1}
. Желаю здоровья, счастья и успехов! ",{name,congratulation}),
out4 = T 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 (congratulation))
in
out7
И разберем -
out1
- простейший вариант через амперсанд
out2
- то же, но через
Text.Combine
- полезно, когда соединяем несколько значений из списка (обращаю внимание -
в список можно объединять результаты разных шагов запроса)
out3
- не самый очевидный вариант, но бывает полезным, когда у вас есть достаточно большой текст,
по которому нужно раскидать переменные - как в варианте
out3a
(обращаем внимание, что переменные опять же передаем списком, хотя есть и вариант передавать их записью)
out4
- текст не обязательно комбинировать, можно же и запихнуть один в другой
out5
- причем запихивать можно и через замену - посмотрите, просто заменяем текст нулевой длины - зачем так? да незачем - просто не бывает какого-то одного правильного варианта, их всегда несколько и вы можете выбирать наиболее удобный и понятный для себя
fnComb
- и раз уж мы про функции - любой такой шаг можно превратить в функцию, всё то же самое,
просто конкретные названия шагов меняем на абстрактные переменные
out6
- а при использовании функции передаем то, что необходимо
fnClojureComb
- ну и куда ж без замыканий - мало ли текст поздравления будет меняться, да и список может быть разным
out7
- пример использования (особо не распространяюсь - подробности были тут
( Анатомия Функций 005))
К чему это всё? Да просто показать, что на одну и ту же задачу можно смотреть очень по-разному, а какой путь выбрать - решение каждого.
Поэтому в Новом Году желаю каждому найти свою функцию и успешно ее применить 😉
АнатомияФункций 007: Table.Column, Record.Field
(x,y)=>x[y]
Анатомия Функций 007
Всем привет!
Продолжим рассмотрения вопроса написания пользовательских функций.
Сегодня на повестке обращение к элементам и полям.
Начнём с простого:
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])
(кому не понятно - читаем тут ( Анатомия Функций 001) )
Осталось только сообразить, что под 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
1. для конструкций
record[[ ],[ ],[ ]]
и
table[[ ],[ ],[ ]]
используйте
Record.SelectFields
и
Table.SelectColumns
соответственно
2. для большей прозрачности кода хорошо использовать замыкания (подробнее про них тут ( Анатомия Функций 005)):
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}),
to = Table.Group (tbl, nms{0}, lst)
in
to
Собственно, всё. Столбцы проанализированы пачкой. И это одна из наиболее простых и очевидных задач, решаемых подобным способом.
Надеюсь, было полезно. Всех благ!
АнатомияФункций 008: List.Accumulate
List.Accumulate – а надо ли?
Анатомия Функций 008
Всем привет!
По просьбам трудящихся разберем функцию 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 , есть альтернативная точка зрения – использовать рекурсию:
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 выигрыша по скорости не даст, а значит использовать этот приём стоит только в тех ситуациях, когда нет прямой альтернативы или альтернативный путь тернист для написания.
Нормализация таблицы с повторяющимися заголовками там же в архивах нашлась другая, весьма специфическая задачка, также весьма просто решаемая аккумулятором, но на мой вкус это скорее учебный пример – не надо так данные изначально организовывать:
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
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 009: List.Accumulate WITH recursion
List.Accumulate WITH recursion – а что, так можно было?
Анатомия Функций 009
Всем привет!
Чтобы окончательно закрыть вопрос с аккумулятором и рекурсией давайте разберем ещё один пример.
Итак, например, при парсинге сайта, вы получили некий список значений, а вам нужны уникальные. Ну ОК – элементарный код:
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
А вот это уже гемор. И в этой ситуации, независимо от числа уровней вложенности, мы решаем поставленную задачку с помощью @f() , т.е. рекурсивного вызова нашего аккумулятора. На мой вкус очень просто и элегантно.
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 010: Table.FromList, Table.AddColumn
Table.FromList vs Table.AddColumn – кто кого?
Анатомия Функций 010
Всем привет!
Один известный вам медведь отправил меня читать таких же упоротых, как и я. Ну и подсмотрел идейку. Итак, на входе имеем таблицу с несколькими числовыми столбцами и нужно добавить по каждой строке агрегации – сумму, среднее и медиану. Делов-то:
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% по производительности на пустом месте. Вот так, мир не рухнул – на списках всё-таки быстрее 😉
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 011: Splitter.SplitTextByPositions
Splitter.SplitTextByPositions или плач по регуляркам
Анатомия Функций 011
Всем привет!
Разберем сегодняшний сабж по разделению текста. Имеем:
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
– думаю из названия понятно, что делает данная функция. Вот только у сплиттера нет аргумента, отвечающего за сам разделяемый текст, я даже от кого-то слышал, что его вообще надо использовать только в составе других функций… С другой стороны, мы же знаем про замыкания – берем и насильно передаем текст во вторых скобках… и оно работает!
Как-то так. Скорость не заоблачная, но вполне терпимая.
Всех благ!
АнатомияФункций 012: Запрос в одну строку или функции в отдельные шаги?
Запрос в одну строку или функции в отдельные шаги?
Анатомия Функций 012
Всем привет!
Потянуло пофилософствовать. Итак, возьмем запрос
let
lst = {1..12},
trnsf = List.Transform(lst,(x)=>Date.ToText(#date(2022,x,1),"MMMM yyyy")),
to = Text.Combine(trnsf,"#(lf)")
in
to
который совершенно не обязательно писать как выше, зачем вообще два раза указывать последний шаг:
let
lst = {1..12},
trnsf = List.Transform(lst,(x)=>Date.ToText(#date(2022,x,1),"MMMM yyyy"))
in
Text.Combine(trnsf,"#(lf)")
Работает так же, некоторые считают, что так даже нагляднее (автор не разделяет данную точку зрения). В целом можно считать (упрощённо), что отдельные шаги – это просто куски кода, которым дали имя, и когда вы ссылаетесь на конкретный шаг – вместо имени этот кусок кода и подставляется. Поэтому если мы напишем просто:
Text.Combine(List.Transform({1..12},(x)=>Date.ToText(#date(2022,x,1),"MMMM yyyy")),"#(lf)")
это будет работать. Почему? Потому что в принципе весь запрос представляет собой одно выражение, его можно не делить на шаги и писать вот так в одну (но очень большую и совершенно нечитаемую) строку (т.е. вы можете использовать PQ как калькулятор – просто напишите 8*(127-13) и он вам выдаст результат).
Но ведь можно задуматься и об обратном – мы для того и делим запрос на шаги, чтобы повысить его читаемость, а значит вместо вкладывания кучи функций друг в друга, можно поделить запрос на шаги, особенно если фрагмент повторяется или сложный и требует вложенных конструкций let in – выносим его в отдельный шаг с определенным именем, потом вызываем по необходимости.
Например, код выше можно переписать так:
let
lst = {1..12},
f=(x)=> Date.ToText(#date(2022,x,1),"MMMM yyyy"),
trnsf = List.Transform(lst,f),
to = Text.Combine(trnsf,"#(lf)")
in
to
получилось –
f
– это функция преобразования номера месяца в дату с определённым форматированием, а на шаге
trnsf
– мы эту функцию используем для преобразования элементов списка. По скорости оба варианта эквивалентны, но с точки зрения читаемости второй вариант на мой вкус лучше, особенно если мы говорим про сложные боевые примеры:
let
from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
group = Table.Group(from, "a", {{"sum b", each List.Sum(List.FirstN(List.Sort([b],Order.Descending),3))},{"sum c",each List.Sum(List.FirstN(List.Sort([c],Order.Descending),3))}})
in
group
хорошо, если столбцов два, а когда больше? А так мы выносим функцию и получаем счастье:
let
from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
f=(y,z)=>(x)=>[ a = Table.Column(x,y),
b = List.Sort(a,Order.Descending),
c = List.FirstN(b,z),
d = List.Sum(c)][d],
group = Table.Group(from, "a", {{"sum b", f("b",3)},{"sum c",f("c",3)}})
in
group
Обращаю внимание – на шаге
f
использовано замыкание
– мы просто передаем функции имя столбца и число элементов, которые надо просуммировать – мало ли что поменяется; расписали по шагам – так нагляднее, но согласитесь – больше одного раза вы бы вряд ли стали это делать.
В целом этим постом я хотел сказать, что написание функций – это не какая-то запредельная задача – это просто вынесение определённых повторяющихся шагов в шаг с определенным именем для многократного и удобного использования в коде.
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 013: Table.Group – Часть 1
Table.Group – Часть 1. Третий аргумент
Анатомия Функций 013
Всем привет!
По запросам страждущих немножко опишу работу с этой замечательной функцией. Начнём с простого:
let
from = Table.FromRecords(Json.Document(Binary.Decompress
(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BO
jmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7
kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVB
CglJaizBhSailSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+
brCR2GeDX6Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
f=(x)=>List.Sum(x[b]),
group = Table.Group(from, "a", {"sum b",f})
in
group
Посмотрим на аргументы
Table.Group
Первый аргумент
– сама таблица
Второй аргумент
– столбец или список столбцов, по которым нужно группировать (обращаю внимание – мышкоклацанием один столбец все равно будет записан как список из одного элемента -
{“c”}
. Какая разница? – спросите вы – Дойдем до пятого аргумента – объясню – отвечу я )
Третий аргумент
– агрегация или список агрегаций, т.е. либо
{«агрегирующий столбец»,функция_агрегации}
, либо
{{«агрегирующий столбец1»,функция_агрегации1 },{«агрегирующий столбец2»,функция_агрегации2}}
И вот тут нужно разобраться. Функция агрегации вычисляет что-то на основании результата группировки, но что является этим результатом? Подставьте в запрос выше вместо функции f такое f=(x)=>x – и вы увидите, что результатом является таблица. И это самое важное знание.
Разберем
f=(x)=>List.Sum(x[b])
x[b]
– это обращение к столбцу таблицы, которое дает нам список значений, далее мы этот список суммируем (в варианте
each List.Sum([b]
) который вы получите мышкоклацанием это не очевидно)
Теперь же мы понимаем, что с таблицей можно творить всякое – допустим вам нужно не просто получить сумму, а найти суммарную разницу по двум столбцам (можно конечно допстолбец добавить или пойти через
CombineColumns
, но мы же не хотим ничего усложнять):
let
from = ...//взять из первого запроса,
f=(x)=>List.Sum(x[b])-List.Sum(x[c]),
group = Table.Group(from, "a", {"diff b c",f})
in
group
Обращаю внимание – с первого запроса поменялась только функция f (это к тому, что функцию лучше вынести, чем каждый раз лазать в дебри запроса). Как видим – с несколькими полями всё также прекрасно работает. Ну и раз уж аргументом является таблица, можем задействовать всю мощь табличных функций. Например, часто спрашивают как добавить нумерацию, но внутри группы:
let
from = ...//взять из первого запроса,
f=(x)=>Table.AddIndexColumn(x,"i",1,1),
group = Table.Group(from, "a", {"tmp",f}),
to = Table.Combine(group[tmp])
in
to
Думаю, с работой функции для добавления индексного столбца сложностей не возникнет. А вот шаг to – просто обратите внимание – в столбце tmp у нас таблицы, group[tmp] – список этих таблиц, и этот список мы отдаём Table.Combine – всё, задача решена.
Или такое – нужно получить по каждому сотруднику лучшую продажу или последнюю дату – это решается через Table.Max :
let
from = ...//взять из первого запроса,
f=(x)=>Table.Max(x,"b"),
group = Table.Group(from, "a", {"tmp",f}),
to = Table.FromRecords(group[tmp])
in
to
Обращаю внимание – Table.Max возвращает запись, поэтому в шаге to мы используем Table.FromRecords
Вот, пожалуй, и всё – если вы понимаете, что агрегируете (точнее с чем приходится работать вашей функции) задачи решаются просто и элегантно.
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 014: Table.Group - Часть 1.1
Table.Group – Часть 1.1 Третий элемент третьего аргумента – тип данных
Анатомия Функций 014
Всем привет!
Продолжаем изыскания по
Table.Group
– надо обсудить третий элемент (это не влезло в предыдущий пост), а именно: полный синтаксис агрегации выглядит как
{«агрегирующий столбец1»,функция_агрегации1, тип_данных_агрегации }
Суть в том, что при группировке мы можем сразу определить тип столбца на выходе. Возьмем пример из прошлого поста:
let
from = Table.FromRecords(Json.Document(Binary.Decompress
(Binary.FromText("rZM7EsIwDETv4jpFJPkj5yp
AQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd7mML1NUZh
0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ
6e7JsNT9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3E
B5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak9O3MT
YE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Sl
z4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
f=(x)=>List.Sum(x[b]),
group = Table.Group(from, "a", {"sum b",f,Int64.Type})
in
group
Здесь добавлен целочисленный тип Int64.Type – мне так захотелось, поскольку столбец целочисленный. Но обращаю внимание – мышкоклацанием вы получите type number – т.е. число с плавающей точкой (судя по всему автоматически определяется только примитивный тип (https://docs.microsoft.com/ru-ru/powerquery-m/m-spec-types), независимо от того, какой столбец агрегируется), поэтому будьте внимательны и проверяйте данный момент, если группируете через интерфейс. Аналогично для таблицы:
let
from = Table.FromRecords(Json.Document(Binary.Decompress
(Binary.FromText("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWT
uzNoU5BOjmm28DyttbJ8eYd7mML1NUZh0/GrkUzXMIQ5TJyHsI
Splm3w4Al4Hn247O7kw1kNJ6e7JsNT9uF5RDPqdK+Gx+qMSk29
ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSailSOlNUMDBtak
9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6Slz4Ux
u9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
f=(x)=>Table.AddIndexColumn(x,"i",1,1),
group = Table.Group(from, "a", {"tmp",f, type table [b = Int64.Type,c = Int64.Type,i = Int64.Type]}),
to = Table.ExpandTableColumn(group, "tmp", {"b", "c", "i"})
in
to
Обращаю внимание – тип в данном случае задан не только для исходных столбцов, но и для добавляемого столбца индекса "i" , т.е. типизируем мы конечный результат, что удобно и не может не радовать.
Ложка дёгтя состоит в том, что если вместо ExpandTableColumn вам нужно использовать Combine - данные о типах слетают, но это дело поправимое - просто переносим типизацию на следующий шаг:
let
from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText
("rZM7EsIwDETv4jpFJPkj5ypAQQK5ARWTuzNoU5BOjmm28DyttbJ8eYd
7mML1NUZh0/GrkUzXMIQ5TJyHsISplm3w4Al4Hn247O7kw1kNJ6e7JsNT
9uF5RDPqdK+Gx+qMSk29ixjuHDtF9CIH3EB5GIjSJ05QVBCglJaizBhSa
ilSOlNUMDBtak9O3MTYE2oaHvb80Byln1eaf1wW09VUTev+brCR2GeDX6
Slz4Uxu9SZqeAbxc5MhO3kzlCa/xKKscGJttsH"),Compression.Deflate))),
f=(x)=>Table.AddIndexColumn(x,"i",1,1),
group = Table.Group(from, "a", {"tmp",f}),
to = Table.Combine(group[tmp], type table [a = text, b = Int64.Type,c = Int64.Type,i = Int64.Type])
in
to
Вуаля - все работает как надо, таблица на выходе типизирована, как мы и хотели.
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 015: Table.Group – Часть 2
Table.Group – Часть 2. Четвертый аргумент
Анатомия Функций 015
Всем привет!
Закончили с агрегациями - переходим к четвертому аргументу. Здесь не буду вас долго утомлять:
Во-первых, он есть
Во-вторых, возможны всего лишь два варианта –
GroupKind.Global
и
GroupKind.Local
Если вы никогда не писали четвертый аргумент, имейте в виду, что вы пользовались
GroupKind.Global
Разница состоит в том, что
Global
объединяет все значения с данным ключом в одну группу, а
Local
– все последовательно идущие значения с ключом в одну группу.
Когда нам это надо? Например, вы группируете по месяцам, но не хотите, чтобы январь 2021 сгруппировался с январём 2022, или идёт сменная/вахтовая работа, и вам важно понять выработку за каждую вахту:
let
from = Table.FromRecords(Json.Document(Binary.Decompress
(Binary.FromText("zZe7agNBDEX/ZWsL9JjZh78
jXZwigXyCK+N/t2aNiZ3CXJAKwSC2GC2n2KO9+rxM
pzM3a3vlUZven6fjpKxKLH4+mI/7mQ73Bn1q6P/a3
l+Ql1e0n73y322Tvf4+9Xw/Xi3XAwRsXATYIGAlXY
sAdwjYSLciwAsE3EiXIsAbBNxJ5yLAglk3k7YqxJh
2C6klEmuEGPNuJc0cxSFiTLyNJHNShIgh84RJModx
hFgh80RIMmdFiBgyT/z0KsSAeTYyhWTOish0U8A8G
6FCqsQ2BcyzkSpEixAbYJ6NWCFlkiZgno1cwVWSm2
Hmzf6jrkKMmbc4dJHpZph5q3/KRYgbZt7mA64KMWS
e54rUNTpEDJnnxKl7dIgYMs+TUOoiHSKGzPMklLqY
Roi7XL9u"),Compression.Deflate))),
f=(x)=>[a=(y)=>Text.From(Date.From(Text.Split(y,"T"){0})),
b = a(List.Min(x))&" - "&a(List.Max(x))][b],
to = Table.Group(from, "вахта", {
{"период",(x)=>f(x[дата]), Text.Type},
{"выработка",(x)=>List.Sum(x[выработка]), Int64.Type}
},
GroupKind.Local)
in
to
Просто сравните выполнение запроса с четвёртым параметром и без. Ещё обращаю внимание на шаг f – функции можно упаковывать в функции – почему бы и нет, компактно и надеюсь наглядно. Ну и надо сделать ещё пару замечаний - GroupKind.Global и GroupKind.Local – это НЕ функции, а константы – в чём легко убедиться выполнив Number.From(GroupKind.Global) и Number.From(GroupKind.Local) – выдаст 1 и 0 соответственно. Т. е. если вы относитесь к людям, которые верят, что чем короче код, тем он быстрее – заменяйте громоздкое выражение на числовое (но вообще не рекомендую – значения констант быстро забываются, но помнить о них надо – на просторах интернета встречал использование числовых значений, теперь вы будете знать, что это за нолик).
Ну и самое главное – сам по себе GroupKind.Local не особо интересен, вся его мощь просыпается, когда мы дополнительно пишем пятый аргумент – о нём мы и поговорим в следующей части
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 016: Table.Group – Часть 3.1
Table.Group – Часть 3.1 Пятый аргумент - основной пример и описание
Анатомия Функций 016
Всем привет!
Нам осталось сталось самое интересное. Мы уже разобрались с тем, что агрегации при группировке могут быть любыми, их удобно делать с помощью функций, выяснили, что можно идти последовательно по строкам, используя GroupKind.Local . А теперь возникает вопрос – что если в столбце могут быть каки-то метки (разные) и мы хотим группировать по каким-то значениям из списка, или мы хотим сгруппировать записи хронологически по неделям (можно, конечно, сделать допстолбец с номером недели, но мы хотим как-то проще) или надо ориентироваться на разницу между строками – группировать только пока показатель растет и т.п. Думаю вы догадались, что так МОЖНО. Осталось только разобраться как.
Пятый аргумент –
comparer
– это функция от двух аргументов, возвращающая
0
или
1
. В качестве аргументов выступают значение, с которого начинается группа и текущее значение -
state
и
current
(
s
и
c
,
x
и
y
– кому как больше нравится). Соответственно, пока функция возвращает ноль – строки объединяются,
как только единицу – начинается следующая группа. И это важный момент – нам нужны именно
0
и
1
Пример возьмем общий, в дальнейшем шаг
from
я не буду заново повторять и саму агрегацию возьмем самую простую
{"tmp", each _ }
- чтобы просто был виден результат группировки:
let
from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText
("5ZrLboMwEEX/hXUiecZjXr/SdtG8VlF3WVX99xJDhBsaehtsC7AUWSRge8KRDWfsl8
/s9aJEiy3VtRRuj7M6Y8W8VdR8so29TnJ7rrTluy13WU3tSX205cmWxl6unGadqnJqGr
dfD/0Feucci9OD6jrv6rLTl+7aEW1LGgRQOr0cb+3YFmR3H6GmvuWul1sAtfqlqnECka
yuzMa9l8e+gTY6ve/jaiMS7sInezHZwFltlWpu+NcGQ8OjaD4u5/PTdP64OXPgQgiY3CM
YhsHopMFw7BGjYTCSNBiNgBGPYAQGY8KBYW6DWwoAn1OWgQHkSY8MQcCUHsHkMJhiFAyvm
YqJTaWAqZRJD5ccAENKeSRTwmSqWZOh+7q6msLBRH6eVCgGUuEwLMYcITzV83haMLrHA6s
jjVu9XvPkVSBUCo9UYG+ktIW+DDybDcDA3khpC30FgJn0uB+QgcWRxo1e1oyFVOwBA+skB
fT5JZCh2GRgn6S0RZ84NhnYKWnc9Ke9Mu/dfzioeHA6LB42FdtuIFb+NLOt3iBr3AVFFjA
NsBjLwYbUBM15QAmXnXmnBILz0VGH0RUNbDwcME2wBDQS2EUHZGDl4fEMgVk1FoNgYY9YY
N/htFMEBKXUPC4JEKw8PO8cgedXsyLySibBgsMB1/iX804G8fE5TmDN4cRTA2XstTOC1854
PDeQr5pLFXvEwGtpHDAxsAAyrCI/axj2f057GwAjWzC9zmUMSz+nLf0cehPmzzyn/Un+tVF
DB1T/GW35w0D4XG5unitv3w=="),Compression.Deflate))),
group = Table.Group(from, "итог", {"tmp", each _}, GroupKind.Local, (s,c)=>if c="успешно" then 0 else 1)
in
group
Обращаю внимание – просто логическое выражение, возвращающее
true
или
false
, воспринято не будет, поэтому была использована конструкция
if then else
. Другое дело, что чаще используют другой вариант:
group = Table.Group(from, "итог", {"tmp", each _}, GroupKind.Local,(s,c)=>Number.From(c<>"успешно"))
Получим то же самое. Обратите внимание, что
Number.From()
не является обязательной функцией, не важно как именно были получены
0
и
1
, просто с ней работает немного быстрее, чем через
if
, но вы можете писать как вам удобнее.
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 017: Table.Group – Часть 3.2
Table.Group – Часть 3.2 Пятый аргумент - варианты использования
Анатомия Функций 017
Всем привет!
Освоили базу, теперь пройдёмся по вариантам –
Группировать по любому ненулевому значению
group = Table.Group(from, "цикл", {"tmp", each _},GroupKind.Local,(s,c)=>Number.From(c<>null))
Группировать по конкретному значению
group = Table.Group(from, "итог", {"tmp", each _}, GroupKind.Local, (s,c)=>Number.From(c="сбой"))
Группировать по значению из списка
group = Table.Group(from, "операция", {"tmp", each _},GroupKind.Local,(s,c)=>Number.From(List.Contains({"наладка","ТО"},c)))
Группировать по условию
group = Table.Group(from, "выход", {"tmp", each _},GroupKind.Local,(s,c)=>Number.From(c<95))
Смысл, думаю, понятен. Но что же со
state
-том? Мы его не используем – он, что совсем не нужен? Нужен! Ой как нужен! – группируем, когда отличается первые/последние/любые в середине символы в значении:
group = Table.Group(from, "документ", {"tmp", each _}, GroupKind.Local, (s,c)=>Number.From(Text.Start(s,3)<>Text.Start(c,3)))
Группируем, если разница между первой и последней строкой в группе превысила определенное значение
group = Table.Group(from, "выработка", {"tmp", each _},GroupKind.Local,(s,c)=>Number.From(c-s>50))
Ещё интересный момент – группировать-то можно и по нескольким столбцам:
group = Table.Group(from, {"дата","цикл","выработка"}, {"tmp", each _},GroupKind.Local, (s,c)=>Number.From(c[цикл]<>null))
В этой ситуации при группировке вы сохраните несколько столбцов, причем значения в них будут по первой строке в группе, а проверяется, по сути, только поле цикл.
И вот тут обращаю внимание – если второй аргумент в группировке - "цикл" – то и в формуле мы просто ориентируемся на значение (пишем
c<>null
), а если вы намышкоклацали
{"цикл"}
– то (включаем логику), это список полей, а значит на выходе мы получим… запись. И обращаться уже нужно
c[цикл]<>null
– вот так, и больше не спрашивайте, почему я обычно не пишу фигурные скобки вопреки «стандартному» виду.
Напоследок хочется сказать, что если у вас получилась сложная функция сравнения – не стесняйтесь – дайте ей имя и поместите в отдельный шаг, как мы уже неоднократно делали:
f=(x,y)=> [ a=(z)=> Text.Start(z,3),
b =Number.From(a(x)<>a(y))][b],
group = Table.Group(from, "документ", {"tmp", each _}, GroupKind.Local, f)
На этом на сегодня, думаю, закончим. Главный вывод: Table.Group – это функция ПЯТИ аргументов, и хоть два последних и не обязательны, но в умелых руках бывают крайне эффективны – так что пользуйте их на благо светлого будущего человечества.
Надеюсь, было полезно.
Всех благ!
АнатомияФункций 018: Table.ReverseRows
Table.ReverseRows и причём тут группировка?
Анатомия Функций 018
Всем привет!
В продолжение темы группировки – недавно была задачка. Сложность в том, что сгруппировать надо по ключевому слову в ПОСЛЕДНЕЙ строке группы. Есть отличное решение – допстолбец с индексом, условный столбец, FillUp , потом группировка – в общем все логично и берите на вооружение - у такого подхода есть масса практических приложений.
Но хочется же сделать финт ушами - иначе бы поста не было. Поэтому:
let
from = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText
("5ZXBCoMwDIZfZfS8QZPGVn2VbYc59Q12Gnv3aToxMBA8JBQECY0m+eNHS
q5vd3t5Appt8LMlzOcTO8DOwDaxFaGhZ1tz5Ohahx7x4mF63Hm7cGCHOj6P
QqQThattEVxEkmhJCv7eTzkcNqxdkBcpSQjkr8+l8iiCc3pcO8twCFwbPmc
lkGgBMuwE+RAy/R9UIb8bJKiBDBYgqZiJjGogyQJkVQxIVANZWYCMB7ja0Q
JkKmYi9ZZNsgBZH2AiawuQTTETqbdsGgOQ4IsBqbZs1n/UBAkFXe37Fw==")
,Compression.Deflate))),
typ = Table.TransformColumnTypes(from,{{"Дата выполнения", type date}, {"Дата создания", type date}, {"Процесс", Int64.Type}, {"Задача", type text}}),
tbl = Table.ReverseRows(typ),
group = Table.Group(tbl, {"Процесс","Задача"},
{{"Дата создания", (x)=>List.Min(x[Дата создания]), Date.Type},
{"Дата выполнения", (x)=>List.Max(x[Дата выполнения]), Date.Type}},
GroupKind.Local,
(s,c)=>Number.From(c[Задача]="Контроль")),
to = Table.ReverseRows(group)
in
to
typ
– используем
Table.ReverseRows
– строчки меняются с ног на голову – и нужно группировать по ключевому слову… в ПЕРВОЙ строке группы
group
– группировка, группируем по «Процесс» и «Задача» - сразу по обоим, чтобы процесс не надо было вынимать из сгруппированной таблицы,
GroupKind
естественно
Local
, ну и в функции не забыли указать, что интересует нас только поле «задача»
to
– и снова
ReverseRows
, чтобы вернуть порядок строк в исходный
Вот и всё. Редко пользуюсь реверсами, но по скорости в данной ситуации этот вариант выигрывает кратно.
Курите стандартную библиотеку – там много интересного.
Надеюсь, было полезно.
Всех благ!
