Сборка таблиц из разных файлов Excel с помощью Power Query

Постановка задачи

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

Предположим, что у нас есть вот такая папка, в которой содержится несколько файлов с данными из филиалов-городов:

Количество файлов роли не играет и может меняться в будущем. В каждом файле есть лист с именем Продажи, где расположена таблица с данными:

Количество строк (заказов) в таблицах, само-собой, разное, но набор столбцов везде стандартный.

Задача: собрать данные из всех файлов в одну книгу с последующим автоматическим обновлением при добавлении-удалении файлов-городов или строк в таблицах. По итоговой консолидированной таблице затем можно будет строить любые отчеты, сводные таблицы, фильтровать-сортировать данные и т.д. Главное - суметь собрать.

Подбираем оружие

Для решения нам потребуется последняя версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010-2013 с установленной бесплатной надстройкой Power Query от Microsoft (скачать ее можно здесь). Power Query - это супергибкий и супермощный инструмент для загрузки в Excel данных из внешнего мира с последующей их зачисткой и обработкой. Power Query поддерживает практически все существующие источники данных - от текстовых файлов до SQL и даже Facebook :)

Если у вас нет Excel 2013 или 2016, то дальше можно не читать (шучу). В более древних версиях Excel подобную задачу можно реализовать только программированием макроса на Visual Basic (что весьма непросто для начинающих) или монотонным ручным копированием (что долго и порождает ошибки).

Шаг 1. Импортируем один файл как образец

Для начала давайте импортируем данные из одной книги в качестве примера, чтобы Excel "подхватил идею". Для этого создайте новую пустую книгу и...

• если у вас Excel 2016, то откройте вкладку Данные и выберите Создать запрос - Из файла - Из книги (Data - New Query- From file - From Excel)
• если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на ней Из файла - Из книги (From file - From Excel)

Если нажать в правом нижнем углу этого окна кнопку Загрузить (Load), то таблица будет сразу импортирована на лист в исходном виде. Для одиночного файла - это хорошо, но нам нужно загрузить много таких файлов, поэтому мы пойдем немного другим путем и жмем кнопку Правка (Edit). После этого должен в отдельном окне отобразиться редактор запросов Power Query с нашими данными из книги:

Это очень мощный инструмент, позволяющий "допилить" таблицу под нужный нам вид. Даже поверхностное описание всех его функций заняло бы под сотню страниц, но, если совсем кратко, то с помощью этого окна можно:

• отфильтровывать ненужные данные, пустые строки, строки с ошибками
• сортировать данные по одному или нескольким столбцам
• избавляться от повторов
• делить слипшийся текст по столбцам (по разделителям, количеству символов и т.д.)
• приводить текст в порядок (удалять лишние пробелы, исправлять регистр и т.д.)
• всячески преобразовывать типы данных (превращать числа как текст в нормальные числа и наоборот)
• транспонировать (поворачивать) таблицы и разворачивать двумерные кросс-таблицы в плоские
• добавлять к таблице дополнительные столбцы и использовать в них формулы и функции на встроенном в Power Query языке М.
• ...

Для примера, давайте добавим к нашей таблице столбец с текстовым названием месяца, чтобы потом проще было строить отчеты сводных таблиц. Для этого щелкните правой кнопкой мыши по заголовку столбца Дата и выберите команду Дублировать столбец (Duplicate Column), а затем щелкните правой кнопкой мыши по заголовку появившегося столбца-дубликата и выберите команды Преобразование - Месяц - Название месяца:

Должен образоваться новый столбец с текстовыми названиями месяца для каждой строки. Дважды щелкнув по заголовку столбца, его можно переименовать из Копия Дата в более удобное Месяц, например.

Исключить строки с ошибками или пустые строки, а также ненужных менеджеров или заказчиков можно с помощью простого фильтра:

Причем все выполненные преобразования фиксируются в правой панели, где их всегда можно откатить (крестик) или изменить их параметры (шестеренка):

Легко и изящно, не правда ли?

Шаг 2. Преобразуем наш запрос в функцию

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

В редакторе запросов перейдите на вкладку Просмотр и нажмите кнопку Расширенный редактор (View - Advanced Editor). Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера:

Теперь аккуратно вносим пару правок:

Смысл их прост: первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath, а ниже мы меняем фиксированный путь на значение этой переменной. 

Все. Жмем на Готово и должны увидеть вот это:

Не пугайтесь, что пропали данные - на самом деле все ОК, все так и должно выглядеть :) Мы успешно создали нашу пользовательскую функцию, где запомнился весь алгоритм импорта и обработки данных без привязки к конкретному файлу. Осталось дать ей более понятное имя (например getData) на панели справа в поле Имя и можно жать Главная - Закрыть и загрузить (Home - Close and Load). Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера.. Вы вернетесь в основное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:

Шаг 3. Собираем все файлы

Все самое сложное - позади, осталась приятная и легкая часть. Идем на вкладку Данные - Создать запрос - Из файла - Из папки (Data - New Query - From file - From folder) или, если у вас Excel 2010-2013, аналогично на вкладку Power Query. В появившемся окне указываем папку, где лежат все наши исходные файлы-города и жмем ОК. Следующим шагом должно открыться окно, где будут перечислены все найденные в этой папке (и ее подпапках) файлы Excel и детализация по каждому из них:

Жмем Изменить (Edit) и опять попадаем в знакомое окно редактора запросов.

Теперь нужно добавить к нашей таблице еще один столбец с нашей созданной функцией, которая "вытянет" данные из каждого файла. Для этого идем на вкладку Добавить столбец - Пользовательский столбец (Add Column - Add Custom Column) и в появившемся окне вводим нашу функцию getData, указав для ее в качестве аргумента полный путь к каждому файлу:

После нажатия на ОК созданный столбец должен добавиться к нашей таблице справа.

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

А теперь "вау-момент" - щелкнем мышью по значку со своенным стрелками в правом верхнем углу добавленного столбца с нашей функцией:

... снимаем флажок Использовать исходное имя столбца как префикс (Use original column name as prefix)и жмем ОК. И наша функция подгрузит и обработает данные из каждого файла, следуя записанному алгоритму и собрав все в общую таблицу:

Для полной красоты можно еще убрать расширения .xlsx из первого столбца с именами файлов - стандартной заменой на "ничего" (правой кнопкой мыши по заголовку столбца - Заменить) и переименовать этот столбец в Город. А также подправить формат данных в столбце с датой.

Все! Жмем на Главной - Закрыть и загрузить (Home - Close & Load). Все собранные запросом данные по всем городам будут выгружены на текущий лист Excel в формате "умной таблицы":

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

В будущем, при любых изменениях в папке (добавлении-удалении городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши прямо по таблице или по запросу в правой панели и выбрать команду Обновить (Refresh) - Power Query "пересоберет" все данные заново за несколько секунд.

P.S.

Поправка. После январских обновлений 2017 года Power Query научился собирать Excel'евские книги сам, т.е. не нужно больше делать отдельную функцию - это происходит автоматически. Таким образом второй шаг из этой статьи уже не нужен и весь процесс становится заметно проще:

1. Выбрать Создать запрос - Из файла - Из папки - Выбрать папку - ОК
2. После появления списка файлов нажать Изменить
3. В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла

И все! Песня!

Ссылки по теме

• Редизайн кросс-таблицы в плоскую, подходящую для построения сводных таблиц
• Построение анимированной пузырьковой диаграммы в Power View
• Макрос для сборки листов из разных файлов Excel в один



Страницы: 1  2  3  4  

miho miho

06.02.2017 07:42:48

Очень полезная статья, большое спасибо.

Ссылка

Максим Зеленский

06.02.2017 08:28:36

Николай, отлично, что вы добрались до чудесного PQ!
Важное замечание - для 2010 эта надстройка тоже работает! Установочный файл тот жеще,  что и для 2013

Ссылка

Николай Павлов

06.02.2017 09:09:35

Полез уточнить - точно, с 2010 уже можно ставить. Спасибо, Максим - сейчас поправлю в статье

Родитель Ссылка

okshef

06.02.2017 22:55:59

За исключением Office 2010, установленного в Windows XP

Родитель Ссылка

Сергей Трекин

06.02.2017 08:29:29

Спасибо. Power query есть для Excel начиная с 2010 года.

Ссылка

Николай Павлов

06.02.2017 09:09:57

Спасибо, сейчас подправлю. Владельцы старых версий обрадуются

Родитель Ссылка

Сергей С

07.02.2017 04:24:49

Очень удобная вещь! Спасибо, Николай!
Подскажите пожалуйста, возможно ли подобным образом собирать данные не только из отдельных файлов, но и из папок. Объясню. Коллеги разносят файлы по папкам 01 Январь, 02 Февраль, и т.д. При этом важно чтобы данные обновлялись
Спасибо.

Ссылка

Николай Павлов

07.02.2017 10:05:33

Сергей, все совершенно так же. Указываете для сбора верхнюю папку в иерархии и Power Query соберет все файлы из всех подпапок автоматически.

Родитель Ссылка

Сергей С

08.02.2017 07:10:08

Николай, спасибо большое за оперативный ответ! Действительно все работает. В процессе применения данной волшебной функции возник вопрос: при сборе данных возможно ли каким-нибудь образом установить исключение повторов и еще -  из одинаковых строк выбрать строку с максимальным значением определенного критерия?
Спасибо.

Родитель Ссылка

Nego Sergey

07.02.2017 11:30:41

Надстройки Power (Query / Pivot / View) для Excel это хорошие инструменты уровня Self-Service BI, которые продвинутый аналитик может эффективно использовать в своей работе.8)

