Power Query Практический №6. Консолидация книг и листов одновременно

[@comrade_excel]

Файлы для спонсоров Boosty: boosty.to/comrade-xl/posts/614203fb-a61c-486a-acce-757051b62b13?share=post_link Файлы для спонсоров ВК: vk.com/comrade.excel?w=wall-185123800_112 Файлы для спонсоров KZbin: kzbin.infoUgzjnk3xCjm0Ll2bDhV4AaABCQ Индивидуальное обучение: comrade-xl.ru/pq-training/ Страница урока на сайте: comrade-xl.ru/2020/02/11/power-query-consolidacia-listov-i-failov/

[@skrald_88]

Тимур, Ваш курс запоминается не в последнюю очередь потому, что учит не бояться "работать ручками" в PQ после первичного (интуитивного или не очень) понимания, как ту или иную трансформацию выполнить кнопочками в интерфейсе. По моему скромному опыту, неопытному пользователю легче перейти с кнопочных решений на самостоятельное написание кода на примерах наподобие "как из двух шагов (Table.AddColumn(...) → Table.SelectColumns(...) вручную сделать один с тем же результатом". То есть если кнопками выборка проводилась через дополнительный столбец: ```m ... table_add_col_2 = Table.AddColumn( rows_select, "Пользовательская", each Table.First([Data])[Column1] ), rows_select_2 = Table.SelectRows( table_add_col_2, each ([Пользовательская] = "число") ) ... ``` - то можно и одним обойтись, если перенести критерий отбора из шага 1 в шаг 2: ```m ... rows_select_2 = Table.SelectRows( rows_select, each Table.First([Data])[Column1] = "число" ) ... ``` Или "как из запроса сделать пользовательскую функцию", ну и т.д. Просто делюсь соображениями)

[@senge_v4871]

Спасибо за Ваш труд. Печально когда сотрудники не знают элементарных вещей в Екселе. Ведь это знание облегчает труд и экономит время Жду Ваш подкаст как не надо работать в Екселе. Тому подписчику можно скинуть ссылку лучший сайт по Екселю в русском сегменте Интернета-Планета Ексель.

[@comrade_excel]

Спасибо! Подписчику данные предоставили, я думаю. Вряд ли он их сам заполнял. Почерк разный в разных файлах. В самом последнем файле явно заполнял кто-то более опытный.

[@makmelni]

Это кстати мой файл, я там водителем работаю, и по моим субъективным ощущениям, я там самый опытный пользователь экселя 🙈

[@maximpatrin490]

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

[@comrade_excel]

Загрузите все файлы в облако и ссылку напишите в ответном комментарии.

[@maximpatrin490]

@@comrade_excel Примеры файлов в ограниченном количестве и с измененными данными - sharefiles.app/download/3674969d2b334c020e773f777032821fc4ad871e. Главный файл - head.

[@comrade_excel]

@@maximpatrin490 что должно получиться?

[@maximpatrin490]

@@comrade_excel Вот такая таблица: sharefiles.app/download/e02c2e72ad527bdf8a2b828d784050d9e200c119. Файлов может быть значительно больше. В каких-то столбцы одни, в каких-то есть новые. Все файлы лежат в одной папке.

[@ДмитрийНикитин-и8о]

6:10 Наверное, лучше тогда использовать Table.FirstValue ?

[@ИванПрохоров-ц1ы]

Добрый день! Подскажите пожалуйста, при добавлении контента в редактор, все числовые значения отображаются по модулю Можно ли решить данную проблему, что бы отрицательные значения так же добавлялись корректно?

[@ppr8117]

