Как обработать в Power Query таблицу с многоуровневой шапкой

Все приемы работы с Power Query предусматривают работу с таблицами у которых шапка (заголовки столбцов) в одну строку. В работе у нас таких очень мало. Обычно таблица имеет минимум 3 "этажа".
Как же быть? Как из такой таблицы получить базу данных для построения сводной таблицы? Очень просто - таблицу нужно транспонировать.
Присоединяйтесь к нам в социальных сетях и на сайте:
Статьи и примеры файлов Power BI и Excel www.finalytics...
Вконтакте finalytics
Facebook / finalytics
Telegram t.me/finalytic...
Записаться на курсы www.finalytics...

[@live_everywhere]

Гениально! Я каждый день мудохался с таблицами 1С, а теперь получаю удовольствие

[@salosteysv]

Спасибо за комментарий ) Еще лучше создать подключение сразу к папке, туда "складывать" таблицы 1С и настроить обработку сразу всех файлов.

[@izaharova3333]

Станислав, большое спасибо, что делитесь своими знаниями в доступной для понимания форме

[@Rustam_Imirlayev]

Большое спасибо! С первого раза повторять, а тем более адаптировать под свою задачу было сложно, но вот теперь стало легче))) Пришло понимание логики и алгоритма действий. Классика: лайк, подписка и комментарий.

[@xeviltimxy]

Понасоздают "визуально наглядных" матриц, потом извращайся с ними как хочешь))) Каждый аналитик знает, что матрицы и объединенные ячейки - зло)) Благо powerquery в этом деле довольно хорошо помогает. Особенно если нет таких запущенных случаев с многоуровневыми заголовками, то unpivot сделать в один клик буквально. Хороший урок, спасибо!)

[@SV-13]

Расскажите это Нуралиеву ))

[@fingenius]

Станислав, благодарю. Быстро, четко и понятно! Ждем еще видео с полезными фишками Query

[@АлександрДергилёв-п8х]

Станислав. Огромное спасибо за материал. Очень частый случай в повседневной работе.

[@FrauLIV]

Станислав, большое спасибо! Три дня искала это видео и наконец нашла!)

[@cytq]

Отличная инструкция, Станислав. Спасибо.

[@KaterynaMieshkova]

Спасибо! Замечательный лайф-хак! Так сразу и не додумаешься :)

[@lim_ra]

Это, ШЕДЕВР!!!!! ОГРОМЕНННОЕ СПАСИБИЩЕ))))))) подписочка оформленна ))))

[@salosteysv]

Спасибо за такую оценку!

[@anacryon4827]

Благодарю Вас!!! Спасибо огромное!!! Искренне!

[@yurd563]

Большое спасибо!

[@евгенийтаранов-ф3о]

Очень полезно спасибо!!!!

[@ArsenIslamovandhiscattle]

спасибо!

[@Lenokk-lr5de]

Потрясающее видео! А нет ли у Вас урока - как сделать обратно? - столбец из базы данных превратить в шапку?

[@salosteysv]

Только увидел вопрос ) Кажется нет. Если я правильно понял о чем вы спрашиваете, вам нужна функция которая называется "Столбец сведения"

[@hiasmrworldx6226]

спасибо

[@pretor700]

красавчик!

[@Sibbarb9]

Как раз то что нужно

[@АидаТенисова]

Спасибо!

[@senge_v4871]

Спасибо.

[@denysss5872]

Спасибо за способ. Не работает, если изначально строк много: в момент транспонирования или повышения заголовка после, выдаёт ошибку о превышении длины запроса, и всё.

[@РашИбнСулейманНафин]

Вот это даааа!

[@yaraslav2906]

Станислав,спасибо.Как всегда полезнейшие вещи рассказываете и показываете.Думаю,не ошибусь если скажу,что по количеству и качеству материалов по Power Query и Power BI в целом вы вне конкуренции.Очень мало материалов по этим инструментам.Так что, ждем новых видео. И не большой вопрос:на этапе разбиения ранее объединенного столбца возникает следующая проблема-в некоторых ячейках разбиение по разделителю не происходит,в результате в первом столбце остается объединенное значение (включая разделитель),а во втором столбце разделения появляется значение "null".Попытка выбрать на стадии изначального объединения другой разделитель результата не дала.

[@salosteysv]

Спасибо за отзыв! Это скорее всего возникает в пустых ячейках. Их нужно либо отключить фильтрами (если это итоги), либо заполнить пустые значения стрелочкой вниз.

[@yaraslav2906]

Станислав,а могут быть другие причины?В моем случае дело явно не в пустых ячейках.

[@ArsenIslamovandhiscattle]

очень плохо индексируется по поиску видео. надо поменять название. что нибудь вроде "преобразование в плоскую многоуровневой таблицы". и купить микрофон ;)

[@АнтонКутькин-ж8в]

Купить микрофон - это точно

[@salosteysv]

Друзья, спасибо за советы! Это видео я записывал спонтанно в командировке ) А вообще, микрофон куплен - посмотрите последние видео. За совет про индексацию - отдельный респект. Опять-таки, когда я записывал материал у большинства пользователей не было понимания, что такое "плоские таблицы" )

[@SV-13]

Станислав, а как быть с "многоуровневыми" ячейками, которые так любит 1С в своих отчётах? Как можно средствами PQ "трёхэтажные" ячейки столбца раскидать по отдельным колонкам?