Николай, спасибо за отличный пример – использования функции для обработки файла, при загрузки из папки! Буду ждать видео к данной статье!

Если интересно, в качестве альтернативы данную задачу можно решить без функции, на эту тему рекомендую посмотреть видео от коллеги по вашему цеху: https://www.youtube.com/watch?v=tODq6yRVZl8

Еще, как правило, есть проблема в Power Query, что путь к файлам/папкам фиксируется в запросе, но это тоже решается – можно создать динамический путь примерно так: = Excel.CurrentWorkbook(){[Name="tPath"]}[Content]{0}[Path]

Если нужно будет кому-то, вот одно из видео на эту тему: https://www.youtube.com/watch?v=kRB6O2qxsIc

Вообще возможности ExcelЯ c надстройкой Power Query вдохновляют!

Например: долгожданные SQL JOIN-ы таблиц теперь можно делать с помощью Power Query. В ближайшее время планирую сделать статью и видео на эту тему и выложить себе в блог: http://biweb.ru/blog

Спасибо!

Ссылка

Nego Sergey

20.04.2018 09:25:21

Кстати уже давно написал: Операторы слияния таблиц SQL JOIN в Excel | Power Query

Родитель Ссылка

mariner14

07.02.2017 17:18:02

Я работаю в Excel  13 у меня все получилось за исключением наз Месяцев данная вкладка не вылазиет нет её.
А так все очень круто запросы действиетльно очень полезные.

Ссылка

Kami

08.02.2017 09:00:49

Здравствуйте,
подскажите, пожалуйста, а как в шапке отобразить значки типа данных ('ABC', календарик, 'ABC 123') ?

Ссылка

Николай Павлов

08.02.2017 09:02:23

А у вас какая версия Excel?

Родитель Ссылка

Kami

08.02.2017 09:12:17

Excel 2016

Родитель Ссылка

Дмитрий Оралов

08.02.2017 18:39:22

Здравствуйте!
1. Николай, у вас в тексте написано: "первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath..", а на видео  вместо filepath указано filename.
2. Exel 2016 и тоже не показывает как у вас в шапке значки типа данных ('ABC', календарик, 'ABC 123')
3. а если такую функцию внедрить в Plex, на подобие плюшки в Plex "Сборка листов"?

Ссылка

Сергей С

09.02.2017 08:58:42

Николай, помогите пожалуйста. выпадает вот такое сообщение
[DataFormat.Eror] Не удалось выполнить синтаксический анализ входных данных, представленных в качестве значения даты.
подскажите пожалуйста как устранить данную проблему.

Ссылка

Дмитрий Плотников

07.02.2019 17:26:36

такой же вопрос

Родитель Ссылка

Эльбрус

09.02.2017 21:47:57

Николай, а где книга Мастер формул?

Ссылка

Николай Павлов

18.02.2017 08:44:26

Выложил в раздел КНИГИ, можно брать

Родитель Ссылка

Эльбрус

18.02.2017 11:22:33

Спасибо за ответ. Купил уже, дня три как. Я такой радый!)

Родитель Ссылка

Роман Емельянов

10.02.2017 10:31:14

Ура! Теперь надо обязательно ставить обновление от Января 2017. Вот что пишут:

Enhanced “Combine Binaries” experience when importing from any folder

Before this release, users could combine Text or CSV files only. The combine would not work for any other supported file formats (such as Excel Workbooks, JSON files, etc.), and it would not account for transformations required on each file before combining them into a single table (such as removing the first row with header values).

With this release, we enhanced the “Combine Binaries” experience when importing from any folder so that:

• Excel analyzes the input files from the Folder query and detects the right file format to use (i.e., Text or Excel Workbook).
• Users can select a specific object from the list (such as a spreadsheet name) to use for data combine.
• Excel automatically creates the following entities
  

https://blogs.office.com/2017/02/08/january-2017-updates-for-get-transform-in-excel-2016-and-the-power-query-add-in/

Теперь вроде как можно радоваться и хлопать в ладоши, т.к. теперь не нужно извращаться с изменением кода вручную.

Однако (на моей работе) при выгрузке файлов из 1С в формате .xlsx, Power Query всё равно не может их проглотить, пока просто не пересохранишь файл в самом Excel (открыть файл, сохранить, закрыть).

Ссылка

Николай Павлов

10.02.2017 12:12:36

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

Спасибо, Василий! You made my day!

Родитель Ссылка

Sergii Skrypai

17.02.2017 21:18:26

Простите за вопрос. А что принципиально поменялось (улучшилось) после этого обновления?
как теперь лучше решать эту задачу?

Родитель Ссылка

Николай Павлов

18.02.2017 08:43:20

Не нужно корректировать запрос на языке М, чтобы превратить его в функцию. Т.е. Шаги 1 и 2 не нужны, можно сразу с шага 3 начинать - и все ОК.

Родитель Ссылка

Алексей Семилетов

22.02.2017 17:25:24

Огромное спасибо за статью, как раз тему осваиваю.
А можно в студию версию сборки Excel 2016 после установки январского обновления?

Родитель Ссылка

Николай Павлов

23.02.2017 00:15:22

В смысле? Перезаписать видео?

Родитель Ссылка

Алексей Семилетов

27.02.2017 10:22:59

Нет, просто, написать в сообщении какая у вас теперь версия Excel стала после установки январского обновления в разделе "О программе". У меня сейчас установлена версия офиса 16.0.4266.1001. Мне это нужно для описания АйТишникам на работе, до какой версии мне надо офис обновить.

Родитель Ссылка

Wlad

10.02.2017 15:54:16

Я это делаю с помощью надстройки Plex:
1.Сборка листов
2.Собрать на итоговый лист
Остается только через фильтр удалить заголовки...

Ссылка

Николай Павлов

12.02.2017 10:14:11

Это да Но PLEX:

• будет собирать дольше
• не умеет обновлять собранные данные (нужно будет пересобирать)
• не умеет запоминать цепочку действий по причесыванию данных
  

Так что Power Query в этом вопросе однозначно круче.

Зато PLEX умеет разбирать одну таблицу по разным листам, а PQ - пока нет

Родитель Ссылка

pinguindell

08.03.2017 11:43:08

Николай, добрый день

Подскажите а как быть когда при добавлении в папку нового файла с данными, при обновлении запроса Query задваиват данные

Родитель Ссылка

Олег Клёпов

12.02.2017 01:44:42

Николай, доброго времени суток. Прошу уточнить: на видео при внесении поправок в расширенном редакторе Вы вписываете filename, а в описании данного примера -filepath. Это не ошибка? Спасибо

Ссылка

Николай Павлов

12.02.2017 10:11:45

Олег, это просто имя переменной - оно может быть абсолютно любым, хоть ABCD. Главное, чтобы в первой строке и в аргументе File.Contents они совпадали.

Родитель Ссылка

Дмитрий Андреев

14.02.2017 17:04:28

Николай, добрый день. Не подскажете - имеется ли где информация/инструкция как подключаться к Базам данных. Например если 1с базируется на SQL Servere. Я тестово пробовал с IT специалистом провести подключение, однако инструментарий PQ почему то не принимал учётные данные, пробовали обойти - выдаёт ошибку подключения.

Ссылка

Дмитрий Оралов

19.02.2017 10:58:58

Николай, можно сделать гораздо проще, без запроса.
Таблица

Ссылка

Николай Павлов

19.02.2017 12:24:33

Так стало возможно только после январского-2017 обновления Power Query - почитайте комменты выше, уже писали про это

Родитель Ссылка

Дмитрий Оралов

19.02.2017 15:18:02

этот прием я нашел на видеоот 04.06.2016 (с 30:47), при чем тут январское обновление

Родитель Ссылка

Николай Павлов

19.02.2017 21:12:44

Кнопка "Развернуть" на столбце с Binary раньше работала только для импорта CSV файлов, не XLSX. Разворачивать-подставлять содержимое Excel-книг с выбором конкретного листа стало возможным только после январских обновлений Power Query - гляньте их официальный блог.

