Сборка разноформатных таблиц в Power Query

Рет қаралды 47,289

2 жыл бұрын

Как собрать несколько разноформатных простых (не умных!) таблиц с одного листа Excel, а затем развернуть их в плоские (нормализовать) и соединить в единое целое с помощью Power Query. Скачать пример www.planetaexcel.ru/technique...
Мои книги planetaexcel.ru/books/
Мои онлайн-курсы www.planetaexcel.ru/learning/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel

Пікірлер: 110

@Albertus99 2 жыл бұрын

Всегда приятно иметь дело с умным человеком! Низкий поклон за ваши труды!

@user-zq2hd8ki9u 2 жыл бұрын

Как всегда нереально крутой пример в супер качественном формате обучения. Нам бы в каждой школе/техникуме/ВУЗе хотя бы по одному преподавателю такого уровня и подачи как у Николая - система образования вышла бы на другой уровень подготовки. Ничего не имею против текущих учителей, они большие молодцы и тянут как могут, но то что у автора прям талант это бесспорно. Спасибище прям.

@planetaexcel 2 жыл бұрын

Спасибо! :)

@user-zh3oh1pr8c 2 жыл бұрын

Спасибо Николай, за Вашу работу, все как всегда понятно, лаконично.

@StasSykora 2 жыл бұрын

Круто! Кажется я нашёл для себя давно решение для обработки экспорта данных))! Большое спасибо!!

@user-mv6xq9sb3g 2 жыл бұрын

Николай, очень полезное видео. Так умело показали решение моей проблемы.Будем пробовать применять в жизни.Благодарю Вас!

@Macnayd 2 жыл бұрын

Огонь! По языку "м" мало информации в интернете. Спасибо за Ваши уроки!

@antonkharitonov9007 2 жыл бұрын

Иван третий - собиратель земель русских, Николай Павлов - собиратель листов экселевских! Браво))

@senge_v4871 2 жыл бұрын

И снова изящество! Просто браво! Power Query снова удивляет!!!

@user-qg9nu6cl2w 2 жыл бұрын

Эксель новый уровень ! Жду начала курсов. Уже записан.

@assem_me 2 жыл бұрын

Так здорово😍 Спасибо вам!😍🙏🏻

@user-ch6kr2qd2x 2 жыл бұрын

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

@user-OlgaL 7 ай бұрын

Это супер видео и объяснения. Благодарю вас.

@user-dr4od4tx7i Жыл бұрын

Шикарный урок. Отличный приёмы затронуты ссылка на строку ранее

@MrTrylobeat Жыл бұрын

Спасибо, очень помогло. Буду пользоваться

@sergeyd9989 2 жыл бұрын

Спасибо, полезные знания.

@asfalt66 2 жыл бұрын

Ну, традиционно лайк! Слова благодарности. Когда знаешь, всё просто)

@hhhohlick 2 жыл бұрын

Отличное видео, как всегда! Я решил бы немного не так 😀 , но самое главное - объяснить метод, а с этим с Николаем сложно соревноваться 😀

@planetaexcel 2 жыл бұрын

Спасибо, Максим! Но не скромничайте - по Power Query мне до вас далеко :)

@thefrostery Жыл бұрын

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

@oleg-shrek 2 жыл бұрын

Как всегда хорошо подготовленный и полезный, интересный материал. А насколько сложнее было бы создать из каждой таблицы запрос и потом объединить из по нужному полю? Или вообще не получилось бы?

@Albertus99 Жыл бұрын

Нищкий моклон большое спасибо!

@1BUXGALTER 10 ай бұрын

ОТЛИЧНОЕ ВИДЕО! СПАСИБО!

@biweb13 2 жыл бұрын

Отлично!

@jollyjuln1054 2 жыл бұрын

Уххх, какая крутотень. А книжка по языку М или про продвинутому PQ случаем не планируется?

@planetaexcel 2 жыл бұрын

Книжка пока нет, а курс уже почти готов.

@user-sj3iw6gm1r 2 күн бұрын

отличный пример

@berryblack9768 Жыл бұрын

Здравствуйте! Спасибо огромное за супер-видео, всегда всё очень ясно и понятно! Скажите пожалуйста, есть ли такая возможность, чтобы собирались не просто данные таблицы, но и например, оформление ячеек. Если кто-то делает заливку определенным цветом, но этот цвет очень важен. Перерыла весь Интернет, но не нашла, есть ли такая возможность в Power Query?

@user-lu5de8mu3w 2 жыл бұрын

Спасибо!

@Gunselinger 2 жыл бұрын

Ура! То что нужно и всегда актуально!

@alexandrgavrilyuk4090 2 жыл бұрын

Как всегда - круто! А как при Unpoivot оставить нулевые значения?

