Урок 7. Отменить свертывание столбцов в Power Query (Unpivot)

Обратное преобразование данных из сводной таблицы в "длинную" (Unpivot) часто является проблемой. В Power Query операция производится специальной командой нажатием одной кнопки.
Файл с примером и другие уроки о Power Query по ссылке statanaliz.info/uroki-power-q...
► Корпоративный тренинг "Продвинутый уровень MS Excel":
statanaliz.info/trening-prodv...
► Онлайн курс "Продвинутый уровень MS Excel":
statanaliz.info/kurs-prodvinu...
**************************
** Мой сайт об Excel и статистике **
statanaliz.info/
** Странички и аккаунты в соцсетях **
Facebook: / statanaliz.info
Вконтакте: id_statanaliz_info
Твиттер: / statanaliz_info
Telegram: t.me/statanaliz

[@ExcelStore]

Спасибо! Ждем новых видео на эту же тему.

[@user-dj7ox1zb1h]

Класс!!! Спасибо. Кратко, конкретно, понятно.

[@user-ok3pm2qe4n]

Спасибо большое! Дай Бог тебе здоровья, мил человек!!!!!!

[@Natali_Pavlyuk555]

Конечно не зря!!! Спасибо Вам большое!!!🙏🙏🙏

[@user-qi9bl9hd9l]

Дмитрий, отличная функция и вы ее отлично объяснили. Спасибо

[@statanaliz]

Благодарю за комментарий.

[@ilia_tovkach]

Большое спасибо, не мог даже сформулировать, что мне нужно, но это именно то, что искал!

[@statanaliz]

Действительно, термин unpivot на английском звучит понятнее, чем "отменить свертывание столбцов" на русском.

[@user-vi6tf5ox1c]

Классная функция. Сколько я вручную их на отменял .... Спасибо

[@user-eo4wq8nu5r]

Гениально !!!! Сэкономлю очень много времени! Спасибо

[@statanaliz]

Рад, если поможет! )

[@maryyarsich9781]

Благодарю, Дмитрий! знать бы раньше про unpivot!

[@statanaliz]

Пожалуйста. PQ не так уже давно придумали.

[@user-zu5bo7ei4p]

Дмитрий Вы гений!

[@statanaliz]

Спасибо, конечно, но я только учусь ))

[@kamolkurbanov7504]

Отличный ролик

[@anastaywafoto7208]

Спасибо, Дмитрий за уроки! Все осень доступно. Интересует функция можно ли, чтобы Эксель брало данные из таблицы и создавал много шаблонных документов, но с разными именами и данными?

[@slavochkafomin]

красавчик! спасибо от души

[@andrewk3958]

Да, полезно. Спасибо!

[@statanaliz]

Пожалуйста!!

[@gioz9961]

SPASIBO!

[@statanaliz]

Пожалуйста )

[@user-uk1eu8gv9e]

Спасибо за видео, отличная функция. Раньше такую обработку делал через мастер сводных таблиц, но там много шагов и весьма не очевидный последний шаг. Ваши видео вдохновили на автоматизацию создания нескольких отчетов. Все получилось! Подскажите, в PQ можно остатки из вашего примера "размазать" так чтоб в каждой строке была 1 единица товара (например, по Товару 1 на 20.05.19 - 20 ед. остатков, эта запись идет в одной строке, а нужно сделать 20 строк по 1 ед.)?

[@statanaliz]

Насколько знаю, готовой команды нет. Придется что-то придумывать.

[@1iuh]

У вас такие хорошие уроки, понимание и изложение тем, что вы вполне можете (ставить себе цель) достичь статуса MVP (Наиболее Ценный Профессионал), что в РФ пока имеет только один Николай Павлов, сертифицированный тренер по Excel и продуктам Microsoft Office, (о чём он упоминает в своих видео). Из зарубежных авторов очень много MVP: это ExcelIsFun (Mike Girvin) MrExcel (Bill Jelen) и др. профи; для них любая компьютерная грамотность, языки Python, Java, C и т.д. - фактически созданы на их родном англ. яз и гораздо доступнее для освоения, чем нам. А когда-то в СССР были языки программирования на русском, но какие-то продажные чиновники решили не развивать отечественное программирование, со временем всё загубили (слили) и перешли на иностранное; иначе в СССР могли бы создать со временем свои аналоги любых программ, как китайцы на китайском.

[@statanaliz]

Спасибо на добром слове. Работаю в этом направлении )). А СССР для того и развалили, чтобы мы покупали чужое.

[@1iuh]

@@statanaliz На вашем сайте указана стоимость тренинга Excel 100 000 рублей. Это в белорусских или РФ рублях? стоимость за каждого чел или на всю аудиторию слушателей в приглашающей организации, а там уж сколько их наберётся? В рублях РФ 100 000/чел - это очень много, а для неограниченной группы от организации - вполне норм.

[@statanaliz]

@@1iuh это стоимость проведения тренинга в организации в рублях РФ, количество участников не ограничивается.

[@batyrpalanow2687]

Здраствуйте Дмитрий а можно зделат выпадаюший список с набором поиском чтобы не искать если список большой а чтобы он выводил автомотически при набранных буквах без VBA

[@Zhesha84]

Подскажите пожалуйста есть какая-нибудь возможность разделить чередующиеся значения на два разных столбца?

[@batyrpalanow2687]

Да Дмитрий как я говорил я и этод метод пробывал срез, шкала только тут ведется приход расход а мне нужно остаток на начала месеца ДТ, начала месеца КТ, оборот ДТ, оборот КТ и остатки на конец месеца по ДТ и КТ

[@user-ij1fl5hz3z]

будут ли новые уроки по Power Query, Дмитрий??? стоит ждать???

[@statanaliz]

Да. Следующий ролик про объединение запросов.

[@-VelikoDushniy-]

Здравствуйте Дмитрий, а как можно переобразовать "плоскую" таблицу со столбцом дат в таблицу где даты находятся на разных столбцах. То есть обратное действие того что вы показали на видео? Заранее спасибо!!!

[@StasSykora]

Добрый день, Дмитрий. Спасибо болбшое за полезное видео. Всё работает корректно, когда к даннай структуре (в исходном файле или таблице) после обновления дабавляется новый столбец, но не рабатает, когда при выгрузке данная структура не меняется !, но меняктся "шапка", например даты поменялись на + 1 месяц. Есть ли быстрое решение? Спасибо.

[@statanaliz]

Добрый день. С ходу не подскажу, не сталкивался. Может быть, кто-то из комментирующих ответит на Ваш вопрос.

[@alex_80]

Добрый день и Спасибо. У меня нет "отменить свертывание выделенных столбцов" ( выбрал "отменить свертывание столбцов" и в панеле формул руками удалил OtherColumns после Table.Unpivot и до первой скобки. Вроде сработало. но как включить такие кнопки, которых нет? Спасибо UPD// После урока 8 перешел на Excel 2021. Был на 2016 и слишком много отсутствий/несоответствий.

[@Ilya_01]

Здравствуйте Дмитрий. Не подскажете, может есть какая нибудь формула в Excel или комбинация, которая создаёт под или над собой пустую строку? Что то ни где на форумах не нашёл..

[@statanaliz]

Формулы не умеют создавать пустые строки. А вставить пустую строку можно, выделив строку, НАД которой нужно вставить пустую (shift + пробел) и вставить строку выше - ctrl + "знак плюс"

[@Ilya_01]

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

[@statanaliz]

@@Ilya_01 макросы - да, должны помочь. Только у меня под рукой нет готового решения. Задайте вопрос на форуме planetaexcel.ru, там точно помогут.

[@ppr8117]

@@Ilya_01, есть такая возможность в Excel и в PQ в частности. В настраиваемом столбце вводите: Table.InsertRows([Блоки], Table.RowCount([Блоки]), {[Column1=null, Column2=[Column3],Column4=null, Column3=null], [Column1=null, Column2=null,Column4=null, Column3=null]) Вместо Column подставляете имена своих столбцов... и будет Вам счастье P.S. перед этой процедурой необходимо сгруппировать необходимые столбцы

[@natalyakonovalova_]

Дмитрий, а если в файл добавили и столбец "Группа товара", и столбцы с новыми датами, тогда как ???

[@user-zs1bh2mf6s]

Хороший вопрос! Сразу в голову пришло.

[@batyrpalanow2687]

Дмитрий главный вопрос как зделать сводную таблицу чтобы он выводил остаток на начало задонного числа и на конец

[@statanaliz]

Нужно видеть таблицу, чтобы дать точный совет. Попробуйте через ВПР (или аналог) по нужной дате подтянуть остаток или другое значение из строки.

[@Arm_arm1928]

👍👍👍

[@statanaliz]

Спасибо )

[@frey-war8151]

Здравствуйте, подскажите пожалуйста, есть ли способ заполнять ячейки в Exel, числами с сайта? То есть, число к примеру: 2315 автоматически заполниться в ячейку. Спасибо

[@statanaliz]

Здравствуйте. Нужно делать запрос на интернет-страницу. Однако сам запрос зависит от того, как организована страница. Нет стандартного решения. Позже сделаю урок или два о подключении к интернету.

[@user-hp6sk6yq4y]

тоже волнует эта тема)

[@batyrpalanow2687]

Куда мне отправить образец

[@batyrpalanow2687]

Отличное видео и очен хорошо объясняите. Дмитрий я ваш подписчик и я почти все ваши видео смотрел, но негде ни увас ни удругих я не могу найти то что мне нушно. Помогите пожалуйста я очень много пытался зделать сам нужную мне таблицу ну не как не получаеться не как не могу до конца закончить. Я бухгалтер мне надо чтобы в таблице выводил начальный остаток по дебету и кредиту и автомотически выводил дибиторы-кредиторы по клиентам и чтобы можно было выбирать по конкретным числам или за месяц и так дали. Как я говорил я много раз пытался в сводной таблице но там все плусуеться не возможно вывесьти остатки можно только обороты, также я пробывал функцией сумесли, сумеслимн и многими другими функциями. Дмитрий пожалуста или лучше зделайте видио.

[@statanaliz]

Если из большой таблицы нужно фильтровать данные по времени и/или другим признакам, то попробуйте для начала обычную таблицу Excel - kzbin.info/www/bejne/nnrHgoF6gqtna68. Там есть срезы, которыми удобно делать фильтрацию.

[@natalyakonovalova_]

Дмитрий, вы корочку каку- нибудь выдаете после прохождения вашего онлайн- курса: продвинутый Excel? Ну, вы поняли, о чем я...

[@statanaliz]

Да. Только с этим вопросом лучше на почту писать, а не на ютубе )

[@user-zl2lf8md3s]

Дмитрий, вы показали инструмент абсолютно не автоматизированный, это просто ручная работа. На примере 12 строк-это быстро, а если исходник состоит из 1000 строк? Вы посоветуете тот же метод?

[@statanaliz]

Да, 1000 и более строк не проблема для Power Query.

[@user-zs1bh2mf6s]

Дмитрий, спасибо за пользу!!! Прошу Вас помогите с одним вопросом: как в PQ выделить все столбцы независимо от их количества!? Очень нужно, помогите. Это нужно при загрузке "Из папки", где иногда попадаются файлы с немного другой структурой (не 23 столбца, как у всех файлов, а например 31 столбец). Выделение всех столбцов нужно для того, что бы потом заполнить вниз и уже потом по названиям столбцов (не "null") удалить другие столбцы.

[@statanaliz]

С ходу не знаю. Может, кто-нибудь в комментариях подскажет?

[@batyrpalanow2687]

Скажите пожалуйста почту Facebook и другие унас в стране не работают

[@statanaliz]

Почта в описании канала. Здесь не пишу, чтобы поменьше спама приходило.

[@user-qn5by5iv7u]

2021

[@user-qn5by5iv7u]

2022

[@biweb13]

5+ ...в Power Query

[@statanaliz]

Сергей, спасибо большое, что заметил ошибку и сообщил. ))

[@biweb13]

Дмитрий, это тебе большое спасибо за качественный и полезный контент!!!