Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Power Query Быстрая замена по справочнику
 
#1
22.08.2017 14:27:19
при работе с массивами данных может потребоваться небольшая доработка исходных данных, в моем случае это защита от ввода различных вариантов (опечаток) при работе нескольких пользователей.
суть доработки - замена части названий из исходного столбца на исправленные варианты написания (привел пример в файле)
код для power query у меня вышел вот такой
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"№", Int64.Type}, {"Фирма", type text}, {"цифра", Int64.Type}}),
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип",{"Фирма"},Таблица3,{"исходное название"},"NewColumn",JoinKind.LeftOuter),
    #"Развернутый элемент NewColumn" = Table.ExpandTableColumn(#"Объединенные запросы", "NewColumn", {"требуемое название"}, {"требуемое название"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Развернутый элемент NewColumn", "1", each if [требуемое название] = null then [Фирма] else [требуемое название]),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Добавлен пользовательский объект",{"№", "1", "Фирма", "цифра", "требуемое название"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Переупорядоченные столбцы",{{"1", "Фирма"}, {"Фирма", "удалить"}}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Переименованные столбцы",{"№", "Фирма", "цифра"})
in
    #"Другие удаленные столбцы"
ИМХО, сложновато для такой простой операции вопрос можно ли эту же процедуру делать как быстрее или за меньшее число шагов?
 
 
 
#2
22.08.2017 14:42:31
Доброе время суток.
Цитата
Blood81 написал:
можно ли эту же процедуру делать как быстрее или за меньшее число шагов?
Быстрее - вряд ли, за меньшее число шагов, да, но это мышкой не на клацаешь - нужно будет написать функцию.
 
 
 
#3
22.08.2017 14:50:00
Вот поменьше шагов
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"№", Int64.Type}, {"Фирма", type text}, {"цифра", Int64.Type}}),
    #"Объединенные запросы" = Table.Join(#"Измененный тип",{"Фирма"},Таблица3,{"исходное название"},JoinKind.LeftOuter),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Объединенные запросы", "1", each if [требуемое название] = null then [Фирма] else [требуемое название]),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Добавлен пользовательский объект",{"№", "1", "цифра"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Другие удаленные столбцы",{{"1", "Фирма"}})
in
    #"Переименованные столбцы"
А так-то пользовательскую функцию слепить и будет красиво!
Если использовать удаление других столбцов, то перестановка столбцов местами не нужна, т.к.
Код
Table.SelectColumns(#"Добавлен пользовательский объект",{"№", "Фирма", "цифра"})
выводит столбцы в порядке их перечисления.
Изменено: PooHkrd - 22.08.2017 14:59:42
Вот горшок пустой, он предмет простой...
 
 
 
#4
22.08.2017 15:02:56
Цитата
PooHkrd написал:
Вот поменьше шагов
Ещё чуток :)
Код
let
    source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    dict = Table.Buffer(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content]),
    result = Table.TransformColumns(source, {"Фирма", each try dict{[#"исходное название" = _]}[#"требуемое название"] otherwise _}),
    typed = Table.TransformColumnTypes(result,{{"№", Int64.Type}, {"Фирма", type text}, {"цифра", type number}})
in
    typed
 
 
 
#5
22.08.2017 15:09:20
Ай-ай, сколько всего вкусного.
Забираю!
А сколько таких таблиц можно в буфер на пихать? И, получается, что можно в рамках этой функции брать данные также и из файлов?
Изменено: PooHkrd - 22.08.2017 15:17:53
Вот горшок пустой, он предмет простой...
 
 
 
#6
22.08.2017 15:44:58
Андрей VG,  ОГО!
не зря спросил тут полно хороших приемов )))))

