Страницы: 1
RSS
Замена значений по списку
 
Друзья, добрый день.

Не сдаюсь и пытаюсь обучиться PQ для меньшего количества тем на форуме.

Недавно гении форума помогли решить вопрос разбивки по ячейкам: https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=131385&MID=1076958#message1076958

Но есть одна проблема, есть список позиций в исходном виде не подходящий под правила, необходимо произвести замену по справочнику "Что на Что".
Попробовал прикрутить код из этих тем:
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=128343&MID=1056539#message1056539

Но не получилось...
Взял пример из Гугла и попробовал адаптировать под себя, но результат не тот.
Помогите пожалуйста разобраться, что изменить чтобы:
1) Происходила замена
2) Данные появлялись не в новом столбце, а менялись в исходном
3) Отключить верхний регистр (не требуется, таблица замен идет с сохранением регистра).

Заранее благодарен.
Изменено: phelex - 13.08.2020 09:16:17
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Доброе время суток.
Вариант
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    subst = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    join = Table.Join(Source, {"Столбец2"}, subst, {"Что"}, JoinKind.LeftOuter),
    addTemp = Table.AddColumn(join, "temp", each if [НаЧто] <> null then [НаЧто] else [Столбец2]),
    delOldCol2 = Table.RemoveColumns(addTemp, {"Столбец2"}),
    renToNewCol2 = Table.RenameColumns(delOldCol2, {"temp", "Столбец2"}),
    result = Table.SelectColumns(renToNewCol2, Table.ColumnNames(Source))
in
    result
 
phelex, а что же Вы не отписались в теме по первой ссылке?
 
Андрей VG, супер!
Как раз то что искал, но не получалось структурировать.

Возьму смелость назвать это самым подходящим решением задачи во всем гугле, без циклов и с минимум кода.

Для других (и для себя) попробую раскрыть код:
Код
let
// начало этапов (не понимаю почему бывает 2 let в тексте, но это не тот случай)

    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
// Выбираем таблицу в которой нужны замены назначаем имя Source

    subst = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
// Выбираем таблицу2 - Справочник что на что меняем назначаем имя subst

    join = Table.Join(Source, {"Столбец2"}, subst, {"Что"}, JoinKind.LeftOuter),
// Создаем новый столбец из объединения таблиц по совпадению "Столбец 2" из таблицы Source (исходная) и столбца "Что", в таблице subst (справочник), появляется новый столбец.

    addTemp = Table.AddColumn(join, "temp", each if [НаЧто] <> null then [НаЧто] else [Столбец2]),
// добавляем столбец по логике, если в столбце "НаЧто" запроса temp пусто то берем из оригинала "Столбец2", если не пусто берем это значение

    delOldCol2 = Table.RemoveColumns(addTemp, {"Столбец2"}),
// удаляем оригинальный столбец, но не понимаю как тут участвует addTemp 

    renToNewCol2 = Table.RenameColumns(delOldCol2, {"temp", "Столбец2"}),
// Меняем имя столбцу temp на столбце 2
// Вот дальше не понятно какая строчка передвигает столбец из крайнего правого состояния на нужное место.

    result = Table.SelectColumns(renToNewCol2, Table.ColumnNames(Source))
in
// конец этапов
    result
// что выводить конечным результатом, если написать join - то это выведет в конце.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Юрий М, в работе по первой ссылке :)
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
Вот дальше не понятно какая строчка передвигает столбец из крайнего правого состояния на нужное место
SelectColumns не только выбирает столбцы, но и выводит в указанном во втором аргументе порядке. А во втором аргументе список столбцов источника, как вы понимаете, уже в нужном порядке. При желании, можно заменить на Table.ReorderColumns - в данном случае они эквивалентны.
 
для разнообразия (но на больших объемах затупит)
Код
 let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    dict = Table.Buffer(Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content]),
    f=(t)=>try dict{[Что=t]}[НаЧто] otherwise t,
    to = Table.TransformColumns(from,{"Столбец2",f})
in
    to

а вот так должно быть даже не медленнее, чем у Андрея:
Код
 let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    dict = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    rep = List.Buffer(List.Zip({dict[Что],dict[НаЧто]})),
    lst = List.ReplaceMatchingItems(from[Столбец2],rep),
    col = Table.ToColumns(from),
    to = Table.FromColumns(List.ReplaceRange(col,1,1,{lst}),Table.ColumnNames(from))
in
    to

или функцию забацать
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    dict = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    rep = List.Zip({dict[Что],dict[НаЧто]}),
    f=(tbl,col,lst)=>[  a = Table.ColumnNames(tbl),
                        b = List.PositionOf(a,col),
                        c = Table.ToColumns(tbl),
                        d = List.ReplaceMatchingItems(c{b},lst),
                        e = List.ReplaceRange(c,b,1,{d}),
                        f = Table.FromColumns(e,a)][f],
    to = f(from,"Столбец2",rep)
in
    to
Изменено: buchlotnik - 13.08.2020 21:40:57
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, а функция не должна закрываться ]?
И почему через функцию, а не просто последовательной обработкой?
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
а функция не должна закрываться ]?
а внимательно на код посмотреть?
Цитата
phelex написал:
И почему через функцию
я же написал
Цитата
buchlotnik написал:
или
мало ли, замены понадобятся в разных столбцах, а функция одна
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik,понял. Крутое решение. Попробую завтра погонять на скорость.
Спасибо
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
погонять на скорость
на 500k ячеек со словарем в 1k мой вариант - 7,6 сек, Андрей VG  - 4,8 сек; финт ушами не удался, Join быстрее  :)
Соблюдение правил форума не освобождает от модераторского произвола
Страницы: 1
Наверх