Форум пользователей Visio
http://visio.getbb.ru/

Visio + Excel Power Query
http://visio.getbb.ru/viewtopic.php?f=29&t=1429
Страница 1 из 3

Автор:  Machestro [ 10 мар 2019, 12:48 ]
Заголовок сообщения:  Visio + Excel Power Query

Сегодня, впервые, я хочу не задать вопрос, а поделиться одним полезным открытием. Сразу оговорюсь, что данный пост будет понятен тем, кто знаком с Power Query и, знакомых со структурой vsdx файла, как набора файлов XML. Но всё же стоит напомнить что такое Power Query (далее PQ) - это стандартная надстройка Excel, которая по умолчанию поставляется с версией 2016, и позволяет получить данные практически из любого источника, и изменить их так как надо (раньше она так и называлась Get & Transform).
Всё началось с того, что наткнулся на статью, как можно извлечь данные из zip архива с помощью PQ (http://sql10.blogspot.com/2016/06/readi ... ery-m.html). И тут я вспомнил, что vsdx файл - это по сути zip архив, который содержит набор xml файлов, и решил попробовать, решение, указанное выше, как результат - получилось, PQ увидел все XML файлы внутри vsdx файла, но увидел их как Binary файлы. Но у PQ есть функция Text.FromBinary, которая, собственно, извлекает весь текст любого файла (в нашем случае XML). Далее, можно напрямую работать с текстом, но можно пойти чуть дальше. PQ так же умеет работать с XML документами и разбивать их структуру на таблицы с помощью функций Xml.Document или Xml.Tables. Ну а далее - дело техники, вашего умения работать с PQ и вашего воображения, так как PQ это мощнейший инструмент, который удивляет меня всё больше и больше.
В качестве примера прикладываю Zip архив с двумя файлами Visio и Excel. Последний содержит данные, которые подключены к файлу visio, другие страницы содержат таблицы, которые отображают все шейпы на всех страницах, которые содержат пользовательские данные, а вторая - шейпы с данными, которые имеют связь с данными. Если в visio добавить ещё шейпов с данными, сохранить документ, перейти в Excel и обновить все данные (на вкладке данные), то таблицы обновятся - и всё это без единой строчки на VBA.
Таким образом, получаем мощный (масштабируемый, так как PQ может обрабатывать сразу несколько файлов) и гибкий инструмент для извлечения данных из visio.
Данным постом я не предлагаю готового решения, я лишь хотел показать, что есть такая возможность извлечения данных. Надеюсь будет кому-то полезно.

Вложения:
Комментарий к файлу: На вкладке Путь укажите путь к папке с файлом Visio
Example.zip [102.04 Кб]
Скачиваний: 16

Автор:  Surrogate [ 11 мар 2019, 11:07 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Machestro, спасибо за информацию ! и за то что разбавляете преобладающий здесь подход использования программирования :D
bzinchenko в сообщении #11049 писал(а):
Судя по нынешнему наполнению форума, основную аудиторию составляют разработчики (2). Это явный перекос. Форум должен больше ориентироваться на самую массовую аудиторию

Machestro писал(а):
дело техники, вашего умения работать с PQ и вашего воображения
в моем случае воображения мне явно не хватает! :wall:
phpBB [youtube]

уже несколько раз пытался себя заставить разобраться с PQ, но что-то не срослось. Наверно от того что я не могу придумать как это можно применить для своих задач (ибо я не маркетолог, не бизнес-аналитик и т.п.)

Автор:  Tumanov [ 11 мар 2019, 11:22 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Вообще, тема довольно интересная и явно заслуживает внимания. Нужно будет поковыряться...

Автор:  Machestro [ 11 мар 2019, 12:12 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Лично для себя я буду использовать описанный метод, для сравнения и синхронизирования данных между visio и Excel, так сказать, двусторонний обмен (знаю, что здесь была надстройка, но она мне не подошла).
А так, задавайте вопросы, постараюсь ответить на них (хотя я вообще новичок в PQ). Возникла даже мысль сделать видео и выложить на Youtube весь процесс, который реализован в приложенном файле. Но есть две проблемы, я никогда не записывал видео с экрана компа (и не знаю хороших и простых программ), и времени очень мало.

Автор:  Surrogate [ 11 мар 2019, 12:34 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Machestro писал(а):
Но есть две проблемы, я никогда не записывал видео с экрана компа (и не знаю хороших и простых программ)
в свое время я использовал бесплатные программы oCam (для захвата экрана) и OBS Studio (для монтажа, в принципе у этой программы тоже есть возможность захвата экрана. но 2 года назад, когда я ее использовал меня не устраивало качество и размер снятого с экрана видео). Возможно сейчас что-то изменилось, я почти полтора года пользуюсь Camtasia (которую мне подарили), меня все устраивает в ней.
Machestro писал(а):
и времени очень мало.
длинные описания (более 10 минут) не имеет смысла снимать, до конца редко кто досматривает :mrgreen:
Дополнено позднее: не знаю как сейчас, а в 2016 для вновь созданного канала были ограничения на длительность видео - 15 минут. я совсем не сразу смог получить право выкладывать длительные видео

Автор:  Tumanov [ 11 мар 2019, 12:54 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Цитата:
длинные описания (более 10 минут) не имеет смысла снимать, до конца редко кто досматривает

!!!
Очень серьезный момент! А иногда посмотришь, и жалеешь о потерянном времени.
Самый большой недостаток видео - это невозможность заранее узнать, стоит ли тратить на него время. Поэтому на мой взгляд увлекаться видео не стоит. Или делать именно так, как предполагается в данном случае. То есть сначала текстом описывается задача и ее решение, а только приложением дается видео - типа "посмотрите, как это происходило".

Автор:  Surrogate [ 11 мар 2019, 13:11 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Tumanov писал(а):
Поэтому на мой взгляд увлекаться видео не стоит.
где-то читал доклад британских учОных психологов, что для современной молодежи чтение текста более листа А4, это уже long read! Поэтому они предпочитают просмотр видео.
Второй аргумент за видео это хайп! Видео опубликованные в YouTube или в instagram будут чаще попадать в поисковую выдачу. Поэтому шанс что кто то досмотрит видео до конца, выше чем шанс что кто то найдет статью и ее прочтет.
Дополнено позднее: Видео подходит не для всех тем, например про программирование получается совсем скучно: когда докладчик в течении длительного времени вбивает код. Который потом зрители должны на своих компьютерах попытаться ввести :wall:

Автор:  Tumanov [ 11 мар 2019, 13:37 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Цитата:
что для современной молодежи чтение текста более листа А4, это уже long read! Поэтому они предпочитают просмотр видео.

Возможно. Но нужна ли такая аудитория?
Вообще конечно, все зависит от цели выкладывания информации. Она ведь может заключаться не только в в желании проинформировать читателей, но и в желании раскрутить автора :)
Так вот, для второй задачи чем больше народа ознакомится с материалом, тем лучше. И без разницы, кто именно тыкнет в кнопку. А вот для первой - немножко по-другому.
А если по собственному опыту... Статей по Visio я прочитал великое множество. И много было интересных и полезных. Видео посмотрел - ну никак не более десятка и больше половины из них были довольно бестолковы, а из оставшихся большинство - не интересны или бесполезны. Вот и думаешь - а стоит ли плодить это.
С другой стороны, видео как иллюстрацию к статье, я бы только приветствовал. Когда читатель представляет, что именно он увидит, на кнопку он будет давить со знанием дела. И не будет жалеть л потерянном времени. Короткий ролик на 5-10 минут может отлично дополнить статью.

Автор:  Surrogate [ 11 мар 2019, 13:38 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Tumanov писал(а):
Самый большой недостаток видео - это невозможность заранее узнать, стоит ли тратить на него время.
Если автор сделает нормальное описание к видео, этого можно избежать. Особенно если в описании будет оглавление ключевых моментов видео с привязкой по времени!

Видео и статей про PQ можно найти достаточно, даже на русском.
Дополнено позднее: Неофициальное, открытое методическое пособие к программе Power BI и надстройкам над Excel Power Query и Power Pivot

Вот со спецификой визио еще никто точно не делал
Tumanov писал(а):
Вот и думаешь - а стоит ли плодить это.
Согласен сделать хорошее видео, сложнее чем написать приличную статью. Мне этого еще ни разу не удавалось :wall:
PS про свои приличные статьи я тоже выдаю желаемое за действительное :mrgreen:

Автор:  Surrogate [ 11 мар 2019, 14:29 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Tumanov писал(а):
Но нужна ли такая аудитория?
Я бы не был столь категоричен! У меня есть один знакомый архитектор: он не ходил на курсы обучения AutoCAD, из огромного талмуда "Библия AutoCAD" он прочел лишь несколько глав. Всему научился благодаря видео-урокам в Youtube.
Посмотрев видео он заменил стекло у телефона, для этого пришлось открутить и отсоединить 26 различных деталей!
Товарищ увлекается квадрокоптерами: смотрит видео, заказывает детали, собирает аппарат. Однажды ему пришлось адаптировать прошивку для своего аппарата на C++, понятное дело что это не супер-программирование!
Но все же не надо считать всю аудиторию кто смотрит видео-уроки "блондинками из анекдотов" !

Автор:  Tumanov [ 11 мар 2019, 15:32 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Да нет, я понимаю, что там не только блондинки...
Но речь шла о "современной молодежи" для которой "чтение текста более листа А4, это уже long read! Поэтому они предпочитают просмотр видео" :)

Автор:  Surrogate [ 11 мар 2019, 15:40 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Tumanov писал(а):
речь шла о "современной молодежи"
ну надо же кому-то как то свои знания передавать ?
приходится готовить контент в более доступной форме. надеюсь не придется доходить до зачитывания текста речетативом :mrgreen:

Автор:  Surrogate [ 12 мар 2019, 02:44 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Для жителей МСК и окрестностей есть возможность поучаствовать в мероприятии, один из докладов про PQ
Доклад 3 — Подготовка данных в Power BI. От Power Query к потокам данных
(Дмитрий Соловьев, Power BI Lead, Астрософт, Санкт-Петербург)

  1. Знакомство с Power Query и получение данных из файлов и папок. В рамках этой секции познакомимся с синтаксисом языка M и разберем типовые ошибки, которые возникают при подключении к данным с использованием исключительно кнопок и меню.
  2. [How-To] Потоки данных в Power BI. Что это и для чего нужно. Получаем данные из внешних систем.
  3. Вопросы и ответы

Автор:  Tumanov [ 12 мар 2019, 17:49 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Полистал запрос из Example.zip и стало мне грустно.
Для получения довольно простой таблички-отчета потребовалось более 50 строк запроса на специальном языке. И строки по километру. И сдается мне, что запрос еще и от локализации зависит.
Чувствую, на VBA того же результата добиться в несколько раз проще.
Возможно, конечно, что запрос составлен не оптимально и сильно продвинутый пользователь сможет его упростить... Но все равно - подавляет.

Автор:  Surrogate [ 12 мар 2019, 18:20 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Tumanov писал(а):
стало мне грустно
я если честно пример еще не листал, и не ожидал чуда
Machestro писал(а):
и всё это без единой строчки на VBA.
т.к. изначально был в курсе что PQ использует язык M. Обзорная статья на хабре
Еще статья от Максима Уварова

Автор:  Machestro [ 12 мар 2019, 18:24 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Запрос вообще не оптимизирован.
Но это позволило мне узнать какие шейпы связаны со строкой данных, что vba мне не даёт сделать, точнее даёт, но только перебором. Да и запросы я же не писал, а только мышкой тыкал.

Автор:  Machestro [ 12 мар 2019, 18:30 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

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

Автор:  Surrogate [ 12 мар 2019, 18:49 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Machestro писал(а):
Тут уже надо задуматься кто быстрее справится vba или PQ
наверно все же PQ, но времени на обучение работе с языком запросов M убьешь точно не меньше! Хорошо если это еще где-то пригодится :D

Автор:  Machestro [ 12 мар 2019, 18:51 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

Если, становится профи, то времени не меньше, а если чисто получить результат, то на много меньше. Мне понадобилось пару дней, а vba я изучал гораздо дольше

Автор:  Surrogate [ 12 мар 2019, 18:55 ]
Заголовок сообщения:  Re: Visio + Excel Power Query

:mrgreen: Я уже понял в какую категорию вы меня занесли, и 99.9999% пользователей форума!
немного перефразируя: любите ли вы кошек писать запросы в Power Query ?
Изображение
GitHub: Power Query Documentation

Страница 1 из 3 Часовой пояс: UTC + 3 часа [ Летнее время ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/