Страницы: 1
RSS
Power Query Создать диапазон значений из букв и чисел [Challenge]
 
Друзья, доброго времени суток!
Решал челлендж на одном из сайтов по PQ, немного его видоизменил, чтобы усложнить, и предлагаю поучавстсовать желающим  :) потому что кажется, что сильно перемудрил при его решении.
Задача такова.
  Есть таблица:

  Необходимо получить диапазон значений от From до To.
  Если To не заполнен, то в результате только From.
  To всегда больше чем From.
  Буквы это как бы продолжение чисел, т.е. должно идти так 6, 7, 8, 9, A, B, C...128, 129, 12A...12Z, 130. Вроде понятно, в приложенном файле желаемый результат, на нем понятней будет.
Всем предложившим варианты спасибо.  :)

Что получилось у меня
 
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
Изменено: surkenny - 28.09.2022 12:40:14
 
surkenny, спасибо!  8)
Да, потом увидел, что у меня ошибка вылезает где-то, что-то про нельзя number и text соединять.
Разберу.  8)
 
whateverlover, предлагаю еще усложнить:
часть(-и) id может быть только цифрами, часть и цифрами и буквами, часть только буквами.
Банальный пример (первый символ - буквы, второй - цифры): { A0, A1..A9,B0... }
Понятно, что изначально каким-то способом задается условие, где буквы, где цифры, а где и то, и другое.

Ну и предлагаю подключить Михаила, дав ссылку на https://t.me/PQ_ru :)
 
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
Изменено: surkenny - 28.09.2022 19:15:44
 
А других вариантов нет от коллег? :)
 
Я пока сдался, мне нужен целый вечер выкроить, чтобы Ваш вариант разобрать.  :)
А свой сделать несколько вечеров(
Страницы: 1
Наверх