Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Kintoho
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
DoEvents в макросе на обновление запроса PowerQuery, Использование DoEvents не прерывает работу макроса.
 
#
24.04.2019 20:43:55
artyrH, большое спасибо! Отличное видео, ответ на вопрос получен.
 
Перейти    
DoEvents в макросе на обновление запроса PowerQuery, Использование DoEvents не прерывает работу макроса.
 
#
24.04.2019 17:43:46
Добрый день!
Пытаюсь использовать DoEvents, но почему-то он не прерывает работу макроса. Где кроется ошибка?
Excel 2010, PowerQuery - надстройка
Есть 3 файла: 1-й с макросом "Макрос.xlsm", который запускается вручную, 2й - "Обработка.xlsx" содержит таблицу которая является результатом запроса PowerQuery, 3-й "Данные.xls" - это файл с данными которые обрабатывает PQ.
Задача такая - пользователь через макрос запускает процесс обновления запроса Power Query, после окончания которого файл "Обработка" сохраняется и закрывается.
По факту происходит следующее - макрос запускает обновление, но не дожидаясь его окончания даёт команду на закрытие файла. DoEvents не помогает.
То ли код написан неверно, то ли DoEvents не работает с PowerQuery ?!
Примечание, макрос должен содеражться в 1-м файле. 2-й и 3й файлы не должны содеражть макросов. Нет возможности установить более новые версии Excel.
Мой код:
Код
Sub ОбновлениеЗапроса()
   Workbooks.Open ("C:\Данные\Обработка.xlsx")
   ActiveWorkbook.RefreshAll
   DoEvents
   ActiveWorkbook.Close SaveChanges:=True
End Sub
Изменено: Kintoho - 24.04.2019 17:46:37
 
Перейти    
PQ. Замена значений по нескольким условиям
 
#
09.08.2018 16:37:43
Максим Зеленский, Андрей VG, спасибо за ответы!
Цитата
Максим Зеленский написал:
А что с найденным надо делать?
Удалить или поменять на символ как вы и сделали с точкой.
Цитата
Максим Зеленский написал:
= Table.ReplaceValue(#"Убрать!@#", "@!#", ".", (value, old, new)=>Text.Combine(Splitter.SplitTextByAnyDelimiter(Text.ToList(old))(value),new), {"Арт"})
Такой код работает. А можете пояснить суть записи (value, old, new) ? Я правильно понял, что это вызов функции value определяющее выражение которой указано после => и эта функция возвращает единственное значение new ?
Но не понятно как работает 4й параметр в функции ReplaceValue, ведь Значение это #"Убрать!@#" , старое значение это @!#", а новое это "." и тут по идее функция должна быть закрыта.
Replacer.ReplaceValue(value as any,  old as any,  new as any) as any

И ещё я заметил что функция убирает знак ", причём я не прошу PQ его удалять (указываю символы <)(> ), ну или получается, что прошу (ведь без "" указать значения нельзя), но как тогда быть если символ " нужно сохранить в тексте?
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Остатки"]}[Content],
    #"Замена <)(>" = Table.ReplaceValue(Источник,"<)(>", "!",(value, old, new)=>Text.Combine(Splitter.SplitTextByAnyDelimiter(Text.ToList(old))(value),new),{"Наименование"})
in
    #"Замена <)(>"
Цитата
Андрей VG написал:
Максим, а не проще ли было
Код ? 1Text.Remove(value, Text.ToList(oldValue))
У ТС же просто удаление символов?
Как написать данную конструкцию я пока что не понял, надо дальше вникать в синтаксис языка....
Да, суть задачи - удалить ненужные символы.
 
Перейти    
PQ. Замена значений по нескольким условиям
 
#
08.08.2018 18:50:25
Добрый день!

Сейчас:
Код
 #"Убрать """ = Table.ReplaceValue(#"Убрать !","""","",Replacer.ReplaceText,{"Арт"}),
    #"Убрать '" = Table.ReplaceValue(#"Убрать ""","'","",Replacer.ReplaceText,{"Арт"}),
    #"Убрать @" = Table.ReplaceValue(#"Убрать '","@","",Replacer.ReplaceText,{"Арт"}),
    #"Убрать #" = Table.ReplaceValue(#"Убрать @","#","",Replacer.ReplaceText,{"Арт"}),
    #"Убрать №" = Table.ReplaceValue(#"Убрать #","№","",Replacer.ReplaceText,{"Арт"}),
    #"Убрать ;" = Table.ReplaceValue(#"Убрать №",";","",Replacer.ReplaceText,{"Арт"}),
А хочется в одну строку... Можно ли в функции  Table.ReplaceValue указать сразу несколько значений для замены?
И можно ли в Table.ReplaceValue при поиске указывать "любой" символ? Например, "Игрушка * медведь", где * может быть любое кол-во символов, например, белый или черный или бурый.
Изменено: Kintoho - 08.08.2018 19:14:49
 
Перейти    
Преобразование шапки таблицы в Power Query, Прошу поделиться приёмом преобразования сложных шапок через Power Query
 
#
09.07.2018 16:55:16
Dark1589, ну мне не готовое решение нужно было, а алгоритм/метод/подсказка. Я их получил в полной мере и уже применяю к другим отчётам, за что всем спасибо!

Цитата
Dark1589 написал:
ps. Хотя я бы всё сделал, чтоб убрать все эти объединения и уровни до создания запроса.

Так в том и прелесть PQ, что я единожды создаю набор правил обработки для каждого отчёта (а их более 10 вариантов и шапка везде своя), и потом просто обновляю запрос. А каждый раз форматировать отчёт это долго и зачем? Это может делать компьютер.
В идеале ещё бы прикрутить, автоматическую подкачку файла со свежей датой из определенной папки, и тогда вообще красота будет.... Но этим я займусь немного позже, как я уже понял PQ и это под силу  8)


