Силовой запрос добавить столбец с именем файла

Обновлено: 03.07.2024

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

Инструкции по созданию относительного пути следующие.

1. Чтобы использовать взлом Power Query, сначала подключитесь к другой электронной таблице с помощью Power Query.

Вы можете сделать это, перейдя на вкладку "Данные" и выбрав "Получить и преобразовать", затем "Из файла" и выберите нужный файл Excel, который вы хотите загрузить в Power Query.

В данном случае я выбрал интересующий меня локальный файл Sample Table.xlsx.

2. Когда закончите, выберите «Закрыть и загрузить в».

Если вы решите сначала отредактировать таблицу, выберите «Закрыть» и «Загрузить в» в редакторе Power Query. Кроме того, вы можете пропустить этот шаг и просто загрузить его прямо в электронную таблицу, нажав кнопку «Загрузить» в диалоговом окне Power Query.

3. Затем создайте новую вкладку рабочего листа под названием «Настройка».

Щелкните правой кнопкой мыши внизу рабочего листа и добавьте новую вкладку. Я рекомендую называть его «Настройка», но вы также можете называть его «Бэкенд», «Настройки» и т. д. Однако важно быть явным. Почему? Потому что это хорошая практика работы с электронными таблицами, вот почему.

4. В нужную ячейку добавьте правильную формулу.

Правильная формула выглядит следующим образом:

Давайте разберем эту формулу. Первая часть — это CELL («имя файла», $A$1). Если вы введете это в Excel, он вернет все имя файла, путь и лист. В нашем образце данных это выглядит так:

C:Usersjpo64DocumentsЗагрузить файлы Powe Query[Пример файла Power Query.xlsx]Настройка