вот этот пункт вы не могли бы пояснить
Код
[#"исходное название" = _]
как то не удается "прочитать" условие изменения "= _" - это как работает?
 
 
 
#7
22.08.2017 15:50:55
Вот тут Максим Зеленский на примере другой функции объяснял как оно работает:
Power Query обращение к полю по номеру столбца
Вот горшок пустой, он предмет простой...
 
 
 
#8
22.08.2017 16:11:44
спасибо PooHkrd, и Максиму Зеленскому.
оператор " _ " в формуле означает текущее значение массива - тоесть формула перебирает построчно заданный ей массив (таблицу или столбец) и для каждой строчки выполняет нужное действие.

итого:
функция Table.TransformColumns перебирает последовательно все записи в столбце "Фирма" из диапазона 'source' заданного на первом шаге и ищет для них совпадения со столбцом "исходное название" из таблицы заданной на втором шаге. в случае совпадения подставляет значение из столбца "требуемое название" во всех иных случаях возвращает текущее значение столбца "Фирма"
 
 
 
#9
22.08.2017 17:58:07
Цитата
Blood81 написал:
полно хороших приемов
Тут ещё больше ;)  Не спешите считать этот приём быстрым. При большом справочнике вполне возможно, что Join будет быстрее. Только используйте именно его, а не NestedJoin - он по исследованиям Максима в 2-3 раза медленнее.
 
 
 
#10
23.08.2017 11:33:04
Андрей VG,
Знаете есть один еще вопрос я настроил оба варианта для сравнения на большую базу( порядка 11К строк )и вот итог:
связка шагов Table.NestedJoin + Table.AddColumn обновляет запрос за  - 50 сек
метод с Table.TransformColumns   обновляет запрос за     - 455 сек

на мой взгляд циклы выглядят схоже и там и там проверяется значение в исходном столбце и ищется для него совпадение в справочнике в случае совпадения остается вариант из справочника - в первом варианте это делается за три логических шага во втором за 1  -  было бы справедливо ожидать обратной ситуации по быстродействию. но видимо что то еще осталось за рамками обсуждения еще какие то особенности движка и предложенных функций
 
 
 
#11
23.08.2017 12:53:36
Blood81, не совсем так, в запросе от Андрея поиск в справочнике происходит столько раз, сколько имеется строк в вашем массиве. А при Джойне справочник сканируется один раз, но поиск происходит не по одному значению, а по списку, после чего выводятся все найденные строки, поэтому такой разброс по времени обработки.
И раз пошла такая пьянка, то за какое время выполняется мой запрос? В нем джойнится сразу вся таблица к исходному массиву, это по идее должно работать еще быстрее.
Изменено: PooHkrd - 23.08.2017 12:55:58
Вот горшок пустой, он предмет простой...
 
 
 
#12
23.08.2017 13:00:14
Цитата
Blood81 написал:
на мой взгляд циклы выглядят схоже
Схоже, да не совсем. При Join выполняется неявное создание индексов для полей соединения, то есть поиск происходит как в словаре VBA - куда быстрее, чем при прямом просмотре в предложенном вам мною коротком варианте (плюс в нём затраты на обработку ошибок). ;)
Если же вы не поленитесь использовать Table.Join функцию с
Код
    source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    dict = Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],
    joined = Table.Join(source, {"Фирма"}, dict, {"исходное название"}, JoinKind.LeftOuter)

То получите ещё прибавку по времени, плюс избавитесь от разворачивания табличного столбца.
 
 
 
#13
23.08.2017 13:19:22
Цитата
PooHkrd написал:
поиск в справочнике происходит столько раз, сколько имеется строк в вашем массиве
ну вот и ответ в различиях пересчета вложенный цикл против доп столбца с расчетами
Цитата
PooHkrd написал:
И раз пошла такая пьянка, то за какое время выполняется мой запрос?
при выполнении кода с Table.Join этот же запрос на этом же массиве пересчитывается за 54 / 45 / 50 сек   -  примерно те 50 сек что и Table.NestedJoin  с разворачиванием столбца

Цитата
Андрей VG написал:
Если же вы не поленитесь использовать Table.Join функцию
:D  уже не поленился Спасибо Андрей
Код
    dict = Table.Buffer(Excel.CurrentWorkbook(){[Name="Имена_Складов"]}[Content]),
    JoinDict = Table.Join(#"Повышенные заголовки",{"Склад"},dict,{"В плане"},JoinKind.LeftOuter),
    //    #"Развернутый элемент NewColumn2" = Table.ExpandTableColumn(JoinDict, "NewColumn", {"Должно быть"}, {"Должно быть"}),
    #"Добавлен пользовательский объект3" = Table.AddColumn(JoinDict, "Пользовательская", each if [Должно быть]=null then [Склад] else [Должно быть]),
    #"Переименованные столбцы2" = Table.RenameColumns(#"Добавлен пользовательский объект3",{{"Склад", "Удалить"}, {"Пользовательская", "Склад"}}),
Изменено: Blood81 - 23.08.2017 13:57:13
 
 
 
#14
24.08.2017 18:24:26
любопытно.
А еще есть предположение, что можно, поигравшись с 6-м аргументом Table.Join, ускорить сведение. Но это надо понимать, что делаете. Я, например, еще не разобрался до конца. Жалко только, что 7-й аргумент не работает с файлами

Цитата
Blood81 написал: оператор " _ " в формуле означает текущее значение массива - тоесть формула перебирает построчно заданный ей массив (таблицу или столбец) и для каждой строчки выполняет нужное действие.
при использовании each
F1 творит чудеса
 
 
 
#15
24.08.2017 19:12:35
Если справочник не очень большой, то можно так еще:

Код
let
    dict = Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],
    BadName = List.Buffer(dict[исходное название]),
    GoodName = List.Buffer(dict[требуемое название]),
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Result1 = Table.TransformColumns(Source, {{"Фирма", each try GoodName{List.PositionOf(BadName, _)} otherwise _}})
in
    Result1