Родитель Ссылка

Дмитрий Павлов

26.02.2017 20:29:02

Николай, добрый день.
Случайно не подскажете, как с помощью vba кода потом удалить все запросы из полученной книги?
Макрорекордер не чирикает...

Ссылка

Дмитрий Павлов

26.02.2017 21:08:53

Уже нашел, спасибо.
Вот такой код:

Private Sub DeleteQuery() Dim wSh As Worksheet Dim lo As ListObject     On Error Resume Next     ActiveWorkbook.Queries("Test").Delete     For Each wSh In ActiveWorkbook.Worksheets         For Each lo In wSh.ListObjects             If lo.Name = "Test" Then                 Application.DisplayAlerts = False                 wSh.Delete                 'wSh.ListObjects("Test").Delete             End If         Next lo     Next wSh     On Error GoTo 0      End Sub

Имя запроса можно легко посмотреть и вставить в код.

Родитель Ссылка

Анастасия Гура

01.03.2017 14:44:50

 Подскажите пожалуйста, сделала все по инструкции -  однако не загружаются на самом последнем этапе все данные и выходит вот такая ошибка: [Dataformat Error] Внешняя таблица не имеет предполагаемый формат

Во всех файлах дали одинаковое наименование листу для выгрузки. Что-то все ранво не так.

заранее огромное спасибо за помощь

Ссылка

Николай Павлов

09.03.2017 08:50:34

Не видя файлов, сказать невозможно. Совет один - проверяйте файлы. Может быть столбцы по-разному называются или перставлены, возможно есть скрытые столбцы или строки.

Родитель Ссылка

Марина Молчанова

07.03.2017 21:56:35

Я правильно понимаю,  листы должны иметь одно имя? А что делать если в папке таблицы одинаковые, а листы с разными именами?

Ссылка

Николай Павлов

09.03.2017 08:49:10

А как Power Query поймет какой именно лист вам нужен? Телепатически?

Родитель Ссылка

Эльдар

14.09.2018 19:23:29

Вопрос актуален
Есть какой-то способ обращения к листу не по его названию, а по номеру? Как в VBA.

Родитель Ссылка

Эльдар

17.09.2018 23:10:06

Решение найдено.
Имя листа можно передавать вторым параметром
(filepath, shname) =>
Предварительно в этой же таблице с путями, отдельной колонкой можно завести имена листов.

Родитель Ссылка

Елена Рудакова

06.02.2020 13:13:16

Добрый день!
Подскажите, пожалуйста, как это сделать (я имею в виду отдельной колонкой завести имена листов).
В Query нет функции "номер листа".

Дело в том, в моей тысяче файлов порядковый номер листов, с которых нужно собирать данные, всегда одинаковый, но имена листов различаются.

Родитель Ссылка

Nik

25.06.2022 21:42:21

https://www.planetaexcel.ru/techniques/24/2152/#11903

Уже скорее всего не актуально, но мало ли:)

Родитель Ссылка

Сергей С

29.11.2021 07:40:21

Добрый день, Эльдар. Подскажите пожалуйста каким образом Вам удалось собрать листы с разным именем? Что значит "Имя листа передавать вторым параметром"?
Спасибо.

Родитель Ссылка

Nik

25.06.2022 21:40:29

https://www.planetaexcel.ru/techniques/24/2152/#11903

Родитель Ссылка

Алексей Семилетов

08.03.2017 17:37:08

Кстати, заметил такое, что в Power Query не выходит заранее задать взаимосвязи между кучей таблиц и надергать в один запрос данные из десятка взаимосвязанных источников, как я привык это делать в Access. Учитывая что я только начал, может я куда-то не туда смотрю и такой функционал имеется?

Ссылка

Сергей Трекин

23.03.2017 10:09:47

Все ваши таблицы добавляете через PQ в модель данных и в power Pivot  через представление данных в виде диаграммы связываете таблицы добавленные через PQ  как надо. Потом можно построить сводную таблицу. Конкретнее смотрите тут и тут. Удачи.

Родитель Ссылка

Алексей Семилетов

03.04.2017 14:40:25

Спасибо!

Родитель Ссылка

a0512

27.03.2017 16:10:12

1. в самом начале правки, где добавляем столбец с месяцем, можно ли прописать формулу в новом столбце для обработки данных в другом? не могу найти такого функционала. есть вызов настраиваемой функции, но форма не дает мне выбрать функцию
2. можно ли настроить автоматическое обновление сборной таблицы? или каждый раз при добавлении файлов нужно обновлять вручную?

Ссылка

Николай Павлов

27.03.2017 17:19:21

1. Можно добавить вычисляемый столбец с формулой
2. Насколько я знаю, да - надо руками обновлять.

Родитель Ссылка

a0512

27.03.2017 17:45:35

у меня только
-пользовательский столбец
-вызвать настаиваемую функцию
-условный столбец
-столбец индекса

мне нужна функция поиска, нигде из этих столбцов я не могу ее описать
вычисляемого столбца не нашел

Родитель Ссылка

Сергей С

29.03.2017 10:19:40

Николай, добрый день! Подскажите пожалуйста как добавить столбец и внести во все его строки значение из единственной ячейки в заголовке таблицы (своего рода абсолютная ссылка)?
Спасибо.

Ссылка

Anvar Akhmadov

03.04.2017 09:02:41

Добрый день! Скажите, пожалуйста, есть ли возможность сослаться на несколько листов одного файла одновременно?
Спасибо!

Ссылка

Анатолий Одегов

09.04.2017 17:35:58

Павел, доброе время суток!!! Одни ваши видео уроки мне позволили подняться на несколько голов выше тех, кто получает деньги за работу с Excel,  при этом не умеют работать с ним и вообще не представляют, что Excel  это не тетрадка в клетку и без полей, а нечто большее!!!
Спасибо большое!!!
У меня Excel 2016
Я работаю со штрихкодами (12цифр), для удобства я ставлю формат Дополнительный(табельный номер), отчёты мне приходят ежедневно и хочу собрать их в один файл с помощью Power Query.
И возникает проблема.
Первый  столбец – вводятся данные с помощью сканера штрихкодов и они переносятся корректно.  
Второй Столбец образуется из первого по формуле «ВПР»  и «Еслиошибка» (появляется значение «продан»).
Вот с ним и возникает проблема, данные переносятся не корректно, а именно округляются :
122040897832 - 122040897830
118060007467 - 118060007470
155050192073 - 155050192070

И так далее…

И вопрос №2,
Power Query – делает свою «умную таблицу», а я хотел бы что бы он переносил данные в мою «умную таблицу» где уже прописаны формулы в дополнительных столбцах  и идёт сразу же обработка этих данных.

Помогите пожалуйста!!!

Ссылка

Анатолий Одегов

11.04.2017 21:49:04

Сделал в этих же файлах, но с маленьким объёмом данных - получилось всё чётко!!! где-то лажа с большими файлами, завтра буду снова их полдня "мурыжить" !!! ))) думаю всё получится!!! )))

Родитель Ссылка

Николай Павлов

22.04.2017 10:27:05

1. Попробуйте текстовый формат
2. Или писать ваши формулы рядом с умной таблицей PQ или вытаскивать данные из таблицы-результата в вашу функциями типа ВПР (VLOOKUP).

Родитель Ссылка

Anvar Akhmadov

18.04.2017 06:23:46

Доброго времени суток! Скажите, пожалуйста, можно ли использовать несколько источников данных для одной книги? Например, "связать" несколько листов одной книги с различными базами данных.

Ссылка

Николай Павлов

22.04.2017 10:24:28

Да, конечно. Сделайте несколько запросов в одной книге - без проблем.

Родитель Ссылка

Lenochka

19.04.2017 19:26:47

Добрый день! Работает ли PQ, если некоторые столбцы в каком-нибудь из файлов скрыты? сгруппированы?

Ссылка

Николай Павлов

22.04.2017 10:23:52

Скрытие не помешает, а вот группировка - это проблема. Особенно - в шапке таблицы.

Родитель Ссылка

Roma Hatsko

28.04.2017 09:56:56

Подскажите пожалуйста. У меня Excel 2010. Установил надстройку PQ. При выполнении шага 1. в момент импорта первого файла в качестве образца все зависает и висит пока не убьешь процесс...  в чем может быть дело?
сразу оговорюсь, что таблица сложная много сгруппированных строк и столбцов в шапке, также есть формулы сумм в некоторых столбцах. И количество строк порядка 1500 шт.

Ссылка

Александр Удовенко

16.05.2017 11:34:14

Николай, добрый день!
Если есть время помогите или направьте в нужном направлении. Есть файл банковских операций до 01.05. и файлы обновлений. Слияние на один лист, к примеру "Альфа", исходного файла и обновлений из папки я настроил. Теперь пробую сделать так чтобы информация на листе "Альфа" сохранялась даже при удалении выписок, т.е. чтобы конечный лист помнил свое текущее состояние и обновлялся только в случая поступления новых выписок. Power Query такое конечно позволяет делать результат мне выгружает в новый лист, так как выгрузка в текущую таблицу данных запрещена.
Спасибо!

Ссылка

Иван Куликов

25.05.2017 15:10:49

Николай, добрый день.
Подскажите, как можно сделать слияние, если во всех файлах отличается имя листа (каждый раз генерится новое имя) а структура данных абсолютно одинакова. Я так понимаю нужно делать 2 запроса и превращать их в функцию: 1-й для файла, 2-й для имени книги и подставлять их в итоговый большой запрос?

Ссылка

Николай Павлов

13.11.2017 09:30:37

В двух словах не объяснишь.
Но идея в том, что надо сначала собрать сами файлы в Power Query, а потом применить к ним функцию =Excel.Workbook(), чтобы вытащить из каждого весь список листов (независимо от их имен).
А потом уже разворачиваем содержимое листов и работает с данными.
Как-то так, если на пальцах.

Родитель Ссылка

Nik

25.06.2022 21:35:25

Вот и мне пришлось столкнуться с той же проблемой и судя по запросам у многих эта проблема есть - как собирать из папки если название листов разное. Гугление привело к достаточно простому решению.

На примере кода из статьи, на втором шаге:

Продажи_Sheet = Источник{[Item="Продажи",Kind="Sheet"]}[Data],

Меняем на -

Продажи_Sheet = Источник{0}[Data],

В итоге, перебирая файлы из папки, Ексель не ищет лист и таблицу на нем по названию, а просто забирает первый лист и первую таблицу на нем. Если вместо 0 поставить 1, то соответственно второй и так далее.

Если собирали с помощью функции "из папки", то после создания, перейдите в редактор ПоверКвери, на шаг "Преобразовать файл" и в коде исправьте на:

Лист1_Sheet = Источник{0}[Data],

Николай, если вас не затруднит, возможно стоит добавить этот метод в статью. Спасибо.

Родитель Ссылка

Юлия Мизякина

01.06.2017 11:47:55

Добрый день! Подскажите пожалуйста, у меня возникла проблема после того как добавила пользовательский столбец. У меня пишет в строчках этого столба Error: Не удается преобразовать значение Binary в тип Function.
Подскажите что неправильно сделала?
Excel 2013

Ссылка

Михаил Сафонов

07.06.2017 13:15:10

https://www.youtube.com/watch?v=1yXhDmjmj7k&list=PL84ESW5WdEfJkFpuhLHO_uAvX0-h3QxB1
Здесь много вариантов применения PQ. Все очень подробно.

Ссылка

Konstantin !

12.06.2017 15:04:52