Андрей VG, разницу я понимаю. Выложить пустую таблицу можно конечно.... но смысл? Фактически, я обрезал только итоги по столбцам и строкам, а также поменял имена.
 
Перейти    
Преобразование шапки таблицы в Power Query, Прошу поделиться приёмом преобразования сложных шапок через Power Query
 
#
09.07.2018 15:51:50
Цитата
Андрей VG написал:
Жаль, что ТС поленился представить полную структуру выгрузки. Так что для последующего допиливания напильником.
Я не лился, просто не  имею права выкладывать рабочую информацию. А маскировать таблицу на 1000+ строк не хочется. Да и смысла нет, все возможные варианты присутствуют в кратком примере. Рассмотрю ваш код позже, надеюсь и там почерпну полезную информацию.
Цитата
PooHkrd написал:
Так потому и написал, что осторожно. Менять нужно только в столбце с числовым типом, и только те что равны 0, а не содержат ноль.
Меня более чем устраивает вариант с заменой null на 0. Обратная замена, в моём случае смысла не имеет.
PooHkrd, большое спасибо за помощь! Уже во всю использую в работе  :)

PQ конечно мощный инструмент, жаль литературы русскоязычной не найти... Хочется подробно изучить, особенно "Расширенный редактор".
Цитата
Dark1589 написал:
Просто для справки:  https://www.youtube.com/watch?v=pV8Rw5gSjFI
Наглядное видео, хотя на мой взгляд, много лишних действий. Спасибо!
 
Перейти    
Преобразование шапки таблицы в Power Query, Прошу поделиться приёмом преобразования сложных шапок через Power Query
 
#
06.07.2018 18:23:43
Цитата
vector1 написал:
Исключительно правильный совет.
Наверное вы правы, но к сожалению, такой возможности у меня нет.
 
Перейти    
Преобразование шапки таблицы в Power Query, Прошу поделиться приёмом преобразования сложных шапок через Power Query
 
#
06.07.2018 18:20:53
Цитата
PooHkrd написал:
И зачем такие извращения? Не проще вывод результата в самом отчете 1С изменить? Там обычно это все не сложно делается.
2 причины:
  • Анализ проводить нужно, а с программистом 1С не договориться.
  • Я PQ изучаю только несколько дней и мне было важно понять алгоритм преобразования. Благодаря вам, теперь я смогу обрабатывать и другие отчёты, с другими шапками  :)
