Друзья, доброго времени суток! Решал челлендж на одном из сайтов по PQ, немного его видоизменил, чтобы усложнить, и предлагаю поучавстсовать желающим потому что кажется, что сильно перемудрил при его решении. Задача такова. Есть таблица: Необходимо получить диапазон значений от From до To. Если To не заполнен, то в результате только From. To всегда больше чем From. Буквы это как бы продолжение чисел, т.е. должно идти так 6, 7, 8, 9, A, B, C...128, 129, 12A...12Z, 130. Вроде понятно, в приложенном файле желаемый результат, на нем понятней будет. Всем предложившим варианты спасибо.
Что получилось у меня
Код
let
Источник = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="IDGen"]}[Content],{{"From", type text}, {"To", type text}, {"Amount", Int64.Type}}),
sub = Table.FromList({Text.From(0)..Text.From(9)}&{"A".."Z"}),
count = Table.AddColumn(Источник, "count", (r)=>
[a = List.Generate(
()=> [x=0, flag = 0],
each [flag] <> 1,
each if Text.Range(r[From], [x], 1) = Text.Range(r[To], [x], 1)
then [x =[x]+1, flag = 0]
else [x =[x], flag = 1],
each [x]
),
b = List.Count(a)-1,
c = if b = 0 then 0 else Text.Length(r[From]) - a{b}
][c]
),
values = Table.AddColumn(count, "values", (r)=> let cnt = r[count] in
if cnt > 1 then
List.Accumulate(
{1..r[count]-1},
sub,
(s,c)=>
let
a = Table.AddColumn(s, "col"&Text.From(c), each sub),
b = Table.ExpandTableColumn(a,"col"&Text.From(c),{"Column1"},{"col"&Text.From(c)}),
d = Table.CombineColumns(b,Table.ColumnNames(b),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column1"),
e = Table.AddIndexColumn(d,"index",1,1)
in
e
)
else let
a = sub,
b = Table.AddIndexColumn(a,"index",1,1)
in
b
),
range = Table.AddColumn(values, "ID", (r)=> if r[To] = null then {r[From]} else
let
tbl = r[values],
f = Text.End(r[From],r[count]),
l= Text.End(r[To],r[count]),
a = {Table.SelectRows(tbl, each [Column1]=f)[index]{0}..Table.SelectRows(tbl, each [Column1]=l)[index]{0}},
b = Table.SelectRows(tbl, each List.ContainsAny({_[index]}, a))[Column1],
c = List.Transform(b, each Text.Start(r[From], Text.Length(r[From]) - r[count])&_)
in
c
)[[Amount],[ID]],
exp = Table.ExpandListColumn(range, "ID")
in
exp
whateverlover, 1. Я добавил строку 1200JHG100 1200JHL100 400 Ваш запрос выдал ошибку Не разбирался. Мой запрос выдал 233 639 сток. Время
2. Сравнение на начальных данных. Но тут реально маленький объем для сравнения
3. Идея: пишем 2 функции для перевода из десятичной системы в новую и наоборот. Переводим [from], [to] в десятичную систему, создаем список, переводим в новую. Возможно, универсальная функция для перевода в любую другую СС так же будет полезна. В данном случае у нас 36-ричная СС. f_DECtoNEW
Код
( num as number, notation as number ) =>
let
n = List.Buffer ( List.FirstN ( { "0" .. "9", "A" .. "Z" }, notation ) ),
n_rec = Record.FromList ( n, List.Transform ( List.Positions ( n ), Text.From ) ),
r = List.Count ( n ),
generate = List.Generate (
() => [ m = Number.Mod ( num, r ), id = Number.IntegerDivide ( num, r ) ],
each [m] > 0 or [id] > 0,
each [ m = Number.Mod ( [id], r ), id = Number.IntegerDivide ( [id], r ) ],
each Record.Field ( n_rec, Text.From ( [m] ) )
),
result = Text.Combine ( List.Reverse ( generate ) )
in
result
f_NEWtoDEC
Код
( num as text, notation as number ) =>
let
n = List.Buffer ( List.FirstN ( { "0" .. "9", "A" .. "Z" }, notation ) ),
n_rec = Record.FromList ( List.Positions ( n ), n ),
r = List.Count ( n ),
toLst = List.Buffer ( List.Reverse ( Text.ToList ( num ) ) ),
result = List.Sum (
List.Transform ( List.Zip ( { toLst, List.Positions ( toLst ) } ), ( x ) => Record.Field ( n_rec, x{0} ) * Number.Power ( r, x{1} ) )
)
in
result
И довольно простой код:
Код
let
src = Table.TransformColumnTypes (
Excel.CurrentWorkbook(){[ Name = "IDGen" ]}[Content],
{ { "From", type text }, { "To", type text }, { "Amount", Int64.Type } }
),
notation = 36,
addID = Table.AddColumn (
src,
"ID",
each [
s = f_NEWtoDEC ( [From], notation ),
e = if [To] = null then s else f_NEWtoDEC ( [To], notation ),
lst = List.Numbers ( s, e - s + 1 ),
res = List.Transform ( lst, ( x ) => f_DECtoNEW ( x, notation ) )
][res],
type {text}
),
needClmns = Table.SelectColumns ( addID, { "ID", "Amount" } ),
expand = Table.ExpandListColumn ( needClmns, "ID" )
in
expand
whateverlover, предлагаю еще усложнить: часть(-и) id может быть только цифрами, часть и цифрами и буквами, часть только буквами. Банальный пример (первый символ - буквы, второй - цифры): { A0, A1..A9,B0... } Понятно, что изначально каким-то способом задается условие, где буквы, где цифры, а где и то, и другое.
surkenny, может тогда там где изначально цифра, то только цифра, там где изначально буква, то только буква, а последнее значение и символ и буква В примере как-раз норм для этого данные 1200JHG100: с 1 по 4 - только цифры, с 5 по 7 - только буквы, с 8 по 9 - только цифры, 10 - и цифры и буквы. Или можно в начале запроса как-то задавать это правило. Но по сути правило то можно будет задавать только для "и цифры и буквы" т.к. я же не задам первому символу "только буквы", если оно изначально начинается с цифры.
whateverlover, маску задаем в отдельном столбце (n - цифра, l - буква, a - цифра или буква). Пример: nnnnlllnaa. Конечно, перевод из смешанной СС и обратно уже сложнее. И сравните, кстати, разницу от маски обработки строк 1 и 2. f_DECtoMIX
Код
( num as number, mask as text ) =>
let
numbers = List.Buffer ( { "0" .. "9" } ),
letters = List.Buffer ( { "A" .. "Z" } ),
ns_rec = Record.FromList (
List.Transform (
{ numbers, letters, numbers & letters },
( x ) => [ n_rec = Record.FromList ( x, List.Transform ( List.Positions ( x ), Text.From ) ), r = List.Count ( x ) ]
),
{ "n", "l", "a" }
),
nLst = List.Buffer ( List.Reverse ( {"a"} & Text.ToList ( mask ) ) ),
generate = List.Generate (
() => [ k = 0, n = Record.Field ( ns_rec, nLst{k} ), m = Number.Mod ( num, n[r] ), id = Number.IntegerDivide ( num, n[r] ) ],
each [m] > 0 or [id] > 0,
each [ k = [k] + 1, n = Record.Field ( ns_rec, nLst{k} ), m = Number.Mod ( [id], n[r] ), id = Number.IntegerDivide ( [id], n[r] ) ],
each Record.Field ( [n][n_rec], Text.From ( [m] ) )
),
result = Text.Combine ( List.Reverse ( generate ) )
in
result
f_MIXtoDEC
Код
( num as text, mask as text ) =>
let
numbers = List.Buffer ( { "0" .. "9" } ),
letters = List.Buffer ( { "A" .. "Z" } ),
ns_rec = Record.FromList (
List.Transform (
{ numbers, letters, numbers & letters },
( x ) => [ n_rec = Record.FromList ( List.Positions ( x ), x ), r = List.Count ( x ) ]
),
{ "n", "l", "a" }
),
nLst = List.Buffer ( List.Reverse ( Text.ToList ( mask ) ) ),
toLst = List.Buffer ( List.Reverse ( Text.ToList ( num ) ) ),
generate = List.Generate (
() => [ k = 0, n = Record.Field ( ns_rec, nLst{k} ), p = 1, cn = toLst{k} ],
each [k] < List.Count ( toLst ),
each [ k = [k] + 1, n = Record.Field ( ns_rec, nLst{k} ), p = [p] * [n][r], cn = toLst{k} ],
each Record.Field ( [n][n_rec], [cn] ) * [p]
),
result = List.Sum ( generate )
in
result
Запрос почти не изменился:
Код
let
src = Table.TransformColumnTypes (
Excel.CurrentWorkbook(){[ Name = "IDGen2" ]}[Content],
{ { "From", type text }, { "To", type text }, { "Amount", Int64.Type } }
),
addID = Table.AddColumn (
src,
"ID",
each [
s = f_MIXtoDEC ( [From], [Mask] ),
e = if [To] = null then s else f_MIXtoDEC ( [To], [Mask] ),
lst = List.Numbers ( s, e - s + 1 ),
res = List.Transform ( lst, ( x ) => f_DECtoMIX ( x, [Mask] ) )
][res],
type {text}
),
needClmns = Table.SelectColumns ( addID, { "ID", "Amount" } ),
expand = Table.ExpandListColumn ( needClmns, "ID" )
in
expand