[@salosteysv]

Да, в Power Query есть разделитель по переносу строки

[@abaymakhanbetov3787]

Какими клавишами центровали по после первого центрования??? Спасибо за видео!

[@salosteysv]

Не понял, что центровал?

[@abaymakhanbetov3787]

Комбинация клавиш: центровка слов или выражений внутни ячеек ПО ЦЕНТРУ ВЫДЕЛЕНИЯ??

[@MrGWolfer]

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

[@salosteysv]

Только увидел вопрос.... не прошло и 10 лет )) Мне кажется вопрос можно решить через условный столбец с "if"

[@MrGWolfer]

@@salosteysv спасибо за ответ. За прошедшее с момента моего комментария время мне много раз приходилось проделывать подобную операцию. Чего я только не делал, чтобы выделить разные уровни иерархии из одного столбца. Но в итоге все конечно сводится к if.

[@СергейСергеевич-с7ю]

Добрый день, подскажите как сделать. есть список сайтов. нужно сделать запрос по каждому адресу с поиском e-mail на главной странице сайта

[@ludmilastepanova8639]

А как обработать "таблицу", где многоуровневость шапки отражается в одном столбце. Например форма баланса. Только подготовив саму таблицу (разнести по столбцам) и затем применить данный вариант или это можно сделать в Power Query

[@ientoma]

Станислав, а если необходимо объединить несколько книг Excel такими же повторяющимися шапками логика действий сохраняется?

[@salosteysv]

Да, только запрос нужно делать целиком к папке

[@ArsenIslamovandhiscattle]

что делать, если есть несколько таких файлов и каждый вычищать весьма утомительно?

[@salosteysv]

Настраивать обращение к папке целиком

[@SV-13]

Так в том и прелесть этого способа, что последовательность действий сохраняется, как сценарий! Изменился исходный файл, обновил запрос - и данные свежие!

[@ArsenIslamovandhiscattle]

@@SV-13 я имею ввиду когда папка содержит 25000 типовых файлов. Не менять же у каждого источник вручную

[@SV-13]

@@ArsenIslamovandhiscattle Тогда - как и рекомендовал автор канала.

[@ArsenIslamovandhiscattle]

@@SV-13 спасибо, Кэп

[@КонстантинМайоров-я6э]

Столнулся с проблемой при траспонировании. Переворачивются не все столбцы. Значительная часть данных просто удаляется. Не подскажите как бороться с этим?

[@DeamonDarkFire1]

Дякую !

[@ИннаИннуля-н2т]

Не могу понять чем различаются понятия DAX POWER QUERY и язык М POWER QUERY ??? И там и там есть формулы, но когда и что примеяется ??? Что Вы смогли бы ответить о данному вопросу ???

[@salosteysv]

DAX - Data Analytical eXpressions это формулы в power PIVOT. А язык М это как раз power QUERY.

[@ИннаИннуля-н2т]

Спасибо. Буду искать учебный материал в Интернете. Так как мне это очень надо по работе.

[@salosteysv]

Или можно посетить наши семинары-тренинги.

[@Feldspyt]

Это не сработает с таблицей из 1с, когда сверху идут названия данных: Контрагент, договор и прочее, а внизу в этом же столбце на разных уровнях идут значения этих данных, к сожалению

[@salosteysv]

@@Feldspyt да, за такую структуру сложно "зацепиться". Надо в самой 1С менять представление отчёта (если версия такое позволяет).

[@Kost150rus]

У меня табличка с несколькими уровнями, одна строка в в последнем столбце разбита на две строки. И при загрузке в PQ выскакивает ошибка. Ладно одну таблицу поправить руками, но таких файлов 80 штук и загрузка фалов из папки в этом случае бесполезна

[@staspodolskiy3543]

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

[@Мамкин_толстый_бармен]

Вы из какой трубы говорите?

[@salosteysv]

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

[@Валерий-ь4ь8д]

Да, звук это слабое место, а за опыт благодарю!

[@denysrudniev4572]

Станислав, такой вопрос - добавил таблицу через Power Query, потом добавил её в модель данных и уже в PowerPivot добавил вычисляемый столбец с условиям "IF", связал таблицу с другой. Затем обновлял запрос, изменил его имя. Он потерялся и исчезли связи и добавленные столбцы. Что вы порекомендуете - добавлять столбцы сразу в Power Query (там оно чуть сложнее, больше похоже на код VBA, чем на формулу Excel) или всё-таки через PowerPivot, но есть какие-то рекомендации, как потом избежать потери проделанной работы?

[@salosteysv]

При изменении имени запроса, загруженного в Power Pivot, он перезагружается заново. Т.е. таблица удаляется и опять добавляется. При этом все формулы и связи теряются. Т.е., чтобы данные не терялись, не меняйте название таблиц ) Где лучше использовать вычисляемые столбцы... Остается на усмотрение пользователя. При этом нужно учитывать, что если столбец будет в Power Query, то файл будет чуть дольше обновляться. Если в Pivot, то будет больше весить. Если строк мало, то вес будет незначительным, если много, то может сильно прирасти.

[@denysrudniev4572]

Спасибо! Буду принимать решение исходя из этих нюансов!

[@AlekseyNA]

А если строк в таблице 400 000?

[@biweb13]

5+