Как правильно разворачивать вложенные таблицы в Power Query
Вставка
- Опубліковано 9 лют 2025
- Как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы. Скачать пример www.planetaexc...
Мои книги planetaexcel.ru...
Мои онлайн-курсы www.planetaexc...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Наш канал в Telegram t.me/planetaexcel
Спасибо! Вы отлично объясняете!!! Четко, грамотно, по сути. Работаю с большими массивами и по вашим примерам запросто обработала сотни таблиц. Спасибо! Великое дело вершите - помогаете нам становится умнее и выполнять работу быстро и грамотно. Удачи вам и больше новых видео ;) !!!
Николай с днем рождения Вас!
Благодарю Вас за знания!
Спасибо! 45 - ягодка опять :)
@@planetaexcel 😉
Николай, спасибо очень помог ваш урок, а то ранее приходилось удалять шаг "Развернутый элемент Data" и заново его создавать, чтобы все обновилось при добавлении нового столбца. Супер!!
"Когда знаешь - все просто!". Цитата по жизни. Спасибо большое.
Классно, всё чётко и без воды 👍
Я Ваш фанат ! Велике спасибі за контент ! Хай Бог дарує щасття і добробут !
Я еше не сталкивался в ютубе с роликами такого высокого уровня.
Крайне полезный ролик большой респект и низкий поклон!
Очередной плюсик в карму Николая, а мне плюсик до профессионализма.
Спасибо!
Спасибо большое, что продолжаете свои полезные выпуски! Это сейчас очень важно понимать, что жизнь продолжается и где-то есть стабильность.
А если по существу Excel, то Вы - тот, чье объяснение даже довольно сложных вещей, для меня становится просто и понятно!
Огромное спасибо очень полезный урок!
Низкий поклон и большой респект за Ваши труды на благо народа!
С Днём рождения, Николай!!!
Спасибо! 🙏😁
Николай,с днём рождения. В наше время главное здоровья. А через доп.столбец же по-моему можно развернуть. Команду не помню но реализацию видел точно.
А какого числа было сие событие?
Молодец. Не бросил нас, подписчиков. А есть и такие.
Спасибо большое! Как всегда очень ёмко, просто и главное предельно понятно. Молодец!
Большое спасибо за видеоурок!
Во первых здоровья вам и большое спасибо что столько лет преданны мвоему делу, и то что простым обывателям как мы дозодчиво обьясняете принципы работы в Ексель. Вам вернётся это добро кмноженное на 10.
Николай, спасибо! Слежу и жду ваших замечательных уроков.
Отличный урок, а главное, как всегда полезный. Спасибо!
Примерно полгода назад начал много внимания уделять PowerQuery, PowerPivot.
Поначалу решения на языке M казались страшными, непонятными. То есть я мог повторить последовательность действий из видео и получить результат, но чтобы самому додумать решение подобных задач - нет. Интересно, что со временем, когда информация уложится, возвращаешься вновь - тот же язык М становится более понятным, логичным.
Постепенно я начинаю видеть красоту (гибкость) решений, написанных на коде.
В общем, Николай, вам спасибо, потому что мое путешествие в анализ данных началось с ваших видео об Excel :)
*Всё гениальное - ПРОСТО!* Спасибо Николай!
Спасибо ОГРОМНОЕ!!! Часто сталкивалась с такой проблемой))) 🙂
Восторгу нет предела 🙏🙏🙏🙏🙏
о новая 🎧 отбивочка 🎧, круто.
👍👍👍👍а урок как всегда: выше всех похвал
Это генально) Николай, спасибо огромное!
Как всегда актуально и информативно!
Спасибо! Совсем недавно столкнулся именно с такой проблемой
👍
Как обычно, спасибо за приём)
Спасибо! Отличная и доступная подача!
Круто, давно это искал, спасибо!!
Спасибо 😊
Это очень круто!
Николай, Спасибки!
А вот это кстати мне очень нужно 👍
Как обычно оч. круто, спасибо!
Супер!
Спасибо! Круто как всегда!
Спасибо за видео, очень познавательно)
Спасибо! Отличный ролик! Но, вот, что подумалось. Описан способ загрузки наименований столбцов таблиц. Но можно ли подгрузить актуальные наименования колонок, если изменение/добавление/удаление столбца произошло в одной таблице и эта таблица не первая? Не могу пока придумать, в какой ситуации может понадобиться, но, тем не менее...
Спасибо большое😀
Спасибо! 💥Как всегда ОЧЕНЬ актуально. Простите, не помню - мы уже проходили, что делать, если появится новая табличка, либо какие-то из старых исчезнут?
Николай, спасибо за ваш труд. Вы даете очень много полезной информации. Не могли бы вы подсказать как можно организовать изменение типа данных динамических столбцов, ведь там идет привязка к имени столбца. При подстановки функции List.Range (для определения имени столбца по его номеру) в функцию изменения типа данных, конструкция перестает работать.
Здорово. Спасибо
В свете последних событий, на тот случай если микрософт офис окажется под запретом, зашёл в Мой офис, эксель и стало как то печально, мои файлы в нем не работают. Было бы интересно увидеть Ваш обзор этого продукта или просто узнать мнение о нем
Ps: я так и не нашел в этом офисе как зайти в vba
Maslennikoff_Alex, в МойОфис недавно только сводные таблицы появились, ты о чём? это отставшая программа
Ещё раз 🔥.
После просмотра видео))))
Добрый день. Николай огромное спасибо за Ваши уроки. Мне они очень помогает во многом разобраться. Хочу попросить Вас о помощи: при сборке данных в Power Query возникает ошибка:
Expression.Error: Ключу не соответствует ни одна строка в таблице.
Подробные сведения:
Key=Record
Table=Table
Крутой видос🙏🔥
Спасибо за полезную информацию. Подскажите, а как сделать тоже самое, но для источника данных из JSON? текущие рекомендации не работают, к сожалению.
Огонь!
Спасибо
Николай, добрый день! Спасибо Вам за ваш труд, ролики правда полезные.
Небольшое предложение по возможной теме ролика: оч часто в сводной нужно посмотреть топ-5/10/.. позиций, но при этом остальные не убирать, а свернуть в позицию " прочие", т.е. чтобы в итоге выводилось 6/11/... Строк и общая сумма оставалась такой, какой была изначально.
Существующий в экселе фильтр по топ прочие строки просто убивает... Если подскажите, как это можно делать красиво (не в лоб, как вероятно большинство делает), то буду премного благодарен!
Здравствуйте.
В сводной таблице выделите все наименования, которые вы хотите объединить в Прочие, правой кнопкой мыши вызовите меню и выберите команду Группировать. Переименуйте получившуюся группу, встав в ячейку, F2, Прочие.
Спасибо, как правильно разворачивать при слиянии?
Спасибо.
👍👍👍
Отлично, а как сделать, если кто-то захочет сменить заголовки? Так как они тоже привязаны к пунктам в запросе, приходится переименовывать названия внутри формул в запросе. Может есть более простой способ?
При разворачивании записей, хранимых в json, получается так что Power Query анализирует первые порядка 1 тыс. записей и по ним разворачивает столбцы, если в следующих записях в json имеются дополнительные элементы, то Power Query их не развернет (т.е. если самостоятельно формировать json, а элементов нет, то для в этих случаях все равно необходимо их учитывать в json). Предложенный метод (в видео сказано что заголовки берутся из первой таблицы) будет работать только если все таблицы имеют одинаковую структуру.
Само-собой, это пример про одинаковые таблицы. Если они у вас разные - придется собирать все заголовки в список и удалять дубликаты отдельным запросом, скорее всего.
Ну, и кто говорил, что монетизация видео для блогеров - это главное? Счастье - это когда любимая работа ещё и хобби. Хотел бы я иметь такую работу.
Добрый день. Ваши уроки очень полезны и познавательные. А подскажите работали ли вы когда нибудь с "МойОфис" (в плане написания макросов). И если возможно, то подскажите думаете ли Вы дополнить свои уроки данной темой.
Николай, помогите разобраться.
В настройках среза есть такое примечание "Имя для использования в формулах: срез_..."
Как использовать это имя?
Класс
Красиво)
Николай, где вас искать после блокировки Ютуб?
t.me/planetaexcel ;)
Николай, скажите, пожалуйста, как быть если в двух других таблицах есть дополнительные столбцы, которых нет в первой?
Столбцы должны быть идентичны. Более того данные приемы работают при обязательном переименовании (или вернее обязательном её участии) 1-й таблицы (в примере нулевая строка). Если строку соответственно в PQ поставить 1, а не нуль, то обязательно будет менять наименование во 2-й таблице. Насколько я понял..
Николай, будете делать канал про OpenOffice? Или подобные открытые аналоги с учетом текущих реалий?
Не хотелось бы :) Но посмотрим по обстановке, конечно. По крайней мере, обзор + и - ближайших аналогов Excel имеет смысл сделать, думаю.
@@planetaexcel спасибо, ждём:)
@@planetaexcel действительно, страшно даже представить новые реалии. С Excel я многорукая Лакшми, а без него? Просто курица!!!
Как активировать подсказки (выпадающие) при вводе функций и команд в PowerQuery?
Важная фраза в финале видео: достаточно обновить наш запрос. Он автоматически извлечет шапку из ПЕРВОЙ таблицы и использует её присборке всех данных. Т.е. обязательно чтобы изменения были в первой таблице чтобы и новые данные из других талиц подтянулись в "сборку". Так я понял
супер
Метод довольно ресурсоемкий с точки зрения системы. И что если таблицы отличаются от первой? Можно использовать table.join() чтоб не думать вообще что там с названием столбцов
Николай, есть небольшой вопрос) я ввёл в ячейку 31.11.2022 и мне не выдалась ошибка, хотя такой даты не существует) подскажите, как избавиться от этой гадости?)
Excel принял вашу не существующую дату как ТЕКСТ
@@denisgaraev7156 я думал в экселе автозащита стоит от такого
А вы не подскажите, что за утилита у вас стоит? Каторая подсказывает какой код вам писать
Если вы про выпадающие подсказки, то они появились с одним из обновлений Office пару лет назад, насколько я помню.
Николай, будет ли книга по DAX?
Пишу. Но вдохновения в последнее время маловато, к сожалению.
@@planetaexcel А нет ли у Вас в планах (хотя бы дальних) книги о PBI?..
Скобки в PQ бывают обычные, квадратные, фигурные и (как в языках программирования, типа Python) - для создания (не помню точно в какой последовательности) списков, кортежей, множеств.
В квадратных - имя столбца, в обычных - функция пишется со скобками, а фигурных скобках { } что? список?
Добрый день! [\w|.|-]*@[\w-]*\.[\w|.]* - это регулярка из примера для поиска адреса мейл. Вопрос у меня в следующем , почему квантификатор везде *, а не +. С * получится в теории неправильный результат....
Какое отношение этот вопрос имеет к данному видео? :)
@@planetaexcel немного не там прокомментировал :))))) бывает:)
Спасибо! Не кажется ли вам, что так PQ должно разворачивать по умолчанию?
Детские болезни PQ.
К v.30 поправят ))
Или нет (((
А что если объединяемые таблицы имеют разное количество столбцов? Судя по примеру, берется список столбцов из первого по списку файла, а во втором например может быть столбец которого нет в первом по строке. И его не "зацепит" приём
Предлагаю такой вариант:
=Table.ColumnNames(Table.FirstN(Table.Combine(Table[Data]),0)) //Объединяем через combine все файлы котор нужно собрать, колонка называется [Data]. Затем оборачиваем всё в сохранить первые строки = 0, нас интересуют названия столбцов из всех файлов. И третье - Table.ColumnNames, получаем список всех столбцов из всех файлов. Шаг обзываем "List'
Далее добавляем шаг после
= Table.ExpandTableColumn(Table, "Data", List, List)
Надеюсь, Николай моя идея Вам понравилась(❁´◡`❁)
Как развернуть вложенные таблицы в список, удалив при этом некоторые столбцы внутри них?
As always…the shikardos
Даже не понятно, лучше ли стало с этой формулой, раньше при разворачивании создавалась пользовательская функция, внутри которой был запрос для файла-примера. Периодически производил в нем некоторые манипуляции
2022
Павел, Добрый день! помогите пжта. У меня есть инфо кода точек и напротив названия оборудования, таких строк с одинаковым кодом точек может быть 40, та как есть разное оборудование на этой точке, как мне сделать так, ро, чтобы напротив одного кода точки горизонтально встали поочередно все типы оборудования этой точки в каждой ячейке. то есть точка должна остаться в строке уникальной, а оборудование повторялось в столбце напростив точки.
Виктория, гляньте www.planetaexcel.ru/techniques/2/15810/ Похоже на вашу задачу, если я правильно вас понял :)
как развернуть таблицу так чтоб не строки задублировало, а в столбцы развернуло?
Развернуть, а потом транспонировать строки в столбцы, может быть?
Это косяк разработчиков. Тут надо обращаться к ним, чтобы исправили кривой код. Эта опция должна работать без всякого шаманства.
Соглашусь. Надеюсь, это починят в ближайших обновлениях.
2023
А в условиях бомбардировок ваши советы так же эффективны?
Зачем ещё и здесь это? Вам мало других форумов???
Всю это надо банить. Эти идиоты не понимают, что если раньше они вызывали сочувствие, то после таких комментариев, только раздражение.
@@ytolk эка вы возбудились! Нам не нужно ваше сочувствие. Вот великий Зю уже инициирует отмену смертной казни, великий Пу собрался бороться с пятой колонной. А Ексель не что иное, как подрывающее духовные скрепы американское ПО. Так что ...
Валерий, понимаю и разделяю ваши чувства, боль, отчаяние и агрессию. Я тоже хочу, чтобы этот кошмар прекратился, поверьте.
Но пока есть возможность, я буду делать то, что могу - учить и помогать - как умею.
@@nasturtsiya "Великий Пу" - это и есть "Пятая колонна", и вообще "Президент - это ШИРМА" (цитата от тех, кто за ШИРМОЙ в любой стране - РФ, Украина, США и т.д.; кто почти 30 лет назад написал "Наши планы относительно славян", о провоцировании войны между Украиной и РФ, а сами они будут в тени; "они" запланировали геноцид славян и "маленькую резервацию на Севере, подобную резервациям для индейцев, а на территории Украины и РФ построить Хазарию; они хвалятся, что не допустят "Никаких высоких технологий" и сделают полный упадок промышленности, что мы и видим при "Великом Пу".
Спасибо огромное. Очень полезное видео. Метка (больше для себя) самого главного ua-cam.com/video/ik0KWTV10S8/v-deo.html
= Table.ExpandTableColumn(#"Другие удаленные столбцы", "Data", Table.ColumnNames(#"Другие удаленные столбцы"{0}[Data]), Table.ColumnNames(#"Другие удаленные столбцы"{0}[Data]))
Можно сделать все тоже самое в два шага
Step_1 = excel.woorkbook... [〔Kind = "Table"〕][Data],
Step_2 = Table.Combine(Step_1)
🔥