Обратите внимание: на самом деле нам нужно все до первой «[», также называемой «левой квадратной скобкой».

Поэтому, чтобы разобрать то, что нам нужно, мы используем функцию FIND(), чтобы найти первый (и единственный экземпляр) левой квадратной скобки в имени файла. В нашем примере он вернет 26, что означает, что он был найден как 26-й символ во всей строке. Это означает, что если нам нужно все до этой квадратной скобки, нас на самом деле интересуют первые 25 символов (поэтому мы вычитаем в формуле на единицу).

Наконец, мы воспользуемся командой LEFT (которая извлекает определенное количество определенного текста, начиная слева), чтобы извлечь путь к файлу из всей строки. В этом случае нам нужно снова получить полный путь к файлу, поэтому мы повторяем CELL("filename", $A$1) в первом параметре LEFT.

5. Назовите эту ячейку Filepath

Чтобы предоставить Power Query доступ к этой ячейке, нам нужно дать ей определенное имя. Самый простой способ сделать это — указать путь к файлу в поле именованного диапазона, а затем нажать Enter.

6. Дважды щелкните свой запрос и откройте редактор Power Query

Есть несколько способов открыть редактор Power Query для определенного запроса, но я предпочитаю щелкать его во всплывающем окне, пока не появится редактор.

7. Откройте расширенный редактор

В Power Query откройте Расширенный редактор на вкладке "Главная".

8. Над источником поместите следующий текст:

Затем измените исходную переменную.

Источник = Excel.Workbook(File.Contents(Filepath & «Sample Table.xlsx»), null, true),

Если вы все сделали правильно, это будет выглядеть примерно так:

Некоторые заключительные мысли

Если у вас возникли проблемы, я обнаружил, что есть две основные области, в которых люди были сбиты с толку:

При импорте данных из плоского файла с помощью Power Query вы захотите объединить несколько файлов и включить имя файла в результирующую таблицу. Например, в статье «Анализ детских имен с помощью Power BI», которую я недавно опубликовал, имя файла содержит год, который мне нужно было включить в таблицу. Вот как это сделать:

Шаг 1. Создайте запрос для одного файла

Во-первых, необходимо создать запрос для извлечения данных из одного плоского файла. Для этого нажмите «Получить данные», выберите «CSV» и перейдите к файлу. Если применимо, выберите «Использовать первую строку в качестве заголовков» на главной вкладке и/или переименуйте столбцы, чтобы использовать осмысленные имена. Вы можете переименовать столбцы, дважды щелкнув столбец и указав нужное имя.

На этом этапе таблица должна выглядеть так, как показано ниже:

Запрос должен быть похож на показанный ниже:

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

Затем преобразуйте приведенный выше запрос в функцию.Просто добавьте новую строку в начале, которая принимает входной аргумент с именем «myfile», и замените имя файла, включая двойные кавычки, входным аргументом. Переименуйте запрос, я выбрал ReadFileContents и нажал «Готово».

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

Шаг 3. Добавьте новый запрос, указывающий на папку

Теперь создайте еще один запрос и «Получить данные» из папки, как показано ниже. Это автоматически объединит данные из файлов в этой папке.

В поле «Путь к папке» укажите папку, содержащую ваши файлы, и нажмите «ОК».

Таблица должна выглядеть так, как показано ниже, со свойствами файла в виде столбцов.

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

Шаг 4. Добавьте пользовательский столбец с помощью функции

Теперь добавим новый столбец, выбрав «Добавить столбец» и «Добавить пользовательский столбец». В окне формулы введите «= ReadFileContents([Путь к папке]&[Имя])». По сути, мы вызываем функцию — один раз для каждого файла — который мы создали ранее, передавая объединенный путь и имя папки.

На этом этапе таблица должна выглядеть так, как показано ниже.

Шаг 5. Разверните

Мы почти закончили. Нажмите на иконку «Развернуть» (стрелки, которые явно ненавидят друг друга, иначе зачем бы они не смотрели друг другу в глаза) справа от столбца «Пользовательские». Это расширит содержимое пользовательского столбца и отобразит все столбцы, как показано ниже.

Шаг 6. Окончательная очистка

Наконец, вы можете добавить дополнительные шаги для очистки таблицы, например удалить «Путь к папке», если он больше не требуется, и отформатировать столбец «Имя» по желанию. Я использовал «Заменить текст», чтобы избавиться от «yob» и «.txt» и включить только «Год».

Обзор

Вот как вы можете объединять файлы вместе с именем файла в Power Query. Это будет полезно в некоторых случаях, когда имя файла — полностью или частично — содержит ключевую информацию, такую как даты, местоположения, сотрудников и т. д., имеющую отношение к вашему анализу.

Power Query — отличный инструмент для импорта и преобразования данных в Excel. Но, к сожалению, в нем отсутствует ключевая функция, которая делает его по-настоящему полезным.

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

Нам потребуется установить Power Query. Прочтите этот пост, чтобы узнать, как его установить.
Структура данных в каждом файле должна быть одинаковой.
- Одинаковое количество столбцов с одинаковыми заголовками.
- Данные находятся на одном и том же имени листа в каждом файле.
Каждый файл имеет одинаковую структуру. Заголовки столбцов одинаковые и расположены в том же порядке. Данные находятся на листе с именем Sheet1 для каждого файла. Только данные разные для каждого файла.

Шаг 1. Настройка запроса на импорт для одного файла.

Теперь мы настроим запрос на импорт для одного файла.
1. Перейдите на вкладку Power Query на ленте.
2. В разделе "Получить внешние данные" нажмите кнопку "Из файла".
3. Выберите Из Excel в раскрывающемся списке.
Теперь выберите файл для импорта. На этом шаге вы можете выбрать любой из файлов, так как мы создаем запрос на импорт, который будет работать для всех файлов, и все файлы имеют одинаковую структуру.
1. Перейдите к своей папке и выберите любой файл.
2. Нажмите кнопку ОК.
Предварительный просмотр ваших данных появится в окне Navigator.
1. Выберите лист, содержащий ваши данные. В нашем случае Лист1. Проверьте предварительный просмотр данных, чтобы убедиться, что у вас правильные данные.
2. Нажмите кнопку "Изменить".
Шаг 2. Отредактируйте запрос.

Power Query создаст код импорта (на пользовательском языке M) в фоновом режиме, но нам нужно будет его немного отредактировать.
1. Перейдите на вкладку "Вид".
2. Нажмите кнопку "Расширенный редактор".
3. Теперь вы увидите код, созданный для вашего импорта.
Теперь мы можем редактировать M-код.
1. Добавьте красные фрагменты текста в свой код.
2. Нажмите кнопку "Готово".
3. Переименуйте запрос в functionGetFiles.
4. Перейдите на вкладку "Главная".
5. Нажмите кнопку "Загрузить и закрыть".
Мы только что изменили запрос на функцию, которая принимает путь и имя файла в качестве входных данных, а затем возвращает данные из этого пути и имени файла.

Шаг 3. Создайте запрос к папке, содержащей ваши файлы.

Теперь мы создадим запрос к папке, содержащей все наши файлы.
1. Перейдите на вкладку Power Query.
2. Нажмите кнопку "Из файла".
3. Выберите из папки в раскрывающемся списке.
4. Найдите или введите путь к папке.
5. Нажмите кнопку ОК.
Этот тип запроса выводит данные обо всех файлах в папке, такие как имя файла, местоположение, дата изменения и т. д. Список ваших файлов будет предварительно просмотрен, и мы можем нажать кнопку «Редактировать», если этот предварительный просмотр выглядит нормально.

Шаг 4. Добавьте столбец с пользовательской функцией.

Теперь мы добавим в этот запрос столбец.
1. Перейдите на вкладку "Добавить столбец".
2. Нажмите кнопку "Добавить пользовательский столбец".
3. Измените имя столбца на GetData.
4. Введите эту формулу в формулу пользовательского столбца.
5. Нажмите кнопку ОК.
Это вызовет функцию, которую мы создали на шаге 2.

Теперь нам нужно развернуть только что созданный столбец GetData, чтобы показать его результаты.
1. Нажмите значок "Развернуть и отфильтровать" в заголовке нашего нового столбца GetData.
2. Выберите переключатель "Развернуть".
3. Нажмите кнопку ОК.
4. Теперь перейдите на вкладку "Главная".
5. Нажмите кнопку "Закрыть и загрузить".
Теперь наши данные будут импортированы из всех файлов в нашей папке и объединены в одну таблицу. Если мы добавим файлы в нашу папку позже, мы также сможем импортировать их, просто обновив запрос. Вау, СИЛА!
1. Вот наши данные из всех 4 файлов, объединенные в одну таблицу.
2. Если мы добавляем файлы в нашу папку и хотим их импортировать, перейдите на вкладку «Данные».
3. Нажмите кнопку "Обновить все".
Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Подписаться

Похожие записи

5 способов получить текущую дату или время в Excel

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

5 способов изменить регистр текста в Excel

Узнайте, как изменить регистр текста между нижним, верхним и правильным регистром в Excel, используя функции Excel, мгновенное заполнение, запрос мощности, DAX и Power Pivot.

Массовый поиск и замена в Power Query

Узнайте, как выполнять массовый поиск и замену в расширенном запросе на основе списка значений.

Комментарии

19 комментариев

Можно ли сделать это с файлами .csv?

Да, но на шаге 1 выберите "Из CSV" вместо "Из Excel". Остальные шаги будут такими же.

Похоже, это не работает в Excel 2016. Он просит спровоцировать параметр, я что-то упустил?

Кроме того, откуда в этой последовательности путь и имя? Нет]), идти?

