Здравствуйте, форумчане. Подскажите пожалуйста, возможно ли с помощью power query произвести замену значений в цикле? Файл на 4.5 миллиона строк и там два столбца. Нужно произвести замену во втором столбце. Пример строки 2 столбца: "товар1: машина, качество1: хорошее, цена1: 123, товар2:мотоцикл, качество2: отличное, цена2: 233, ..." И т.д. в каждой строке таких описание может быть более 10. Мне нужно заменить машина на auto , а мотоцикл на moto. Будь не такое большое кол-во строк я бы через vba циклом написал, а и поменял все значения. Сразу говорю, что названия там могут быть любыми, это строка как пример. Я рассчитывал создать таблицу с уникальными значениями где каждой записи присвоено значение на замену и пропустить их в цикле. Если это будет возможно, то могу приложить файл, но чуть позже.
Здравствуйте, форумчане. Подскажите пожалуйста, возможно ли с помощью power query произвести замену значений в цикле? Файл на 4.5 миллиона строк и там два столбца. Нужно произвести замену во втором столбце. Пример строки 2 столбца: "товар1: машина, качество1: хорошее, цена1: 123, товар2:мотоцикл, качество2: отличное, цена2: 233, ..." И т.д. в каждой строке таких описание может быть более 10. Мне нужно заменить машина на auto , а мотоцикл на moto. Будь не такое большое кол-во строк я бы через vba циклом написал, а и поменял все значения. Сразу говорю, что названия там могут быть любыми, это строка как пример. Я рассчитывал создать таблицу с уникальными значениями где каждой записи присвоено значение на замену и пропустить их в цикле. Если это будет возможно, то могу приложить файл, но чуть позже.monstr_ork
krosav4ig, это при условии, что значение в отдельном столбце, правильно я понял? У меня значения внутри строки или мне нужно разбить фразу на несколько столбцов
krosav4ig, это при условии, что значение в отдельном столбце, правильно я понял? У меня значения внутри строки или мне нужно разбить фразу на несколько столбцовmonstr_ork
Сообщение отредактировал monstr_ork - Воскресенье, 02.06.2019, 21:02
anvg, это у вас в код вставлены нужные данные. Так то их будет около тысячи. которые нужно подменить. Приложил файл, может так лучше будет понятно. Сразу скажу. что таблицы с заменами будет отдельным листом загружаемым в PQ
anvg, это у вас в код вставлены нужные данные. Так то их будет около тысячи. которые нужно подменить. Приложил файл, может так лучше будет понятно. Сразу скажу. что таблицы с заменами будет отдельным листом загружаемым в PQmonstr_ork
И что меняется? Только нужно подготовить данные. Так как вы посчитали, что делать таблицы - это задача помогающего, то сделал их такими как в коде [vba]
Код
let Products = Table.RenameColumns(Excel.CurrentWorkbook(){[Name="Products"]}[Content], {{"Товар", "from"}, {"Товар_замена", "to"}}), Qualities = Table.RenameColumns(Excel.CurrentWorkbook(){[Name="Qualities"]}[Content], {{"Качество", "from"}, {"Качество_замена", "to"}}), pairs = List.Buffer(Table.ToRecords(Products & Qualities)), Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content], result = Table.TransformColumns(Source, {"Столбец2", each List.Accumulate( pairs, _, (acc, next) => Text.Replace(acc, next[from], next[to]) ) }) in result
И что меняется? Только нужно подготовить данные. Так как вы посчитали, что делать таблицы - это задача помогающего, то сделал их такими как в коде [vba]
Код
let Products = Table.RenameColumns(Excel.CurrentWorkbook(){[Name="Products"]}[Content], {{"Товар", "from"}, {"Товар_замена", "to"}}), Qualities = Table.RenameColumns(Excel.CurrentWorkbook(){[Name="Qualities"]}[Content], {{"Качество", "from"}, {"Качество_замена", "to"}}), pairs = List.Buffer(Table.ToRecords(Products & Qualities)), Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content], result = Table.TransformColumns(Source, {"Столбец2", each List.Accumulate( pairs, _, (acc, next) => Text.Replace(acc, next[from], next[to]) ) }) in result
List.Combine объединяет несколько списков в один (можно пользоваться для этих целей оператором объединения List.Combine({list1, list2}) == list1 & list2). List.Buffer принуждает Power Query создать конечный список, заданный его аргументом (помним, что одна из проблем Power Query - это ленивые, отложенные вычисления), что ускоряет выполнение при больших размерах списка).
List.Combine объединяет несколько списков в один (можно пользоваться для этих целей оператором объединения List.Combine({list1, list2}) == list1 & list2). List.Buffer принуждает Power Query создать конечный список, заданный его аргументом (помним, что одна из проблем Power Query - это ленивые, отложенные вычисления), что ускоряет выполнение при больших размерах списка).anvg
тут даем переменной что-то (объединям наши таблицы?) Дальше мы что-то делаем с нашей основной таблицей. Products, Qualities и Source - это переменные или это Вы так обозвали таблицы? Если не трудно, могли бы выложить файл с внесенным кодом? (и небольшими разъяснениями)
anvg, спасибо за помощь, но я себя переоценил (не понял как это все запустить).
тут даем переменной что-то (объединям наши таблицы?) Дальше мы что-то делаем с нашей основной таблицей. Products, Qualities и Source - это переменные или это Вы так обозвали таблицы? Если не трудно, могли бы выложить файл с внесенным кодом? (и небольшими разъяснениями)monstr_ork
Да объединяем и преобразуем в список записей для последующего использования в List.Accumulate. Решение лобовое. Тупо на каждой строке таблицы Source применяем по списку найти и заменить. Эффективность 1000 пар найти и заменить * на 4,5 миллиона строк = 4,5 миллиарда поиска и замен. Может имеет смысл разбить строки в Source на составные части с локальной индексацией, а потом делать замену через Table.Join как и советовали. Но что будет лучше - можно ответить, только зная структуру данных. По простому сколько из 1000 пар найти/заменить будут использованы для каждой строки Source.
Да объединяем и преобразуем в список записей для последующего использования в List.Accumulate. Решение лобовое. Тупо на каждой строке таблицы Source применяем по списку найти и заменить. Эффективность 1000 пар найти и заменить * на 4,5 миллиона строк = 4,5 миллиарда поиска и замен. Может имеет смысл разбить строки в Source на составные части с локальной индексацией, а потом делать замену через Table.Join как и советовали. Но что будет лучше - можно ответить, только зная структуру данных. По простому сколько из 1000 пар найти/заменить будут использованы для каждой строки Source.anvg
anvg, по примеру полностью с Вами согласен. На начальном этапе меня и 4,5 миллиарда устроят, единственное что хотел уточнить, а можно вместо таблицы файл подставить? Если да то как?
По поводу дробления строки на отдельные столбцы, я делал, но не понял как потом эти столбцы проверить, т.к. их в начале по знаку "," разделить, а потом еще прогонять циклом на замену. Если Вам сложно, приложить файл где будет деление по столбцам?
anvg, по примеру полностью с Вами согласен. На начальном этапе меня и 4,5 миллиарда устроят, единственное что хотел уточнить, а можно вместо таблицы файл подставить? Если да то как?
По поводу дробления строки на отдельные столбцы, я делал, но не понял как потом эти столбцы проверить, т.к. их в начале по знаку "," разделить, а потом еще прогонять циклом на замену. Если Вам сложно, приложить файл где будет деление по столбцам?monstr_ork
Не на столбцы, на пронумерованные строки. Точнее, сначала Table.AddIndexColumn для строк Source, и разбиение на слова содержимого второго столбца с преобразованием в таблицу и нумерацией через ту же функцию (нужно, чтобы потом собрать в правильном порядке исправленную фразу). Вы всё же программировали на VBA, ну так осваивайте программирование на Power Query. Мышкоклацание - это не очень хороший подход.
Не на столбцы, на пронумерованные строки. Точнее, сначала Table.AddIndexColumn для строк Source, и разбиение на слова содержимого второго столбца с преобразованием в таблицу и нумерацией через ту же функцию (нужно, чтобы потом собрать в правильном порядке исправленную фразу). Вы всё же программировали на VBA, ну так осваивайте программирование на Power Query. Мышкоклацание - это не очень хороший подход.anvg
Сообщение отредактировал anvg - Вторник, 04.06.2019, 10:43
Не так много инфо и учителей пока (тем более на русском). А от VBA язык значительно отличается.. Составил запрос по инструкции, помогите оптимизировать или подсказать что лишнее или неправильное
[vba]
Код
let Источник = Excel.CurrentWorkbook(), #"Строки с примененным фильтром" = Table.SelectRows(Источник, each Text.Contains([Name], "Табл")), t1=#"Строки с примененным фильтром"{1}[Content]"Строки с примененным фильтром"{2}[Content], t2 = Table.AddIndexColumn(Источник{0}[Content],"Index",1,1), #"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(t2, {{"Столбец2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Столбец2"), transform_t2 = Table.TransformColumns(#"Разделить столбец по разделителю", {"Столбец2", each Table.ExpandTableColumn(Table.NestedJoin( Table.Transpose(Table.FromList(List.Transform(Text.Split(_, ":"), each Text.Trim(_)), Splitter.SplitByNothing(), null, null, ExtraValues.Error)),{"Column2"},t1,{"from"},"1",JoinKind.LeftOuter), "1", {"to"}, {"to"})}), #"Развернутый элемент Столбец2" = Table.ExpandTableColumn(transform_t2, "Столбец2", {"Column1", "Column2", "to"}, {"Column1", "Column2", "to"}), #"Условный столбец добавлен" = Table.AddColumn(#"Развернутый элемент Столбец2", "Пользовательская", each if [to] = null then [Column1] & ": " &[Column2] else [Column1]& ": " &[to]), #"Удаленные столбцы" = Table.RemoveColumns(#"Условный столбец добавлен",{"Column1", "Column2", "to"}), #"Сгруппированные строки" = Table.Group(#"Удаленные столбцы", {"Столбец1", "Index"}, {{"Групп", each Text.Combine(_[Пользовательская], ", "), type text}}), #"Удаленные столбцы1" = Table.RemoveColumns(#"Сгруппированные строки",{"Index"}) in #"Удаленные столбцы1"
Не так много инфо и учителей пока (тем более на русском). А от VBA язык значительно отличается.. Составил запрос по инструкции, помогите оптимизировать или подсказать что лишнее или неправильное
[vba]
Код
let Источник = Excel.CurrentWorkbook(), #"Строки с примененным фильтром" = Table.SelectRows(Источник, each Text.Contains([Name], "Табл")), t1=#"Строки с примененным фильтром"{1}[Content]"Строки с примененным фильтром"{2}[Content], t2 = Table.AddIndexColumn(Источник{0}[Content],"Index",1,1), #"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(t2, {{"Столбец2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Столбец2"), transform_t2 = Table.TransformColumns(#"Разделить столбец по разделителю", {"Столбец2", each Table.ExpandTableColumn(Table.NestedJoin( Table.Transpose(Table.FromList(List.Transform(Text.Split(_, ":"), each Text.Trim(_)), Splitter.SplitByNothing(), null, null, ExtraValues.Error)),{"Column2"},t1,{"from"},"1",JoinKind.LeftOuter), "1", {"to"}, {"to"})}), #"Развернутый элемент Столбец2" = Table.ExpandTableColumn(transform_t2, "Столбец2", {"Column1", "Column2", "to"}, {"Column1", "Column2", "to"}), #"Условный столбец добавлен" = Table.AddColumn(#"Развернутый элемент Столбец2", "Пользовательская", each if [to] = null then [Column1] & ": " &[Column2] else [Column1]& ": " &[to]), #"Удаленные столбцы" = Table.RemoveColumns(#"Условный столбец добавлен",{"Column1", "Column2", "to"}), #"Сгруппированные строки" = Table.Group(#"Удаленные столбцы", {"Столбец1", "Index"}, {{"Групп", each Text.Combine(_[Пользовательская], ", "), type text}}), #"Удаленные столбцы1" = Table.RemoveColumns(#"Сгруппированные строки",{"Index"}) in #"Удаленные столбцы1"
Не, что-то не так сделал синтетический тест. Исходную таблицу размножил на 100тыс строк. Таблицы с заменами не трогал. Результаты по скорости обработки в обратном порядке: 4. мой запрос из сообщения 13 - 24 мин 48 сек 3. запрос anvg из сообщения 14 -1 мин 35 сек (чувствуется разница)) 2. запрос krosav4ig из сообщения 15 - 46,4 сек! 1. запрос anvg из сообщения 6 - 2,3 сек!!!
Не, что-то не так сделал синтетический тест. Исходную таблицу размножил на 100тыс строк. Таблицы с заменами не трогал. Результаты по скорости обработки в обратном порядке: 4. мой запрос из сообщения 13 - 24 мин 48 сек 3. запрос anvg из сообщения 14 -1 мин 35 сек (чувствуется разница)) 2. запрос krosav4ig из сообщения 15 - 46,4 сек! 1. запрос anvg из сообщения 6 - 2,3 сек!!!sboy
anvg, пытаюсь изучать, информации в свободных истониках на русском слишком мало, а те что есть только про то как ручками все делать =( Только ни кто не ответил, либо я не увидел, как вместо таблиц использовать файлы, которые подружаются отдельно? Я сделал так : [vba]
Код
Source = Excel.Workbook(File.Contents("путь к файлу"), null, true),
[/vba] Но потом начались ошибки со столбцами, нужно еще что-то дописать? И буквально еще один вопрос, как выгрзуить эти данные обратно в CSV?
[offtop]Было бы кроуто тему по Power query "открыть", хоть и не много обращений, но все же в обдом разделе[/offtop]
anvg, пытаюсь изучать, информации в свободных истониках на русском слишком мало, а те что есть только про то как ручками все делать =( Только ни кто не ответил, либо я не увидел, как вместо таблиц использовать файлы, которые подружаются отдельно? Я сделал так : [vba]
Код
Source = Excel.Workbook(File.Contents("путь к файлу"), null, true),
[/vba] Но потом начались ошибки со столбцами, нужно еще что-то дописать? И буквально еще один вопрос, как выгрзуить эти данные обратно в CSV?
[offtop]Было бы кроуто тему по Power query "открыть", хоть и не много обращений, но все же в обдом разделе[/offtop]monstr_ork
Сообщение отредактировал monstr_ork - Четверг, 06.06.2019, 23:17