Автор, присланного Вам файла, видимо, не слышал о правилах организации данных. Это печально :((

[@makmelni]

Юхууу 🥳 круть 😎спасибо🙏💕

[@vlasov786]

Сложно

[@iwonder5132]

Тимур, спасибо за ваш труд. При просмотре возник один вопрос: Почему, когда мы указываем колонку (в [] скобках) для функции Table.First она возвращает нам Record, а не List. Ведь согласно правилам [] скоби дают List, а {} скобки Record. Или я неправильно это понимаю? Заранее спасибо за ваш ответ. Алексей.

[@comrade_excel]

Спасибо, что смотрите! Table.First возвращает объект Record. Record - это строка таблицы. Чтобы из Record получить какое-то значение мы дописываем название столбца в квадратных скобках.

[@contr700]

Здравствуйте. Пытаюсь сделать слияние 2-х таблиц, одна из которых содержит дополнительные 4 столбца с пустыми ячейками. Редактор "не видит" эти столбцы, и развернуть даёт только общие для этих двух таблиц, поэтому удалить я их не могу и после загрузки на лист они отображаются. Подскажите, пожалуйста, как избавиться от этих столбцов.

[@Maga05ize]

Добрый день. Просьба помочь с решением задачи. Есть два файла с множеством листов. Например есть лист «1» в обоих файлах, на листе таблица с одинаковыми заголовками и строками, но содержание (числовые значения) разное. Нужно с помощью Power Query на каждом листе сравнить таблицы.

[@annatsilevitch3409]

ДОбрый день, Тимур. Подскажите, пожалуйста, почему эта же техника не работает , если дела. запрос на папку с файлами html? Столкнулась вообще с проблемой - данные файлы из папки нормально объединяются и дружат, но сами html имеют внутренние подитоги, которые квери воспринимает как отдельные листы. соответственно, мне нужно и объединить листы и папки в папке. Я поставила временный костыль - дублирования запросов и далее мердж. Но это именно костыль. так как я хз, какое еще количество листов (=подитогов) рано или поздно появится. Есть ли какой-то лайфхак, до которого я не догадалась? ПС перевели выгрузку из системы в ПДФ. разбивка на нескольких листах. Проблема не ушла. выдает ошибку - DataFormat.Error: Внешняя таблица не имеет предполагаемый формат (формат пдф или хтмл)

[@comrade_excel]

Добрый день! Вам нужна консолидация с предварительной обработкой. Об этом есть уроки у меня на канале. Обработка в каждом случае индивидуальна. Формат же файла значения не имеет.

[@annatsilevitch3409]

@@comrade_excel не сочтите за наглость, не подскажете, какой номер урока или название?

[@comrade_excel]

@@annatsilevitch3409 Я всем предлагаю проходить уроки с первого и по последний. Мои уроки разбиты на плейлисты Базовый, Практический, Продвинутый. Про объединение с предварительной обработкой есть здесь comrade-xl.ru/2020/05/31/power-query-combine-all-files/

[@anmiss08]

Тимур, спасибо за Ваш труд! Расскажите а можно ли поменять цвет итоговых таблиц запросов power query?

[@comrade_excel]

После того как вы загрузили таблицу на лист можно по ней кликнуть, перейти на вкладку конструктор и выбрать стиль. Также вы можете выбрать тему для объектов Excel. Разметка страницы - Темы. Тема помимо цветовых гамм включает еще шрифты и эффекты. Если шрифты и эффекты трогать не хочется, то можно указать только цветовую гамму. Если вы хотите, чтобы нужная цветовая гамма использовалась по умолчанию, то: 1) Откройте новый файл Excel 2) Настройте в нем цветовую гамму 3) При желании настройте стиль таблиц и сводных таблиц 4) Сохраните этот файл под названием "Книга1" в папку "C:\Users\Name\AppData\Roaming\Microsoft\Excel\XLSTART" Вместо Name нужно вписать имя вашего пользователя. Теперь во всех новых книгах будут по умолчанию использоваться указанные вами настройки.

[@anmiss08]

@@comrade_excel спасибо Тимур, попробую, но конструктор не вариант, настройки по умолчанию работают только к Таблицам, они создаются моим любимым голубым, а вот все Таблицы из запросов всегда зелёным неприятным цветом, приходилось их менять руками, попробую шаблоном.

[@anmiss08]

@@comrade_excel жду Ваш подкаст!!!!

[@anastasyanas]

Большое спасибо! Круто👏В больших структурах это частая проблема.

[@ДенисБахаровский-и5ж]

Помогите пожалуйста. Есть файл весом 25мб, с несколькими таблицами на 10к строк и 20 запросами. Научите оптимизации.

[@comrade_excel]

Вам нужна индивидуальная консультация?

[@симасергеева-и1м]

волшебство!

[@ВячеславИванов-с7ю]

Можно я Вам пришлю "образец" как не надо работать в Excel?

[@comrade_excel]

Конечно! Загрузите в облако и здесь напишите ссылку.