Я ценю это!

В настоящее время у меня нет доступа к версии 2016, но я попробую ее с файлами версии 2013 и CSV и дам вам знать. А пока попробуйте этот метод вместо «Как импортировать несколько файлов с несколькими листами в Power Query».

Я опробовал его, и он отлично работал с файлами CSV. Мой совет: начните сначала и следуйте шаг за шагом, так как иногда допущенная ошибка становится слепым пятном.

Это был M-код, который у меня был для функции.

Здравствуйте, я получаю следующее сообщение после попытки ввести пользовательский столбец:

«Выражение.Ошибка: имя ‘functionGetFiles’ не распознано. Убедитесь, что оно написано правильно».

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

Я следовал вашему руководству шаг за шагом 3 раза (загрузить до завершения Шаг 4 > Расход > Закрыть и загрузить), и я получил эти данные, но также появляется сообщение об ошибке: «Инициализация источника данных не удалась. Проверьте сервер базы данных или обратитесь к администратору базы данных. Убедитесь, что внешняя база данных доступна, а затем повторите операцию. Если вы снова увидите это сообщение, создайте новый источник данных для подключения к базе данных».

PS: — Мисс Excel 2016
— я не пробовал M-код, на который вы ответили 22 января 2017 г. в 10:42. и не пробовал другую статью: Как импортировать несколько файлов с несколькими листами в Power Query

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

Во-первых, ВАУ!! Большое спасибо. Я впервые использую PowerBI (хотя у меня есть книга и несколько видеоуроков), и я чувствую, что моя жизнь вот-вот изменится к лучшему.Мне пришлось выяснить, что я должен игнорировать параметры и некоторые другие вещи, но не большие. Я думаю, что, возможно, все изменилось со скоростью света в MS.

Однако у меня есть вопрос о том, как поступить с почти одинаковым счетом-фактурой (несколько столбцов отличаются), и поэтому он выдается как ошибка. Как бы я мог почти выполнить функцию if или просто пропустить, если бы ее там не было?