или второй вариант:
Код
let
    dict = Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Lookup = List.Buffer(List.Zip(Table.ToColumns(dict))),
    Result2 = Table.TransformColumns(Source, {{"Фирма", each List.ReplaceMatchingItems({_}, Lookup){0}}})
in
    Result2

или третий (не замена, а доп.столбец):
Код
let
    dict = Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Lookup = List.Buffer(List.Zip(Table.ToColumns(dict))),
    Corrected = List.ReplaceMatchingItems(Source[Фирма], Lookup),
    Result = Table.FromColumns(Table.ToColumns(Source) & {Corrected}, Table.ColumnNames(Source) & {"требуемое название"})
in
    Result

любопытно сравнить производительность с Join на вашем датасете, Blood81
F1 творит чудеса
 
 
 
#16
27.04.2020 13:59:55
Всем привет!
Не стал создавать новую тему, по сути вопрос у меня такой же, нужно заменить значения из списка, но проблема в том, что нужно заменить не ячейку целиком, а только некоторые символы, суть в том, что есть некоторые "рукожопы", которым лень изменить раскладку на клавиатуре и они как попало вводят данные. Решение нашёл на vba, но нужно в PQ
 
 
 
#17
27.04.2020 15:06:44
Применил запрос Максима Зеленского
Код
    let Source = Excel.CurrentWorkbook(){[Name="Исходник"]}[Content],
    a=List.Buffer(List.Zip(Table.ToColumns(Excel.CurrentWorkbook(){[Name="RUS"]}[Content])))
    in Table.TransformColumns(Source, {{"№линии", each let a=a, b=Text.ToList(_) in 
    Text.Combine(List.Transform(b,each List.ReplaceMatchingItems({_},a){0}))}})
НА МЯСО В ПРОФИЛЕ
 
 
 
#18
27.04.2020 15:46:25
Михаил Л, спасибо, можете уточнить по коду
Код
let Source = Excel.CurrentWorkbook(){[Name="Исходник"]}[Content], \\ обратились к исходнику
    a=List.Buffer(List.Zip(Table.ToColumns(Excel.CurrentWorkbook(){[Name="RUS"]}[Content]))) \\ сформировали список из списков АA, СC и тд.
    in Table.TransformColumns(Source, {{"№линии", each let a=a, b=Text.ToList(_) \\ обратились к исходнику к столбцу №линии, вот тут я не понял, как мы заменили значения  
in 
    Text.Combine(List.Transform(b,each List.ReplaceMatchingItems({_},a){0}))}})\\ тут мы склеиваем b и заменённое новое a, но тут ступар вот с этим {_}, читал выше что это перебор значений
 
 
 
#19
27.04.2020 16:30:17
Код
let пример_таблица_для_замены={{"Х","ddddd"},{"0",""}},пример=Text.ToList("LХ-0030"), пример_Замена=List.ReplaceMatchingItems(пример,пример_таблица_для_замены),пример_сбор=Text.Combine(пример_Замена), Source = Excel.CurrentWorkbook(){[Name="Исходник"]}[Content], // обратились к исходнику
    таблица_для_замены = List.Buffer(List.Zip(Table.ToColumns(Excel.CurrentWorkbook(){[Name="RUS"]}[Content]))), //сформировали список из списков АA, СC и тд.
    b= Table.TransformColumns(Source, {{"№линии", each  // обратились к исходнику к столбцу №линии, который будем трансформировать(изменять, применяя функции)
      let 
         a = таблица_для_замены, 
         b=Text.ToList(_), // надо заменить отдельные буквы в тексте. Для этого  разделим текст в текущей строке по буквам. Получим список знаков(букв)
         c=List.Transform(b,each List.ReplaceMatchingItems({_},a){0}), // из списка берем каждую букву и ищем ее в таблице для замены.Если находим то меняем
         d=Text.Combine(c) // собираем все знаки(буквы) опять в текст, то есть обратное действие Text.ToList
      in d  }})  
in b
НА МЯСО В ПРОФИЛЕ
 
 
 
#20
27.04.2020 16:44:15
Вариант с джойном для коллекции, но вариант от Максима почти в три раза быстрее:
Код
let
    ReplacerRUSLikeLAT = (text,tab,x)=>
        let t = List.Buffer( Text.ToList( text ) ), 
            d = Table.Distinct( tab & Table.FromColumns({t,t}, {"RUS","ABS"}), {"ABS"} ),
            j = Table.Join( Table.FromColumns( {t}, {"RUS"} ), "RUS", d, "RUS") 
        in  Text.Combine(j[ABS]),
    Change = Table.Buffer( Excel.CurrentWorkbook(){[Name="RUS"]}[Content] ),
    Table = Excel.CurrentWorkbook(){[Name="Исходник"]}[Content],
    Replaced = Table.ReplaceValue( Table, Change, 0, ReplacerRUSLikeLAT, {"№линии"} )
in
    Replaced

Поигрался ос своим вариантом по разному получая результат функции - бестолку, AddColumn/TransformColumns/ReplaceValue, результат 16 секунд на 100к строк, вариант Максима со списками на том же массиве 6 сек. Стендик прилагаю, кто хочет еще - расширяйте табличку играйтесь на здоровье.
Вот горшок пустой, он предмет простой...
 
 
 
#21
27.04.2020 18:22:11
Михаил Л, PooHkrd, спасибо большущее!
 
 
 
#22
27.04.2020 22:45:05
adamm, ой, совсем забыл мой вариант может быть косячный т.к. джойн может сбивать исходную сортировку, это туда нужно еще индекс и сортировку прикручивать, чтобы корректно работало, т.е. будет еще медленнее. Мой скрипт без внесения правок использовать в работе категорически нельзя.
Вот горшок пустой, он предмет простой...
 
 
 
#23
11.05.2020 14:00:53
Коллеги, добрый день!

Почитал этот раздел и вопросы.
Хотел бы уточнить методологический вопрос и очень надеюсь, что в практических рекомендациях можете помочь.  

Подскажите, пожалуйста, можно ли на этапе обработки данных в pq, распределить  исходную таблицу по кодам из справочника, чтобы в модель выгружалась таблица с кодами?
То есть идея какая:
Импорт  файла в pq - все что можно заменить на  коды/индексы с помощью справочников,  где есть индексы, например, текстовые значения с нулями  впереди меняю  на код. Затем загружаю в модель данных. Дальше связываю в pivot со  справочниками и вывожу сводные в эксель уже в той  разбивке, которая  нужна.

Это вообще адекватная логика, правильно методологически  или лучше оставить только "косметические" изменения в query?
Я посмотрел ряд тем на форуме, но не совсем понял какая именно функция подходит и вообще эффективно ли так делать.

Новичок в pq pp

С уважением,
Кирилл.
 
 
 
#24
11.05.2020 18:38:50
Kerel, не понятно почему на выводе в столбце NAPR 1 и 2, а не 1 и 1?
И пример бы побольше
НА МЯСО В ПРОФИЛЕ
 
 
 
#25
11.05.2020 20:44:18
Цитата
Михаил Л написал:
Kerel , не понятно почему на выводе в столбце NAPR 1 и 2, а не 1 и 1?И пример бы побольше
Михаил, действительно, мое упущение, спасибо.
Подправил и расширил пример. Достаточно ли? Сколько вообще требуется обычно строк для примера на форуме?

Сейчас я сделал кликами:
То есть сделал эти справочники ЭК - ИМ, все коды тнвэд (32 тыс.).
В них проиндексировал строчки.
Затем в основной таблице объединил запросы по тектовому полю и поставил в таблицу значения индексов.
Исходный столбец удалил.

