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