Извините, когда я имею в виду счета, это потому, что я пытаюсь преобразовать XML-документы. Так что на самом деле я должен был сказать любые документы, которые немного отличаются по столбцам/формату.

Да, power query — это круто, и я до сих пор удивляюсь тому, на что он способен.

Я не уверен, что действительно понимаю ваши проблемы. У Power query есть синтаксический анализатор XML, так что я полагаю, вы используете его, а затем пытаетесь объединить результаты из кучи этих файлов, но в некоторых из них отсутствуют столбцы?

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

ВАУ!
Впечатляющая статья. Большое спасибо. Я смог загрузить большой объем данных, используя функцию получения данных из папки, но сгенерированный автоматически код настолько сложен, что впоследствии мне было трудно выполнить какое-либо преобразование данных.
Это идеальный способ. Код M прост и управляем, и я могу выполнять любые преобразования сначала для импорта одного файла, а затем для всего ввода.

Да, мощный запрос — это круто! Благодарим Microsoft за создание замечательного продукта.

Мне нравится этот инструмент. Есть ли способ обойти ошибку запроса, когда в папке открыт файл? (один из 180)

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

у меня есть CSV-файл для каждого дня месяца, названия даты в той же папке, например, 2018.09.01.csv, 2018.09.02.csv и т. д.… .

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

поэтому мой первый рабочий лист под названием «Сводка» в столбце A имел даты в том же формате, что и имя файла, поэтому A2 = 2018.09.01, A3 = 2018.09.02 и т. д.. следующий лист называется 1 для первого дня месяца, следующий с именем 2 и т. д.…. В моем сводном листе будут дополнительные столбцы с функциями, которые будут суммировать данные из всех рабочих листов за другие дни месяца

Я хотел бы иметь 1 мощный запрос, который использует значения в столбце A в качестве источника файла для заполнения всех остальных рабочих листов. поэтому в новом месяце мне просто нужно изменить месяц в столбце A с 09 на 10 в этом примере. поэтому рабочий лист для дня месяца 1, также названный 1, будет использовать значение в A2 в качестве имени исходного файла в запросе, рабочий лист 2 будет использовать A3 в качестве имени исходного файла и т. д.…

Я понимаю, что запросы должны быть уникальными на листе каждого дня месяца, чтобы указать, что правильная ячейка является сводной, и я не против определить ее один раз на каждом из 31 рабочего листа, но я не хочу этого делать. ежемесячно меняйте все 31 рабочий лист power query, просто меняйте месяц в столбце A сводки один раз в месяц.

Любая помощь приветствуется.

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

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

В любом случае удачи!

Привет, Джон! Я использую Excel 2016. Раздел, в котором вы создаете запрос к папке, содержащей все файлы. Вы видите:
Загрузить, изменить и отменить

Знаете почему?

Да, они обновили пользовательский интерфейс с тех пор, как я написал этот пост. Преобразование — это новая кнопка «Редактировать». Вы также можете нажать кнопку «Объединить» и выбрать «Объединить и изменить» (также новая функция).

Как объединить содержимое всех файлов в папке с помощью Power Query (получить и преобразовать)

В этой статье описывается простой способ объединения большого количества исходных файлов (xlsx, csv. ) из одной папки.

Мы будем использовать «Power Query», название которого в Excel 2016 — «Получить и преобразовать». Power Query недоступен для Excel 2007 и более ранних версий.

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

Мы будем использовать пример нескольких команд в одной компании, сообщающих свои расписания в файлах xlsx.Эти файлы должны быть объединены в единую базу данных Excel.

Один из них выглядит так (допустим, все они имеют одинаковые столбцы):

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

Запрос будет создан, но не загружен. На ленте «Вид» перейдите в «Расширенный редактор», где можно увидеть код запроса.

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

Подтвердите и сохраните. Функцию можно переименовать, например, в Merged (на панели справа).

Теперь давайте создадим еще один запрос из папки.

Выберите папку с исходными файлами:

Содержимым этого запроса является список файлов (а не их содержимое).

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

Подтверждено. В последнем столбце нажмите на двойную стрелку в заголовке и подтвердите.

При необходимости удалите ненужные столбцы и нажмите "Закрыть и загрузить".

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

1 комментарий

Это было очень полезно. Я использовал это, чтобы объединить более 20 шаблонов бюджетов расходов отдела в рамках нашего годового бюджетного процесса.

Читайте также: