Power Query Продвинутый №17. Нюансы консолидации из разных книг/листов Excel и List PositionOf
HTML-код
- Опубликовано: 14 авг 2019
- В этом уроке мы узнаем о некоторых нюансах объединения таблиц по вертикали из разных файлов.
Файлы для спонсоров Boosty: boosty.to/comrade-xl/posts/00...
Файлы для спонсоров ВК: comrade.excel?w=wall-1...
Файлы для спонсоров RUclips: • Post
Индивидуальное обучение: comrade-xl.ru/pq-training/
Страница урока на сайте: comrade-xl.ru/2019/08/15/kons...
![MAF Teeski - Whole Thing (feat. VonOff1700) [Official Music Video]](http://i.ytimg.com/vi/SHWEj-ndz6g/mqdefault.jpg)
Файлы для спонсоров Boosty: boosty.to/comrade-xl/posts/004eb73a-1840-4cef-b5a5-1677b1b53285?share=post_link
Файлы для спонсоров ВК: vk.com/comrade.excel?w=wall-185123800_165
Файлы для спонсоров RUclips: ruclips.net/user/postUgyAMGUjVmLXjetETuN4AaABCQ
Индивидуальное обучение: comrade-xl.ru/pq-training/
Страница урока на сайте: comrade-xl.ru/2019/08/15/konsolidacia-i-listpositionof/
Тимур, огромное спасибо за решение проблемы с фиксированным поднятым названием столбца (через относительную адресацию), как же меня она доставала все эти годы! 😅👍
7:13 Тут ещё один способ объединить таблицы в файлах Excel, которые лежат в одной папке, без использования Пользовательской функции
Спасибо, Тимур! А почему в последнем примере для столбца условная логика? Это связано с какой то задачей? Почему не написать просто равно значению?
Там же нужно заполнить вниз.
@@comrade_excel Я имел ввиду просто добавить условный столбец =[Column1]{0} чтобы получить во всем столбце "Bike"
Правильно. Так даже лучше. Можно так. Если можно решить задачу без 'if", то нужно решать без "if". Условия "if" работают дольше, код громоздкий и хуже читается.
19:21 До сих пор не могу понять, почему не if [Column1]{0}
Если использовать файл эксель в двоичном формате, то павер квери тут особо не помощник?
Где-то 10 месяцев назад в PQ появилась возможность подключаться к двоичному Excel (xlsb).
Если у вас не подключается, то выполните обновление.
Тем не менее с xlsb-файлами все равно работает совсем не так как с xlsx. Например, не распознает таблицы, а даты произвольно коверкает непредсказуемым образом.
Скорее всего со временем исправятся все эти недочеты. Как известно, команда PQ выпускает обновления каждый месяц.
Тимур добрый день. У меня так уже четвертое видео подряд, не могу понять в чем проблема:
Formula.Firewall: Запрос "Accessories" (шаг "Источник") ссылается на другие запросы или этапы и поэтому не может напрямую обращаться к источнику данных. Измените эту комбинацию данных.
в "Путь" формула такая "= Excel.CurrentWorkbook(){[Name="Путь"]}[Content]{0}[Column1]"
Вроде все ровно, но что-то не так.....
Добрый день, Максим!
Вам поможет этот урок ruclips.net/video/3_v1O06Mxnc/видео.html
@@comrade_excel Спасибо))
А будут ли ещё крутые уроки по квери?
Будут. Я планирую переделать базовый курс и еще несколько уроков для плейлиста "Разное".
@@comrade_excel кайф, я готов по 1 к в месяц за этот контент платить.
Товарищ, большое спасибо! Подскажите, пожалуйста, очень- очень необходимо (и срочно), чтобы обработка данных была быстрее. Работаю с большим массивом. В csv выгрузить можно, но в моём случае не работает- потому что в некоторых строках есть разделители табуляция, другие разделители тоже не подошли. Форматы выгрузки тоже пробовала разные. Только xlsx подошёл. Знаю, что есть list. Buffer и table.Buffer. Абсолютно не понимаю их синтаксис. Наверное ещё можно как-то в Power Pivot, но им пока тоже не владею. Можно пожалуйста урок про обработку больших массивов? Может даже лучше несколькими способами- кто владеет Power Pivot и для таких, кто не знает. Как лучше в целом работать с большим массивом. Уверена, что многим бы пригодилось. Ещё может отдельный урок про синтаксис формул. Когда круглые, когда квадратные, когда фигурные, когда равно и стрелка и т.д. Я понимаю, что это есть в отдельных уроках по чуть-чуть, но удобно было бы отдельный урок, который бы систематизировал. Ещё раз большое спасибо! Удачи и процветания Вам и Вашему полезному каналу!!!
1. Можно просто заменить табы на другой разделитель, чтобы везде было одинаково. Например, сначала при импорте CSV вообще убрать деление на столбцы, потом заменить табы на что-то другое, а потом уже выполнить деление.
2. List.Buffer и Table.Buffer применяются при многократном обращении к одной переменной. У меня как минимум 7 уроков с использованием этих функций. Вы можете изучить их, чтобы понять когда она применятся
comrade-xl.ru/tag/list-buffer/
comrade-xl.ru/tag/table-buffer/
3. Отдельных уроков на тему скобок (ссылок) у меня полно. Это уроки о языке Power Query. Помимо теоретических есть еще 5 практических уроков на тему ссылок. Вы можете изучить их
ruclips.net/video/MXG_aZUizMk/видео.html
ruclips.net/video/i3W2VwVqnKQ/видео.html
ruclips.net/video/yQl_49kq6uI/видео.html
ruclips.net/video/h1Sr13avZnk/видео.html
ruclips.net/video/wmhNZCz_QmA/видео.html
ruclips.net/video/dPgMIgSzf00/видео.html
ruclips.net/video/Le3f5ruv-e0/видео.html
ruclips.net/video/-ndpNTmU6j4/видео.html
ruclips.net/video/AHWdHStwVLA/видео.html
4. Лично я предлагаю учиться последовательно от Базового курса и дальше. В курсах есть ответы на все ваши вопросы:
Базовый comrade-xl.ru/power-query-basics/
Практический comrade-xl.ru/power-query-practice/
Продвинутый comrade-xl.ru/power-query-advanced/
Работа с ошибками comrade-xl.ru/power-query-errs/
Веб-запросы comrade-xl.ru/power-query-web-queries/
Сейчас создается курс «Погружение в М». Вы уже можете изучать вышедшие уроки из этого курса. Когда я создам оставшиеся уроки, то для «Погружение в М» тоже будет своя страница и плейлист.
@@comrade_excel Товарищ, огромное Вам спасибо за такой развернутый ответ!!! Наверное, я не очень поняла про табуляцию или неправильно выразилась: к примеру на 100 тысяч строках разделитель табуляция нормально разъединяет по столбцам, но есть 200 строк, где табуляцию применили не к месту (например, должны были проставить пробел, а сделали табуляцию) и для этих строк разъединение по столбцам прошло неверно.
@@mandarina2016 там есть какая-то логика этого «не к месту»? Всегда в одном и том же месте эти лишние табы или лишний там всегда один и тот же по порядку, например, под номером 3?
@@comrade_excel в том и проблема, что логики нет. Просто ошибки операторов на вводе. Поэтому работаю с большим массивом (более 300 тысяч строк в формате xlsx), к которому необходимо цеплять другие таблицы (тоже немалые) и делать простые расчеты по ним. Все весит чуть ли не весь день, ничего толком не могу сделать. Я понимаю, что в идеале наверное перевести на Power Pivot, но проблема в том, что там не получится создать связь 1 ко многим. Все соединяемые таблицы не справочники. Идентификаторы тоже не во всех соединяемых таблицах имеют хороший код- приходится соединять по 7 ключам, чтобы ничего не потерялось. Нормальных отчётов у нас нет. Много приходится исправлять руками,а потом уже исправленный вручную вариант кидать на обработку в PQ. Спасибо Вам большое за отзывчивость! Что можно попробовать сделать?
@@mandarina2016 отправьте мне этот CSV файл и такой же XLSX в телеграм @comrade_xl. Сделайте выгрузку не очень большую, но чтобы там присутствовала эта ошибка. Возможно, из этого выйдет интересный урок, если получится решить эту задачу.
Как сделать динамическое удаление ведущих строк и столбцов?
Я не понимаю, что вы имеете в виду.
@@comrade_excel powerpivotpro.com/2018/02/power-query-magic-dynamically-remove-leading-rows-columns/
@@user-gr6fq6fk5j Подобное было в уроке ruclips.net/video/1QTq98vwv9I/видео.html
Здесь используется List.PositionOf.
В одном из будущих уроков в "Модуле 6 - Практика" как раз я покажу функцию Table.FindText, которая используется в примере, что вы прислали.
@@comrade_excel Этот урок уже был или ждём?) Интересно было бы углубиться.
FindText встречается в уроках:
ruclips.net/video/8iRod7ItkJs/видео.html
ruclips.net/video/3yCRGip6eqA/видео.html
ruclips.net/video/h1Sr13avZnk/видео.html
5:08 Минут двадцать тыкался и не одуплял, почему ошибку выдаёт - Excel.Workbook([Content][Data]{0})?
А оказалось, что конструкцию [Data]{0} нужно выносить вне Excel.Workbook([Content]), вот так - Excel.Workbook([Content])[Data]{0},
что логично, так как это Excel.Workbook([Content]) всё целиком - это как бы таблица, а вот [Data]{0} - это уже в какую ячейку в этой таблице надо провалиться