Доброго дня!
Прошу помощи...при добавлении пользовательского столбца, прописав формулу, не активируется кнопка "Ок" и указывается что "Определение формулы не завершено". Отчеты скачал из данного примера и пробовал другие.
Пробовал и формулу "=Excel.Workbook([Content])" тоже самое... не активируется "ок" и "определение формулы не завершено".
Почему? Что не так?(((((
Excel 2013 и 2010...

Ссылка

Николай Павлов

13.11.2017 09:27:16

Явно проблемы с самим Power Query. Я бы накатил все обновления на Excel и переустановил Power Query для начала.

Родитель Ссылка

Sergii Skrypai

02.08.2017 00:24:53

Как можно добиться названия месяца на языке отличающемся от системного?
Например названия месяцев на русском и английском

Ссылка

Марина Кривошеина

25.10.2017 10:24:46

Добрый день, Sergii!

Я бы сделала дубликат столбца, и заменила бы Январь на January, Февраль на February и т.д. итого 12 раз

Родитель Ссылка

Николай Павлов

13.11.2017 09:26:10

Как вариант, можно сделать таблицу-справочник по датам, где в первом столбце будут даты на русском, а во втором на английском. И затем объединить запросы через команду Главная - Объедиить запросы.

Родитель Ссылка

Марина Кривошеина

25.10.2017 10:02:35

Добрый день!

Спасибо за статью, у меня все получилось.
Единственное - я бы хотела чтобы Power Query вставил данные начиная с определенной строки, например, начиная с 7-ой строки. Как это реализовать? Подскажите, пожалуйста!

Ссылка

Николай Павлов

13.11.2017 09:24:20

Тогда при выгрузке из Power Query в Excel лучше воспользоваться командой Главная - Сохранить и загрузить - Сохранить и загрузить как.
Дальше будет окно, где можно выбрать на какой лист выгрузить и начиная с какой конкретно ячейки.

Родитель Ссылка

ПАлена ПАлена

29.04.2019 20:31:46

Здравствуйте, Николай. А можно поподробнее? Задача собрать таблицы без "шапок", шапки одинаковые, на определенное количество строк. Таблицы нужно объединить, начиная с определенной строки. Это возможно?

Родитель Ссылка

Нишон Ниёзов

13.11.2017 07:55:28

Добрый день, Николай!
Я добавляю папку. В нем файлы и в них много листов. Как указать конкретный лист? Не осилил, прошу Ваш подсказать

Ссылка

Николай Павлов

13.11.2017 09:23:02

Никита, на Шаге 1 на первом же скриншоте выбирается лист Продажи - это и есть выбор листа, который будет импортироваться из всех книг.

Родитель Ссылка

Нишон Ниёзов

16.11.2017 10:40:10

спасибо, получилось.

Родитель Ссылка

Александр Корнеев

23.11.2017 22:35:18

Николай, добрый день! Классный урок.
Как можно обойти ошибку доступа к файлам EXCEL, при обновлении запроса, если один или несколько файлов открыты и на них нет группового доступа, а также закрывать файла нельзя. Может быть можно дописать в функцию команду как для обработки run-time ошибок в VB?.

Ссылка

Николай Павлов

08.12.2017 10:47:53

Погуглите конструкцию try ... catch языка М - возможно, это то, что вам нужно.

Родитель Ссылка

Inna

07.12.2017 08:41:02

Добрый день!
Спасибо за статью. Николай, подскажите, а с группировкой строк в исходных файлах совсем ничего не поделать? У меня выгрузки из базы данных сгруппированы по строкам и файлов достаточно много. Приходится заходить  в каждый и разгруппировывать..

Ссылка

Николай Павлов

08.12.2017 10:45:30

Инна, Power Query при импорте группировку, скрытые и отфильтрованные строки не должен видеть - он берет все содержимое листа как есть. Так что разгруппировывать, по идее, не надо.

Родитель Ссылка

Inna

09.12.2017 15:38:54

Да, действительно. Оказалось, что помешала группировка листов(!), а не строк в таблице

Родитель Ссылка

Антон Кутькин

23.04.2020 12:26:13

Добрый день! Похожая проблема - в книге эксель выгружаемой из 1с скрыты первые 7 строк - и запрос выдает ошибку данных [Binary]. Не знаю что делать

Родитель Ссылка

Иван Чуванов

11.12.2017 12:45:42

У меня есть 2 вопроса буду рад если ответите.

1) А если файлы в разных папках можно как нибудь их собрать одним запросом используя относительный путь к папке типа - = Excel.CurrentWorkbook(){[Name="_001_path_spiski"]}[Content]{0}[ Path]
? Или только каждую таблицу вытаскивать на отдельный лист а потом их все  обьеденять?

2)
В готовую таблицу на основе запроса power query нужно добавить новые столбцы которые должны забиваться вручную!!!!
Важно в исходной таблице из которой брал данные запрос power query этих столбцов нет и быть не должно.
Осуществимо ли это?

Ссылка

Эльдар

17.01.2018 23:15:24

Добрый день!

1) Если по файлам >1млн строк, то загрузить их все можно в PowerPivot только? Можно ли раскидать по листам Excel не фильтруя-разделяя на 2 и более запроса/-ов внутри PQ.
2) Можно ли через PQ получать количество строк по каждому из файлов? Есть список файлов, напротив каждого получить количество строк по содержимому.

Ссылка

Михаил Ари

28.02.2018 10:15:21

Добрый день! Подскажите пожалуйста, у меня такая задача:
есть куча однотипных файлов Эксель, в них есть слово итого и есть сумма. Нужно посчитать общую сумму всех этих "итого"
есть ещё поле дата (которая везде указана в разных форматах) вот очень желательно чтобы сумма считала за конкретный месяц. Я прочёл описание по Power Query, но не смог его установить. Установщик выдает следующее сообщение:
"Для Microsoft Power Query для Excel (x64) требуется 64-разрядная версия Microsoft Excel 2010 с пакетом обновления 1 (SP1) или Microsoft Excel 2013. Сначала установите 64-разрядную версию Microsoft Excel 2010 с пакетом обновления 1 (SP 1) или Microsoft Excel 2013."
У меня следующие ТТХ:
Windows 10 PRO
Exel 2016  в списке надстроек я не нашёл Power Query.

Буду благодарен за ответ!

Ссылка

Сергей Трекин

01.12.2021 09:57:00

В Версии Excel 2016  Power Query  устанавливать не нужно.  Он идет "из коробки". Смотреть на вкладке Данные-Скачать и преобразовать или вкладка Данные-Получить и преобразовать Далее как на видео в начале статьи. Успехов в освоение удивительного и могучего инструмента.

Родитель Ссылка

Александр Окулов

04.04.2018 08:03:58

Если у Вас нет возможности использовать PowerQuery, можете использовать бесплатную надстройкуMerge and Totals

Ссылка

Виталий Лаврушин

08.04.2018 08:48:09

Николай, подскажите:
1. Собирая данные из отдельных Книг в одной папке на сетевом диске - будет-ли PQ работать, если один или несколько исходных файлов открыты на удалённых компьютерах, при условии, что после каждого внесения изменения будут сохранять внесенную информацию.
2. Корректно ли работает PQ во всём диапазоне EXCEL? При каждом обновлении информации PQ проверяет все таблицы или в ней есть алгоритм, который не рассматривает старые значения, а вносит лишь новые?
Спасибо!

Ссылка

Щиенко Владимир

20.04.2018 07:57:55

Николай, здравствуйте!
У меня в одном столбце данные в виде дат и текста, при сборке таблиц из нескольких файлов PQ уделяет данные из этого столбца. Подскажите как это исправить.

Ссылка

Василий Афоничев

23.04.2018 12:45:17

Здравствуйте!
Николай, подскажите пожалуйста, а как можно победить ограничение в 1 000 строк в фильтре столбца в PQ? Если это конечно не секрет?

Ссылка

Василий Афоничев

24.04.2018 10:14:01

спасибо

Ссылка

Василий Афоничев

24.04.2018 10:15:42

спасибо

Ссылка

Дмитрий

15.06.2018 08:57:58

Кто-нибудь знает как обновить Power Query 2016, пишет что у меня не последняя версия...э

Ссылка

Алексей Шебаниц

24.06.2018 19:34:29

Спс, мне очень помогло

Ссылка

Георгий Деревянко

26.06.2018 15:44:17

Использовал успешно PQ для преобразования таблиц и столкнулся с задачей склеить несколько файлов в один. Следуя этому алгоритму на 3 этапе загружается корректно только файл, загруженный на 1 этапе. Ошибка: Expression.Error: The key didn't match any rows in the table

Не подскажете, что можно сделать?

Ссылка

Екатерина

09.07.2018 07:32:12

Добрый день, Николай!
Возможно ли изменить уже записанную функцию? Или надо все заново делать?
Поняла, что удалила некоторые колонки, которые в целом могут понадобиться в будущем для сводной таблицы.

Ссылка

Екатерина

09.07.2018 11:17:05

Уже нашла

Родитель Ссылка

Владислав Калтахчян

19.07.2018 12:40:20

Добрый день, Николай!
После склейки столбцов с путем и именем в новом столбце для всех книг кроме одной (той с которой на первом этапе делали настройки) вместо "table" пишет ошибку "error"
В запросе "" произошла ошибка. Expression.Error: Ключу не соответствует ни одна строка в таблице.
Сведения:
   Key=Record
   Table=Table.
В чем может быть причина?

Ссылка

Николай Павлов

20.07.2018 10:59:02

Не видя ваших исходных файлов и запроса - сказать невозможно. Миллион причин разных.

Родитель Ссылка

Екатерина

26.07.2018 07:53:50

Добрый день,

Настроила power query для нескольких файлов. Доступ в папки и файлы открыт для всех сотрудников. Но папки и файлы хранятся не на общем сервере, доступ открыт через dropbox. Соответственно, адрес линка у каждого сотрудника отличается.
C:\Users\имя сотрудника\Dropbox (название компании)\название папки\сам файл

Все прекрасно работает, НО только с моего компьютера. Пока я не внесу обновления, ни один сотрудник не увидит обновления и также не может вносить никакие данные, тк они все равно не попадут в сводный файл, тк линки с других компьютеров не работают. Это и понятно, тк адрес ссылки отличается по имени. К тому же постоянно выскакивает уведомление, что линки не найдены.

Подскажите, как быть, наверное, можно как-то настроить  и изменить адрес source, чтобы другие коллеги могли не только видеть последние обновления, но и также работать с файлами и вносить изменения.

Заранее благодарю

Екатерина

Ссылка

Евгений Серков

05.09.2018 21:43:16

Добрый вечер. Делаю все по инструкции, запрос создал, когда хочу подгрузить с источника возникает ошибка. Ссылка скриншота https://dropmefiles.com/Evnuj

Ссылка

Юрий Николаев

19.09.2018 14:06:57

Ну, вот и подошёл я к пределу возможностей Power Query : всего-то ему нужно 3 (три!) файла собрать в одну таблицу, а он мне выдаёт OutOfMemory .

Да и объёмы-то несильно большие этих файлов: один 49,9, другой 36,6, а третий 47,4 мега всего... Вот пытаюсь их Повером этим собрать, а он "падает"... Да и таблички там несильно здоровые: 341 столбец на максимум 64300 строк.
Придётся отдельно с каждым файлом возиться

Ссылка

Александр Якимов

12.12.2018 09:03:18

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

Ссылка

алексей чернов

17.12.2018 16:25:11

посмотрите https/www.youtube.com/watch?v=e5l2NSdl_Z8

Родитель Ссылка

алексей чернов

17.12.2018 15:14:30

Все бы хорошо с консолидацией из папки, огорчает, что при переименовании Source.Name например, при использовании в качестве заголовков первой строки, в шапку переходит название файла. Если этот файл из папки удалить или добавить файл с именем, который по алфавиту следует ранее чем первый, получаем ошибку...Запрос не находит имя файла и все - крах консолидации!

Ссылка

Арт М

19.01.2019 20:07:51

Здравствуйте Уважаемый Николай. Подскажите а можно ли примерно таким же образом менять заранее подготовленные веб-запросы ? вот пример:
let
   Источник = Json.
  in
   #"Измененный тип"

можно ли как то в полуавтоматическом режиме вводить данные в поле веб запроса, чтобы каждый раз не менять?

Ссылка

Николай Павлов

18.02.2019 20:55:13

Посмотрите, плиз, статью про параметризацию путей в Power Query - то же самое можно и с веб-запросами делать.

Родитель Ссылка

alex po

20.01.2019 15:55:51

Использование функции и обновление.
Не зависимо от обновления PQ позволяет собрать данные из книги или из папки.
А если в книгах подлежащих консолидации может быть разное количество листов?
Для приведенного примера: Продажи, Продажи2011, Продажи2012 ... и т. д
Запросом к книге собираю все листы имеющиеся в книге (путем незначительной корректировки запроса к одному листу, что описано например в ютубе).
Затем по приведенной методике делаю функцию, которая собирает все листы в книге.
Затем запросом к папке с подстановкой функции собираю все книги в папке.
Результат - сборка всех книг из папки и всех листов из этих книг.
Исходные листы д.б. таблицами, иначе при консолидации диапазонов надо будет как-то удалять лишние строки с заголовками.

Ссылка

Natalia Nikolsakya

30.01.2019 11:28:54

Для чего проставляется флажек add this data to the data mode привыгрузке данных, собранных в Power Query, в таблицу?
Потом из этой таблицы строится Pivot. Может да данном этапе надо проставлять флажек add this data to the data mode?

Ссылка

Николай Павлов

18.02.2019 20:51:50

Этот флажок нужен для загрузки результатов запроса в надстройку Power Pivot (а не в обычную сводную таблицу).

Родитель Ссылка

Александр Назаров

11.02.2019 10:32:03

Добрый день!

Подскажите, пожалуйста, возможно ли после загрузки запроса, сделать дополнительный столбец для ручного заполнения, чтобы при обновлении запроса, введенная информация была привязана к своей строке?

Ссылка

Николай Павлов

18.02.2019 20:53:28

Самый простой вариант - подтянуть нужные данные к результатам выгрузки той же функцией ВПР (VLOOKUP).

Родитель Ссылка

Юрий Николаев

19.02.2019 10:01:27

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

Ссылка

Николай Павлов

27.02.2019 10:59:02

Юрий, попробуйте щелкнуть правой по таблице с результатами запроса на листе - Таблица - Свойства внешних данных и поиграть с галочками в группе отвечающей за форматирование.

Родитель Ссылка

Юрий Николаев

05.03.2019 16:55:19

Браво!
Бис!
А ларчик просто открывался. Когда знаешь, тогда всё легко
СПАСИБО!!!

Родитель Ссылка

Алекс Иваноф

26.02.2019 10:54:48

А чего мой комент удаляют?

Это полезный материал, но я просил помочь с проблемой=>

В запросе "" произошла ошибка. Expression.Error: Ключу не соответствует ни одна строка в таблице.
Сведения:
   Key=Record
   Table=Table

Хотя все делал по написанному  

Ссылка

Николай Павлов

27.02.2019 10:52:53

Удаляю, потому что в нём нет смысла. Если бы делали "по написанному", то все бы работало. Если не работает - значит что-то делаете не так, логично? Но понять, что именно вы делаете неправильно из вашего комментария или приведенной ошибки нереально.
Хотите качественной помощи - пришлите мне ваш файл с ошибочным запросом на почту или спросите на форуме (приложив, опять же, файл).
ОК?

Родитель Ссылка

Наталья Х

26.02.2019 13:02:57

Добрый день! Николай, спасибо за урок!
Помогите, пожалуйста, понять почему PQ загружает только первые 100 строк из исходного листа+1 строка шапка, а 102 строка - многоточие. Т.е. показывает, что данные есть но больше 100 строк не загружает.

Ссылка

Николай Павлов

27.02.2019 11:00:07

Наталья, не видя вашего файла с исходными данными и вашего запроса - не смогу сказать. Шлите на почту - посмотрю как будет минутка, ОК?

Родитель Ссылка

Роман Ш

26.03.2019 19:06:01

Подскажите, пожалуйста, есть ли возможность подобным образом, имея фиксированное количество строк, добавлять условные "города" по столбцам?
В этом примере получается, есть фиксированное количество столбцов и города по строкам (т.е. наоборот, сделать транспоринирование)

Ссылка

Александр

07.05.2019 16:50:59

Добрый день. Подскажите, пжлст, у меня MS Office 2019 Pro, но почему то в редакторе отсутствует доп.контекстное меню для "Месяца" и я не могу получить из колонки Дата название месяца (как описано в статье), далее нет значков для колонок для быстрого определения формата. Это зависит от версии редактора? Как-то нужно его обновить?
И вопрос № 2, подскажите, пжлст, как получить текстовое название месяца из колонки Дата без меню (ну т.е. имею в виду код).
Спасибо!

Ссылка

Николай Павлов

11.05.2019 11:37:04

Да, вам нужно обновить Power Query - для этого просто нужно обновить весь Excel через Файл - Учетная запись - Обновить.
Если формулой, то будет =Date.ToText(колонка_с_датой, "MMMM")

Родитель Ссылка

Юрий Николаев

28.05.2019 10:46:15

Приветствую всех!
В одном файле появилась проблема: при обновлении данных в PQ и в сводных таблицах выскакивает сообщение "Нам не удалось загрузить модель данных. Возможно, модель данных в этой книге повреждена." И больше никаких сведений. При этом само обновление после нажатия кнопки Ок в этом сообщении дальше проходит успешно (?), но, может быть, что-то не так обновляется?
Где нужно покопаться, чтобы найти это самое "повреждение"?

P. S. Хорошо, что делал резервные копии этого файла. При использовании резервной копии такое сообщение не появляется.

Ссылка

Daniyar Saurbayev

29.01.2020 08:00:58

Добрый день, подскажите Вы нашли ответ на Вашу проблему?! У меня такая ж проблема. Спасибо.

Родитель Ссылка

Юрий Николаев

03.02.2020 20:26:13

Да, решил с помощью форума. PQ в этом был не виноват. Это в Сводных у меня был косяк. А ввиду того, что при обновлении данных через PQ обновлялись и данные на других листах и на этом листе через формулы, выскакивало это сообщение.

Родитель Ссылка

Алексей Довнер

06.06.2019 18:18:39

Добрый день!
При попытке создать пользовательский столбец, выдает ошибку:
"Ошибка выражения :Имя "getData" не распознано. Убедитесь, что оно написано верно"
Формула:  =getData([Folder Path]&[Name])

Вопрос снят, я разобрался;)

Ссылка

Кристина Ткачева

26.06.2019 11:43:45

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

An error occurred in the ‘’ query. Expression.Error: The key matched more than one row in the table.Details:    Key=Record    Table=Table

Заранее большое спасибо за ответ

Ссылка

Кристина Ткачева

26.06.2019 12:31:21

Нашла ответ самостоятельно о том, как решить данную проблему:
Ее можно наглядно увидеть по ссылке
После применения данного шага на этапе формирования запроса "GetData" Error в Пользовательском столбце пропадает.
Вызвана она, скорее всего, в большинстве случаев именно привязкой к названию листа, с которого запрос считывает наши данные.

Родитель Ссылка

Елена Рудакова

06.02.2020 13:22:47

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

Родитель Ссылка

Собир Мажидов

29.07.2019 07:58:45

Как можно сделать тоже самое только с файлами csv?

Ссылка

Алексей Семилетов

13.09.2019 14:37:26

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

Ссылка

Александр Назаров

02.10.2019 13:13:35

Николай, добрый день!
Ранее был создан запрос из папки и все отлично работало, но стоило в папку добавить файл с идентичной таблицей, как запрос перестал работать.
Пишет ошибку: "В запросе "" произошла ошибка. Expression.Error: Столбец "Column9" таблицы не найден.
Сведения:
   Column9"
Что изменилось после добавления новой таблицы?
Заранее спасибо.

Ссылка

Александр Лобода

13.11.2019 11:26:57

День добрый!
Функционал полученный при помощи PQ будет работать только если на ПК пользователя в EXCEL так же имеется данная надстройка?

Ссылка

Екатерина Шевченко

11.12.2019 17:51:40

Добрый день!
Спасибо за статью!
Подскажите пожалуйста, что конкретно нужно прописывать на этапе, когда добавляем пользовательский столбец, вместо getData([Folder Path]&[Name])

Я прописываю например: getData([C:\Users\Desktop\Выверка данных по выгрузкам]&[Москва]), то есть ([путь к файлу]&[имя файла])

Мне выдает ошибку синтаксиса : Недопустимый идентификатор

Буду признательна за помощь

Ссылка

Egor87

18.12.2019 16:46:43

getData([тут вписывается имя столбца с адресом к фаилу(folder path ]&[тут имя столбца с названием фаила (name)], и все будет работать.

Родитель Ссылка

Сергей Машковец

15.12.2019 23:49:49

Как сделать так, чтобы в собранной таблице отображалась дополнительная колонка "Date created"?

Ссылка

Татьяна Агафонова

14.01.2020 11:37:41

Сделать второй запрос в виде подключения с именами файлов и Создать запрос/Объединить запросы, назначить столбец с именем файла основой для объединения

Родитель Ссылка

Egor87

18.12.2019 17:02:47

Как в Power Query при сборе данных из нескольких фаилов сделать чтоб имя фаила стало названием столбца и таблица формировалась из разных фиалов в разные столбцы? В исходном фаиле есть след информация которую нужно вытянуть в таблицу: дата/время/данные/данные/. При формирование отчета через Power Query из нескольких файлов все данные из всех файлов формируются в 5 столбцов: имя файла/дата/время/данные/данные/ и данные из файлов идут друг за другом.

Ссылка

Олег Михнюк

23.12.2019 14:09:37

В экселе столбец  - с датами, после размещения в PQ - даты в текстовом формате и с часами минутами все как было в исходном файле эксель. После назначения в PQ столбцу формат даты - содержимое пропадает. В чем может быть причина?

Ссылка

Александр Кар

05.02.2020 10:38:26

Всем добрый день. Задача стоит проще, собрать таблицу, не совсем похожую в разных файлах, в отдельных файлах, в одну книгу на одном листе. Без обновлений информации  и т.п., просто все в кучу, таблица за таблицей подряд. Чтото по данному примеру не получается. Если делать просто запрос из папки и выбрать например пару файлов, т.е. в папке есть всего 2 файла, то при открытие, в первом столбце CONTENT со значениями Binary  нажать  на значок ↓↓ то отображаются данные из этих 2 таблиц корректно, если выбрать больше файлов, то отображение некорректное, все столбцы файлов сливаются в один столбец и разделяются ; . Подскажите что нужно настроить в данном случае? Речь идет об ОФИС 2016. в 2010 с помощью Power Query, получалось, только там  открывается окно перед отображением таблицы и в этом окне выбирается файл из всей кучи в котором больше столбцов и по примеру него собираются всев одну таблицу. Что нужно сделать в 2016 чтобы результат был нужный? Надеюсь объяснил понятно. Спасибо  

Ссылка

Анна

27.03.2020 15:32:04

Добрый день!
Собираю, как у вас данные из кучи файлов. Спасибо все работает. Помогите, пожалуйста, нужен двойной фильтр. Но данные не могу предоставить из-за конфиденциальности. Допустим у меня есть колонка тип с цифрами от 1 до 13 и колонка со множеством пользователей. В собираемой таблице в типах 4 и 9 нужно удалить данные пользователей с Null.
У меня удаляется фильтр и остальные типы вообще исчезают. Что я делаю не так?
Спасибо

Ссылка

Марина

07.04.2020 10:51:23

Здравствуйте, Николай.
Огромное спасибо за ваши статьи.
Подскажите, пожалуйста, как решить небольшую проблему.
Не могу никак собрать данные, хотя на другом компьютере все получалось без проблем. При загрузке запроса выдаётся сообщение:
"Сбой инициализации источника данных.
Проверьте сервер или обратитесь к администратору данных. Убедитесь, что внешняя база данных доступна, и вновь повторите операцию. При повторении сообщения создайте новый источник для подключения к базе данных."

Все собираемые файлы хранятся на рабочем столе в папке с уникальным названием.  

Ссылка

Алексей Шайкин

22.04.2020 21:01:52

Николай, добрый день!
Спасибо за полезные уроки!
У меня проблема - на шаге внесения изменений "Расширенного редактора" выскакивает надпись "Ожидался токен Eof ".
Что это может означать?

Все делаю по инструкции.  

Ссылка

Максим Крутелевич

11.06.2020 18:44:10

Добрый день
помогите разобраться.
у меня есть книга, в которой140 листов. на каждом таблица из 6-7 столбцов. 5 столбцов одинаковые, а 1-2 столбца могут присутствовать или отсутствовать на отдельных листах (в средине таблицы).
мне нужно объединить данные из этих листов в один массив, но когда я делаю это с помощью PowerQuery - то столбцы с одинаковым названием не выстраиваются друг под другом.

Ссылка

Александр Malder

15.06.2020 12:11:24

Добрый день, Николай

Я очень вас благодарю за ваш труд.
Пример отличный.
Но возникает вопрос:  не могу решить....  Меня множество файлов  во множестве папок.  И у них у всех первые три строки в виде шапки имеют объединение ячеек. Это вызывает ошибку в редакторе запросов  при проведении 1го иди 3го шага
подскажите как обойти сию трудность или  как во множестве файлов Excel поснимать объединение ячеек  внутри файлов, не открывая их.

Благодарю.

Ссылка

Дарья

26.06.2020 21:35:33

добрый день! Николай, занимаюсь по Вашей книге "Скульптор данных....", столкнулась со следующей проблемой
когда подгружаю в PQ или текстовый файл(из примера в книге) или "загрузка данных через Word" , то таблицы полностью в PQ не подгружаются , всегда пару строк или столбцов остаются за бортом( я уже и обновила excel (у меня2019). какие действия можно предпринять?

Ссылка

Наталья Логинова

25.07.2020 14:47:35

Николай, добрый день! Мои ИТ поставили 2016 Excel. Не могу сказать, есть ли там январские обновления, но binary колонка не раскрывается. Поэтому делаю так, как вы показали с функцией. Вот беда - в итоге колонки (не строки) оказываются отсортированными по алфавиту. То есть не как в оригинале Name, dpt, 1. 2 ...11, 12, а 1, 11. 12...2, Name, dpt
Огорчение. Как быть?
Спасибо!

Ссылка

Антон Неровный

30.07.2020 12:45:27

Коллеги, добрый день!
Николай, спасибо Вам большое за полезное видео. Всё делал как Вы рассказывали и всё получилось. Но столкнулся с такой проблемой. При обновлении общего файла куда сливаются данные из 70 других файлов, сбивается настройка ширины столбцов и из-за этого очень долго происходит само обновление. (около 20-30 минут. Размер файла 90000 строк на 90 столбцов).

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

Кто-то сталкивался с подобными проблемами? Есть ли какое-то решение или идеи что нажать чтобы всё работало?)

Заранее спасибо за обратную связь!)
 

Ссылка

Алексей Семилетов

28.08.2020 15:44:01

Уберите галочку из чек-бокса, как указано на картинке по ссылке.

Родитель Ссылка

Ирина Гурдзибеева

28.08.2020 12:57:46

Добрый день!
Можно ли с помощью power query объединить несколько книг, состоящих их нескольких листов, так чтоб в новой книге каждый лист суммировался отдельно.  

Ссылка

Николай Павлов

11.09.2020 08:00:20

Да, вполне можно.
Собираете данные из всех книг в одну таблицу, добавляя колонку с именем листа, а потом по этой колонке выполняете в Power Query группировку (Преобразование - Группировать), чтобы просуммировать данные по каждому листу.
Как-то так

Родитель Ссылка

Баяман Эргешов

09.09.2020 17:34:55

Отличная статья! Хотел бы уточнить момент, она подходит значит для решения такой задачи? Есть папка  которую каждый месяц складывают отчет за месяц, то есть там будет 12 файлов к концу года, в файле каждого месяца есть данные по объектам, которые необходимо автоматически переносить в другой файл отчета, в соответствии с его месяцем. То есть если подвязать функцию к папке то данные автоматом должны переноситься как только в папку копируется новый файл месяца?

Ссылка

Николай Павлов

11.09.2020 07:59:02

Да, совершенно верно

Родитель Ссылка

Anatoly Vishnevsky

09.09.2020 21:26:54

Большое спасибо за подобные видео. Это лучший ресурс из тех, что я знаю. Вы очень помогаете!

Ссылка

Сергей Сытник

02.10.2020 14:38:24

Добрый день! А где можно сейчас скачать эту надстройку?

Ссылка

Roman Roman

12.10.2020 12:25:20

[url]www.microsoft.com/ru-RU/download/details.aspx?id=39379[url/]

Родитель Ссылка

Roman Roman

12.10.2020 12:18:59

Здравствуйте.
Подскажите, почему Power Query ругается, когда пытаюсь сделать запрос на загрузку файл формата XLSB из папки?

Ссылка

Мария Антипова

27.10.2020 14:51:36

Здравствуйте! помогите, пож. Создала книгу с запросами pq в 2013 офисе. Дала коллеге шаблон пользоваться, у него 2016 офис. И запрос не обновляется. Возникает ошибка: "Подключение невозможно обновить. Не удалось определить книгу, которая содержит подключение. Попробуйте обновить подключение через некоторое время." В чем может быть проблема?

Ссылка

Елена Петрова

30.10.2020 15:54:55

Подскажите пожалуйста , на шаге добавление польз. столбца с использованием функции getData, выходит ошибка "Ошибка выражения: Имя "getData" не распознано. Убедитесь в том, что оно написано верно." синтаксис верный. Версия Excel 2016 Profesional. Обновить нет возможности.
Заранее спасибо.

Ссылка

Елена Толмачева

14.12.2020 14:06:32

Добрый день, спасибо отличная статья. У меня такой вопрос, я собрала таблицы, но теперь я хочу с этой таблицей дальше делать расчеты, мне лучше скопировать данные в другую книгу или на другом листе делать расчеты ссылаясь на эти исходные данные?

Ссылка

Дмитрий Чиченко

21.12.2020 14:58:20

Николай, добрый день

А можно сделать с помощью PQ все тоже самое, но в обратную сторону?

Т.е. у меня есть таблица, я её обрабатываю в PQ, а в итоге нужно "раздербанить" эту таблицу на множество частей (например по критерию "город") в виде отдельных файлов Excel?

Без VBA и PLEX силами PQ это реально сделать?

Спасибо.

Ссылка

Елена Толмачева

28.12.2020 14:54:47

Добрый день, первый раз когда делал все отлично получилось. А теперь такие ошибки, что с этим сделать? Уже несколько раз пересобирала

https://hkar.ru/13CMr
https://hkar.ru/13CMs

Ссылка

Елена Толмачева

28.12.2020 14:59:29

Ответ нашла сама, ошибка глупая, в 2-х книгах не было 2 последних столбцов и одна из неправильных книг шла вторая в папке и после нее ничего не загружалось

Родитель Ссылка

Елена Толмачева

28.12.2020 15:00:53

Еще вопрос, изначально когда отредактировали таблицу в PQ, ее можно потом отредактировать, когда поняли, что один столбец не удалили?

Ссылка

Маргарита

19.01.2021 06:44:49

Добрый день!
После обновления Экселя пропала кнопка "Получить данные из файла":(
Подскажите пжл, как теперь ее найти и вытащить?

Есть подключение к Аксес, Интернету, тексту, другим источникам. А подключение к другой книге excel отсутствует..

Ссылка

Марина Киладзе

02.04.2021 00:48:22

Николай, а можно с помощью Power Query собрать разные по структуре таблицы? а именно, три конкретных столбца (эти столбцы одинаковые во всех таблицах), только они могут располагаться в разных местах в таблицах. Если "Да", то возможно вы поделитесь ссылкой, где можно посмотреть как это сделать

Ссылка

Михаил

23.03.2023 19:04:54

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

Родитель Ссылка

Михаил

24.03.2023 10:48:10

Может кому нужно будет, получилось сделать как описано в P.S. статьи, PQ сам распределил данные в соотв. с названиями столбцов, т.е. делаем так:

1. Выбрать Создать запрос - Из файла - Из папки - Выбрать папку - ОК
2. После появления списка файлов нажать Изменить
3. В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла
   

Плюс небольшой момент. Если нужны все столбцы, в качестве примера после открытия Binary нужно взять заранее выбранный файл в котором есть все встречающиеся столбцы, ну или можно образец сделать со всеми названиями столбцов, которые могут быть.

Родитель Ссылка

Александр Кравчук

20.04.2021 19:59:46

Здравствуйте, а как собрать данные из множества файлов формата csv в формат екселя не на одном листе а на отдельных листах книги. Т.е. все файлы должны быть в одной книге но на разных вкладках (листах). И исходные файлы с расширением csv

Ссылка

Мария Фурсенко

21.04.2021 16:08:16

Добрый день!
Можно ли построить сводную таблицу в таком формате?

Ссылка

Денис Тарасевич

10.06.2021 14:49:51

Коллеги, здравствуйте.
У меня вопрос!  Office 365
при создании настраиваемого столбца нет функции getData  -  не могу создать формулу...  Почему так?!
Нет в списке вообще ничего с началом get

Expression.Error: Имя "getData" не распознано. Убедитесь в том, что оно написано верно.

Ссылка

Аля Сай

30.07.2021 10:24:00

Николай, здравствуйте! Большое спасибо вам за уроки! все так доступно рассказано!

Я была в надежде что получится соединить данные с нескольких файлов, но почему то у меня выходят ошибки(((
уже при создании Пользовательского в нескольких строчках выходит Error

В запросе "" произошла ошибка. Expression.Error: Столбец "Column7" таблицы не найден.
Сведения:
  Column7

Подскажите пожалуйста, в чем может быть ошибка?
Заранее благодарю!

p.s. скриншоты тут
https://cloud.mail.ru/public/hrc4/LdTa1srFa

Ссылка

Юрий Газарян

25.08.2021 16:14:58

Здравствуйте!
При попытке загрузить данные из файла, почему-то загружает только первые 10 строк.
Кто сталкивался с такой проблемой? Помогите, пожалуйста, найти решение.


Спасибо!

Ссылка

Сергей Лелетко

26.10.2021 17:22:11

Здравствуйте. Подскажите пожалуйста. После того, как пользовательская функция готова, возможно залесть внутрь и добавить еще столбцы?

Ссылка

Валерий Зеленский

01.11.2021 13:06:13

Здравствуйте! Спасибо Николаю за книгу - хороший толчок для освоения этого великолепного инструмента!. Подобная задача была моей первой в PowerQuery, сейчас в разработке уже четвертая - не перестаю восхищаться!

Ссылка

Алексей Труфанов

03.02.2022 14:33:49

Автор красавчик!!! ты сэкономил мне кучу времени этим видосом!!!

куда задонатить пару соточек?

Ссылка

Иман Абишев

25.05.2022 13:18:14

Доброго дня!! А как сделать примером не первый файл из папки а например третий?? Когда загружается из папки он по умолчанию берет первый файл.

Ссылка

Антон Б.

22.06.2022 11:45:56

Здравствуйте! Очень нужна помощь по этой теме!
В рабочий файл запросом power query вставляется таблица из файла Общая база, затем другим запросом она разбивается на листы по сотрудникам, далее макросом сохраняет листы отдельными файлами. Затем снова в файле Общая база запрос из папки собирает все скорректированные файлы сотрудниками в одну таблицу. Если сотрудники просто чтото изменили в строках или добавили новую, в общей базе все корректно и справляется,но если они включили в своём файле фильтр, и даже потом сняли перед сохранением, в общей базе их строки дублируются. Помогите пожалуйста решить проблему с дублями.  

Ссылка

katya katina

30.06.2022 16:22:15

Николай как сделать так чтобы столбы не подбираль все ниже и ниже в вставали в паралльльные

Ссылка

Елена Палова

24.10.2022 12:22:47

Добрый день. На этапе прописания команды getData пишет ошибку... что не так?
https://ibb.co/Rj138VK
https://ibb.co/rtL07jz

Ссылка

Виталий Бабышев

13.12.2022 17:19:57

Аналогичная ситуация, не могу добавить getdata

Ссылка

A D

04.04.2023 07:09:11

Спасибо!

Ссылка

Петр Кириченко

10.04.2023 12:00:34

Добрый день!
Повторил все шаги. Но к сожалению получил ошибку " Предоставленный путь к файлу должен быть допустимым абсолютным путем". Подскажите, с чем это связано?

Ссылка

Игорь Скворцов

13.04.2023 11:54:19

Добрый день!
Как сделать подобный сбор данных не из разных файлов, а из разных листов одного файла?

Ссылка

Олег Ирхин

24.04.2023 18:35:03

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

Ссылка

Денис

17.02.2024 18:36:39

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

Родитель Ссылка

Den Myocean

14.07.2023 15:51:08

у меня не работает.
после добавления getData([folder path]&[name&]) в созданном столбце в значениях ячеек ошибки.

Ссылка

Алексей Нагаев

18.08.2023 06:36:04

Всем доброго времени!
Вопрос.. Как передать в Folder.Files не абсолютный, а относительный путь? Что-то типа Folder.Files(Запрос{строка}[столбец]) у меня не работает...

Ссылка

Денис

17.02.2024 18:34:45

Добрый день Николай. Спасибо за отличную статью. Воспользовался для создания базы данных. Подскажите пожалуйста, а как решить обратную задачу? В собранный файл из нескольких были внесены значения, и теперь их нужно разнести в соответствующие файлы из которых он был собран. Понимаю, что совсем не логично, но такая потребность появилась, буду рад любому совету , чтобы понять возможные направления для решения. Пока на ум приходит только создание в каждом файле из которого он собран запроса, который будет обращаться к собранной базе данных и из неё выбирать соответствующие файлу данные, но это кажется очень долгим способом та как файлов много.

Ссылка

Олег Меркулов

26.02.2024 21:08:12

Николай, доброго времени суток! Спасибо за Ваш труд и понятные уроки! Но столкнулся с такой проблемой.
Есть 3 отчета (3 файла в формате Excel), которые хранятся в папке на общем сервере для 15 пользователей. Данные пользователи вносят корректировки в данные отчеты и сохраняют их там-же. Я же и еще 5 человек работают со сводным отчетом, который так-же находится на сервере, но в другой папке. Источником для запроса итогового файла, является папка с этими тремя отчетами. В зависимости от того кто открывает папку итогового отчета и папку с отчетами время от времени в них создаются скрытые системные файлы: thumbs.db и ~$"название отчета".xlsm (если отчет открыт). При обновление запроса выдает ошибки и итоговый файл не обновляется. Как сделать так, чтобы запрос игнорировал данные файлы? Спасибо!
 

Ссылка

Руслан пон

08.04.2024 12:57:49

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

Ссылка

Денис Киркоров

27.04.2024 17:49:39

А если возникает ошибка "[Expression.Error] ключу не соответствует ни одна строка в таблице" при сохранении и загрузке? Много файлов, все заголовки одинаковые, в квери видит всё корректно.

Ссылка

Маргарита

16.05.2024 09:42:52

Николай, подскажите пожалуйста, есть такая возможность из неправильной таблицы сделать свод, с запросом из папки для ежедневного обновления данных. Пример: есть отчет ключевых показателей, в книге 10 листов. Для создания Инфоцентра нужны не все ключевые показатели, а некоторые в своде с разных листов. Информацию ежедневно нужно путем запроса в новый такой-же файл обновлять? Изначально отчет ключевых показателей сделан не грамотно, есть объединенные ячейки, по факту отчет не преобразовать в умную таблицу.

Ссылка

Лев Москаленко

01.01.2025 17:46:51

"Сложна, сложна, сложна!"

Ссылка

Gennady Terekhov

10.04.2025 10:38:49

Добрый день.
А как-то можно задать диапазон для сборки данных? В исходных файлах таблица с нужными данными начинается с 6 строки, все что выше – служебная информация, которая присутствует в каждом файле? Как сделать, чтобы первые 5 строк игнорировались?

Ссылка

Страницы: 1  2  3  4