@Gamma9971 2 жыл бұрын

Спасибо. Вы как всегда восхитительны)

@user-ui2wu6mm9l 5 ай бұрын

Спасибо! Мне приходилось решать похожую задачку, однако "нехороший человек", который готовил эти таблицы, для наглядности вывел их в два ряда. То есть таблицы были не одна под другой, а ещё и рядом друг с другом. Причём, таблицы разных размеров, поэтому вот первую таблицу в левом ряду с первой таблице во втором ряду ну никак не склеишь! Мне пришлось сначала их все вручную в Excel перенести в один ряд, а затем уже в Power Query объединять похожим способом. Можно ли было как-то пошаманить в Power Query, чтоб избежать ручного переноса в один ряд в Excel? Буду признателен за разбор такого кейса!

@alec_mayor 2 жыл бұрын

Нетривиально 👍

@user-pq4yj7iu6l 2 жыл бұрын

Круто!!!

@pavell6284 2 жыл бұрын

Большое спасибо. Очень хороший материал 🙂🙂🙂

@MI-fd9es Жыл бұрын

👏🏼👏🏼👏🏼

@666saturation7 9 ай бұрын

великолепно

@Slava-nc2fe Жыл бұрын

❤

@user-uv6uh4bu9x 2 жыл бұрын

Николай, здравствуйте! Хотел бы понять для себя. Я работаю в финансово-экономическом отделе и хотел бы постоянно развиваться. Стоит ли прогрессировать и вообще учить VBA для создания макросов? Или лучше углубиться в Экселе в чем-то другом?? Что лучше изучать в экселе, чтобы стать незаменимым специалистом?)) Заранее благодарен за ответ! Кто из зрителей канала поделится своим мнением - тоже буду очень благодарен!

@mbassador_19 2 жыл бұрын

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

@Anatolii_Kodirov 2 жыл бұрын

Не по теме но всётаки, подскажите пожалуйста, как на пользовательской форме VBA в TextBox выровнить числа по правому краю и разделить группу разрядов чисел?

@anymatory8711 2 жыл бұрын

Николай, добрый день! Рассматриваете ли Вы в дальнейшем изучение Power Apps/BI/Automate?

@planetaexcel 2 жыл бұрын

В смысле - изучение? Уже года три как изучаю :)

@anymatory8711 2 жыл бұрын

@@planetaexcel, а где контент??))

@pavelivashchenko7047 2 жыл бұрын

Добрый день. В книге по power query указано, что возможно загрузить данные из exchange (хочу получить данные из outlook). Но фактически в других источниках exchange отсутствует(excel 2016). Помогите пожалуйста, как его добавить?

@rifesta6652 Жыл бұрын

Николай, спасибо за отличный урок! Подскажите как включить всплывающие подсказки при вводе формулы для пользовательского столбца? Спасибо.

@planetaexcel Жыл бұрын

Подсказки появились после очередного обновления - если не ошибаюсь, то году в 2019-2020 примерно. Так что надо просто обновить Office.

@rifesta6652 Жыл бұрын

@@planetaexcel Видимо, еще позже. У меня сборка версия 2110 (сборка 14527.20226) Октябрь 2021 и подсказок нет!

@user-iq5ox3dk8i 2 жыл бұрын

пример в ячейке текст который разделён этим "/" знаком (таких знаков около 10-20ти) Подскажите как в Эксель подсчитать количество этих знаков устал считать в ручную

@user-kz1uq5xz5r 2 жыл бұрын

Добрый день, подскажите пожалуйста, что надо сделать чтобы тоже выходил помощник по заполнению формул в запросах, у меня его к сожалению нет?

@B1a3e 2 жыл бұрын

Николай, здравствуйте. Подскажите пожалуйста, что делать если файл уже выгрузил в Power Pivot из Power Query, но там была допущена ошибка. Как снова вернуть ее в Power Query? При попытке удалить с Power Pivot кстати выдает ошибку, что таблица создана в Power Query и редактировать можно там

@ivan451f 8 ай бұрын

Подскажите: как настроить Power Query, чтобы при использовании функций языка M предлагалось автозавершение и подсказки (как у вас на видео)? по аналогии с формулами - когда начинаешь писать, то выпадающим списком предлагались продолжения

@UC0LMA1CQ6b3s6nrBlO4 2 жыл бұрын

Дякую Вам за контент! Я є Ваш фанат!!! Здоров'я і натхнення Вам !!!

@planetaexcel 2 жыл бұрын

Спасибо! :)

@user-wj7jb8os3u Ай бұрын

Прикольно, но менеджера можно просто вытащить, зацепившись за null. В этом случае не нужен индекс столбец

@writer8629 2 жыл бұрын

Спасибо большое! Кто-нибудь подскажите, почему не появляются подсказки с формулами в Power Query?

@planetaexcel 2 жыл бұрын

Нужно обновиться

@alekneg1652 2 жыл бұрын

Отличная подача материала. Подробно и без лишней воды. Интересна будет и обратная задача. Разобрать одну потоковую динамическую таблицу на несколько отдельных таблиц по названию инструмента например.

@planetaexcel 2 жыл бұрын

Недавно записывал такой видос :)

@odmitv 2 жыл бұрын

*А куда делись при unpivot столбцы ColumnsX?* Или они самоликвадировались при условии что в них не было данных? А если это не ColumnsX, а например "Северо-Запад" и продаж там нема, но эта инфа нужна в сводной (чтобы наглядно показать, что по Северо-Западу продаж НЕТ?

@planetaexcel Жыл бұрын

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

@Slava-nc2fe Жыл бұрын

Николай, пожалуйста, помогите. Во вложенных таблицах не работают функции списков. Например, возникает ошибка если посчитать нарастающий итог по Северу. Что делать? 9:39

@yurd563 2 жыл бұрын

Спасибо за видеурок!

@TheMaayun 2 жыл бұрын

Спасибо, как всегда интересно. Дорогая нынче репа стала))

@beldn5950 2 жыл бұрын

Я в таких случаях создаю справочник менеджеров и через условный столбец определяю, что менеджер, что товар. Не так красиво, да и придётся такой справочник дополнять. Самая большая проблема с pq - это очень низкая скорость работы. Когда запросов становится больше 4-5, то тормозит жутко.

@user-px6kx9gj6u 2 жыл бұрын

Отличное решение! Спасибо. У вас был урок по знакомству с "представлениям". Возможно ли развить тему? К примеру, как применять текущее представление в формулах. Типа если на листе выбрано одно из многих представлений для листа, то в ячейки текст такой-то?

@planetaexcel 2 жыл бұрын

Такое только макросом можно, подозреваю :)

@2008ulman 2 жыл бұрын

Даешь книгу по "М"! Ура товарищи!!!!

@alexandershavleyko1313 2 жыл бұрын

Masterpiece Collector👍

@Pozitive1000 2 жыл бұрын

А я обычно через первый вариант реализую второй.

@planetaexcel 2 жыл бұрын

Тоже можно :)

@user-bk1jk4wh3w 2 жыл бұрын

Сложно.. но очень интересно.. Сделали бы РQчуть более нацеленный на графический интерфейс, было бы вообще изумительно, ибо все это программирование хоть и не сложно, но не каждый день нужно ,и просто забывается( А так - создал пользовательский столбец, перетянул мышкой те ячейки что нужно. и заполнил вниз, а сам PQ пусть себе что хочет программирует... для решения задач с небольшими таблицами - то что нужно)

@ytolk 2 жыл бұрын

Крутотень.

@piktogor 2 жыл бұрын

Музыка огонь))

@Irinastolyarik Жыл бұрын

А если таблицы разноформатные не с листа , а книгами будут, что делать

@user-qu2sy6vg1x 2 жыл бұрын

С первого раза конечно все очень вроде как понятно, но через день , два сам уже не повтоишь 😂

@dmitryvashchenko3520 2 жыл бұрын

А можно ли как-то сделать это без Power Query? Excel под Mac - урезан и там нет этой надстройки :(

@planetaexcel 2 жыл бұрын

Только макросами. Функционал Power Query уже начинают потихоньку добавлять в Office 365 для Mac, кстати ;)

@mstrJons 2 жыл бұрын

Як завжди, на висоті

@ivanmamchych5802 2 жыл бұрын

Интересный урок. Спасибо. А что если бы у нас исходные таблицы не были бы расположены в одном и том же столбце A? Предположим, вторая таблица начиналась бы со столбца B? Надо подумать, как бы тогда их выровнять в PQ перед дальнейшими шагами M-кода 🤔

@owl1891 2 жыл бұрын

Уф, как все элегантно

@eduardmichel4068 2 жыл бұрын

Спасибо за урок. У меня вопрос, как справиться с ошибкой в Power Query, а именно загрузка файлов из папки, когда PQ пробует загрузить временный файл ~$имя и выдает ошибку. Вручную могу удалить. Можно ли процес автоматизировать?

@planetaexcel 2 жыл бұрын

А если отфильтровать список файлов, убрав из него все начинающиеся с `$

@eduardmichel4068 2 жыл бұрын

@@planetaexcel теперь работает спасибо

@Samuell_ua 2 жыл бұрын

Дякую за інформацію, дуже корисно!

@user-qd8nh5rr9z 2 жыл бұрын

Кайфовое видео

@anton_a388 2 жыл бұрын

Спасибо

@user-up7dq4mm6k 2 жыл бұрын

Николай здравствуйте! Прошу обратить Ваше внимание на почту от Вашего подписчика

@nbuhblhfrjy 2 жыл бұрын

А почему добавлять через столбец индекса? 🤔🤔 Можно же было что-то вроде each if [Column1] ”” and [Column2] = null then [Column1] else null Николай, также просьба: если возможно, то предлагайте решения полностью на языке М ) в конце видео или в допролике. В частности использования Table.TransformColumns вместо пары Table.AddColumn и пр

@planetaexcel 2 жыл бұрын

Можно и так, да. Просто хотел показать как делать ссылку на предыдущую строку - у меня же цель научить, а не только решить задачу ;)

@user-xp9qn3gs9t 2 жыл бұрын

Как настроить Power query, чтобы появлялись подсказки при написании формулы в поле пользовательского столбца? У меня так как на видео не появляются

@planetaexcel 2 жыл бұрын

Эти подсказки появились в прошлом году с одним из обновлений :)

@sherbininivanIS 2 жыл бұрын

Николай, а зачем вообще было делать столбец индекса? Не проще ли было сразу создать столбец "Менеджер" и сделать проверку по значению NULL во втором столбце?! Если NULL - значение из столбца1, иначе - NULL

@planetaexcel 2 жыл бұрын

В данном случае можно и так. Просто хотел показать технику ссылки на предыдущую ячейку того же столбца :)

@sherbininivanIS 2 жыл бұрын

@@planetaexcel Просто обычно Вы показываете оба варианта. Но да, техника интересная. Вопрос в одном: какой способ будет быстрее. Понятно, что через ссылку универсальнее, но это дополнительные расчеты PQ, а их лучше избегать. Или хотя бы кэшировать массивы ;)

@hhhohlick 2 жыл бұрын

@@sherbininivanIS ОДНОЗНАЧНО способ без индекса будет быстрее. Представьте себе таблицу на несколько десятков тысяч строк, в каждой строке которой ходим в предыдущий шаг и ищем по индексу там. Не быстро, увы.

@PUNTO1992 2 жыл бұрын

Добрый день. Вы пишите: "мы сначала ссылаемся на таблицу с предыдущего шага #"Добавлен индекс", а затем указываем имя нужного нам столбца [Column1] в квадратных скобках и номер ячейки в этом столбце в фигурных". Подскажите, пожалуйста, почему не работает, как в условии if, просто [Column1]? Спасибо.

@planetaexcel 2 жыл бұрын

В этом случае будет ссылка на ячейку из столбца Column1 из той же строки, а не на весь столбец.

@PUNTO1992 2 жыл бұрын

@@planetaexcel Спасибо!

@im_buddha 2 жыл бұрын

да сложновато для обывателя. но спасибо, что показали сложность решения задач в PQ - лайк однозначно такое ощущение что "тупой копипастинг" как раз более надежная и простая методика для такого случая Microsoft надо купить чье то готовое ETL решение - с PQ у них не получилось сделать это удобным

@planetaexcel 2 жыл бұрын

Да это еще простой случай :)

@lapochkahelenUA 2 жыл бұрын

Лпйк и обнимашки

@user-qn5by5iv7u Жыл бұрын

2023

@Borisyan1 2 жыл бұрын

Добрый день. Николай, Я вам на почту писал письма по поводу ошибки менеджера листов в Plexe. Вы так и не ответили как это устранить, а работать комфортно хочется!))

@planetaexcel 2 жыл бұрын

PLEX не хранит пароли к листам, а лишь вводит введенный вами пароль для блокировки и разблокировки. Так что проблема точно не с ним.

@Borisyan1 2 жыл бұрын

@@planetaexcel почему через стандартную функцию пароль снимается, а через менеджер листов пишет что пароли не совпадают? Может тогда подскажете?

@Borisyan1 2 жыл бұрын

@@planetaexcel у коллег работает корректно. У меня нет. Это может быть связано с тем, что мне переустанови систему, после чего я воспользовался "activatorom" чтобы восстановить лицензионного ключа? Коллеге тоже переустановил систему, но она не восстанавливала старый ключ, а поставила заново. Помогите пожалуйста! Я на работе всем разрекламировал плекс, а теперь у самого он не работает в полном объеме. Причем заметил у себя ещё такую особенность, если ввожу пароль через менеджер листов например 1, и в всплывающем окне тоже ставлю 1, то вылезает ошибка, что пароли не совпадают. Если же в первом окне ставлю например 1, а в сплювающем окне просто пишу *, то пароль программа включает. Если после этого снимаю защиту, то менеджер снимает защиту при любом введенном значении (1 или *). Вот как-то так!