Эффективен ли этот метод, если я хочу сделать шаблон таблицы для приведения исходных файла?
Далее планирую загрузить данные в модель данных и делать power pivot.

у меня по 500 тыс. строк, файлы по 25 мб, 12 файлов в год
Я не знаю как это прописывается кодами, но взял после кликанья.
Код
#"Объединенные запросы" = Table.NestedJoin(#"Сортированные строки2", {"Направление"}, Направление, {"Направление_назв"}, "Направление.1", JoinKind.LeftOuter),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Объединенные запросы",{"Направление", "Направление.1", "Дата", "Страна_код", "ТНВЭД_код", "Ед_измер", "Стоимость", "Масса_объем", "Количество", "Регион_код", "Округ_код"}),
    #"Развернутый элемент Направление.1" = Table.ExpandTableColumn(#"Переупорядоченные столбцы", "Направление.1", {"Направление_индекс"}, {"Направление_индекс"}),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Развернутый элемент Направление.1",{"Направление"}),

Буду признателен
 
 
 
#26
11.05.2020 21:11:07
Цитата
Kerel написал:
справочники ЭК - ИМ, все коды тнвэд (32 тыс.)
Цитата
Kerel написал:
Эффективен ли этот метод, если я хочу сделать шаблон таблицы для приведения исходных файла?
Далее планирую загрузить данные в модель данных и делать power pivot.
у меня по 500 тыс. строк, файлы по 25 мб, 12 файлов в год
Раз файлы по 500 тысяч строк и справочники по 32 тысячи строк, то скорости не будет
Насчет эффективности ничего не могу сказать
Попробуйте применить этот запрос. Отпишитесь сколько по времени
Код
// Таблица2
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    dict = Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content][[TNVED],[TNVED_Индекс]],
    dict2 = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][[NAPR],[NAPR_CODE]],
    Lookup = List.Buffer(List.Zip(Table.ToColumns(dict))),
    Lookup2 = List.Buffer(List.Zip(Table.ToColumns(dict2))),
    Result = Table.TransformColumns(Source, {{"TNVED", each List.ReplaceMatchingItems({_}, Lookup){0}}}),
    Result2 = Table.TransformColumns(Result, {{"NAPR", each List.ReplaceMatchingItems({_}, Lookup2){0}}})
in Result2
НА МЯСО В ПРОФИЛЕ
 
 
 
#27
12.05.2020 23:15:38
Михаил, большое спасибо за решение!
Интересное, изучаю и думаю как его внедрить в остальной массив по замене других текстовых значений.

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

Сводная таблица из
 
Названия строкИМЭКОбщий итог
6110309148387,2114422,5762809,78
8309909048387,2114422,5762809,78
8414598048387,2114422,5762809,78
8460298048387,2114422,5762809,78
8471800048387,2114422,5762809,78
 
 
 
#28
13.05.2020 06:29:58
Цитата
Kerel написал:
по тнвэд нет
В примере покажите ваш запрос
НА МЯСО В ПРОФИЛЕ
 
 
 
#29
16.05.2020 16:56:30
Цитата
Михаил Л написал:
Kerel  написал:по тнвэд нетВ примере покажите ваш запрос
Я скопировал ваш код вставил в pq

Затем добавил справочники - отдельными таблицами из файлов. Затем загрузил и добавил все в модель. Провел связи по индексам 1,2,3...
Потом вывел свод и строки - коды, столбы - направление. Но уже не индексами, а названиями.
И вот такая картинка выходит(  Потом еще раз проверил типы данных столбцов - сделал их в ручную целыми числами (в файле общем и справочниках). Видимо из-за лок настроек pq их меняет на смешанные.
Запрос? его можно общий где-то найти по всем действиям?

Хотел сюда прикрепить таблицу как получилось, но 100 кб, не более.

ps Извиняюсь, что пропал на долго. Работал.
 
 
 
#30
25.04.2022 12:33:47
Чтобы не создавать новую тему: Есть проблема - значения из справочника не хотят заменять все совпадения из исходного файла. Часть меняет - часть нет. Например, ищешь по поиску в исходном файле - 8 значений, после обработки по справочнику заменяет 5, 3 не меняет. Причём, в сам справочник копируется значение через буфер (без лишних пробелов и т.д.), а не ручками через клавиатуру заношу. В чём может быть причина?
Изменено: Zagadka - 25.04.2022 14:41:48
 
 
 
Страницы: 1 2 След.
Наверх