Правда возникла одна проблема, по шагам делал преобразование, и на этапе отмены свёртывания:
Цитата
#"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Повышенные заголовки", {"Клиент", "Менеджер", "Тип бизнеса"}, "Атрибут", "Значение"),
пропадают строки с в которых у фирмы значения отсутствуют. Что есть нехорошо...Я решил эту проблему через Table.ReplaceValue(#"Измененный тип1",null,0,Replacer.ReplaceValue,{
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Столбец1", type text}, {"Столбец2", type any}, {"Столбец3", type text}, {"Столбец4", type text}, {"Столбец5", type any}, {"Столбец6", type text}, {"Столбец7", type any}, {"Столбец8", type any}, {"Столбец9", type any}, {"Столбец10", type any}, {"Столбец11", type any}, {"Столбец12", type any}}),
    #"Удаленные верхние строки" = Table.Skip(#"Измененный тип",4),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Удаленные верхние строки",{"Столбец2", "Столбец3", "Столбец5"}),
    #"Транспонированная таблица" = Table.Transpose(#"Удаленные столбцы"),
    #"Заполнение вниз" = Table.FillDown(#"Транспонированная таблица",{"Column1"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Заполнение вниз",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Сведено"),
    #"Транспонированная таблица1" = Table.Transpose(#"Объединенные столбцы"),
    #"Замененное значение" = Table.ReplaceValue(#"Транспонированная таблица1",";","",Replacer.ReplaceText,{"Column1", "Column2", "Column3"}),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Замененное значение", [PromoteAllScalars=true]),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Клиент", type text}, {"Менеджер", type text}, {"Тип бизнеса", type text}, {"01.01.2016 0:00:00;Количество", Int64.Type}, {"01.01.2016 0:00:00;Сумма", type number}, {"01.02.2016 0:00:00;Количество", Int64.Type}, {"01.02.2016 0:00:00;Сумма", type number}, {"01.03.2016 0:00:00;Количество", Int64.Type}, {"01.03.2016 0:00:00;Сумма", type number}}),
    #"Замененное значение1" = Table.ReplaceValue(#"Измененный тип1",null,0,Replacer.ReplaceValue,{"01.01.2016 0:00:00;Количество", "01.01.2016 0:00:00;Сумма", "01.02.2016 0:00:00;Количество", "01.02.2016 0:00:00;Сумма", "01.03.2016 0:00:00;Количество", "01.03.2016 0:00:00;Сумма"}),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Замененное значение1", {"Клиент", "Менеджер", "Тип бизнеса"}, "Атрибут", "Значение"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Другие столбцы с отмененным свертыванием", "Атрибут", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Атрибут.1", "Атрибут.2"}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Атрибут.1", type datetime}, {"Атрибут.2", type text}}),
    #"Сведенный столбец" = Table.Pivot(#"Измененный тип2", List.Distinct(#"Измененный тип2"[Атрибут.2]), "Атрибут.2", "Значение", List.Sum),
    #"Переименованные столбцы" = Table.RenameColumns(#"Сведенный столбец",{{"Атрибут.1", "Дата"}}),
    #"Измененный тип3" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"Дата", type date}})
in
    #"Измененный тип3

но обязательно ли это? Иначе нельзя, или я что-то не так сделал на этапе Table.UnpivotOtherColumns  ???
 
Перейти    
Преобразование шапки таблицы в Power Query, Прошу поделиться приёмом преобразования сложных шапок через Power Query
 
#
06.07.2018 17:20:24
PooHkrd,ну нет конечно. Я возможно не так выразился. Файл из 1С я немного обработал - не имею права загружать первоначальный вид.
Во вложении вариант выгрузки из 1С, с обрезанными итогами (часть таблицы).
 
Перейти    
Преобразование шапки таблицы в Power Query, Прошу поделиться приёмом преобразования сложных шапок через Power Query
 
#
06.07.2018 17:03:57
Спасибо за ответ!
PooHkrd написал:

Цитата
Слегка изменил ваш пример - не нужно загонять в заголовки первую строку таблицы. Так преобразовывать проще.
PooHkrd, Дело в том, что в дальнейшем, в определенную папку, будет выгружаться отчёт из 1С, а PQ будет обрабатывать новый файл и пользователь должен будет только обновлять запрос, поэтому вариант с изменением шапки не подходит.

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


Dark1589, Спасибо за информацию, обязательно посмотрю позже. Просто на работе заблокирован youtube.
 
Перейти    
Преобразование шапки таблицы в Power Query, Прошу поделиться приёмом преобразования сложных шапок через Power Query
 
#
06.07.2018 15:40:22
   Выгружаю отчёт по продажам из 1С. Требуется приведение таблицы из сложной в плоскую для дальнейшего анализа. Причем преобразовывать таблицу нужно через Power Query.Проблема в том, что по каждому клиенту за каждый месяц есть 2 показателя: сумма и кол-во. Изначально дата в шапке объединена на 2 ячейки, после загрузки в PQ дата остаётся только у "Кол-ва".
  Прошу поделиться приёмом преобразования сложных шапок средствами Power Query. Причём показателей может быть больше 3 или 4 (прибыль, объём).

Я искал по форуму, но похожие темы не дают вопрос на мой ответ. Таблицу прикладываю, на втором листе желаемый результат.

Работаю в Excel 2013 + надстройка PQ.
 
Перейти    
Страницы: 1
Наверх