Не найдена таблица excel с именем power query
В Power Query можно столкнуться с двумя типами ошибок:
- Ошибки на уровне шага
- Ошибки на уровне ячеек
Ошибка на уровне шага
Пошаговая ошибка предотвращает загрузку запроса и отображает компоненты ошибок на желтой панели.
Распространенные ошибки на уровне шага
Не удается найти источник — DataSource.Error
Эта ошибка обычно возникает, когда источник данных недоступен пользователем, у пользователя нет правильных учетных данных для доступа к источнику данных или источник был перемещен в другое место.
Пример. У вас есть запрос из текстовой плитки, которая была расположена на диске D и создана пользователем A. Пользователь A предоставляет общий доступ к запросу пользователю B, у которого нет доступа к диску D. Когда этот пользователь пытается выполнить запрос, он получает dataSource.Error , так как в своей среде нет диска D.
Возможные решения. Вы можете изменить путь к файлу текстового файла на путь, к которому у обоих пользователей есть доступ. Как пользователь Б, вы можете изменить путь к файлу, чтобы он был локальной копией того же текстового файла. Если кнопка "Изменить параметры" доступна в области ошибок, ее можно выбрать и изменить путь к файлу.
Столбец таблицы не найден
Эта ошибка обычно активируется, когда шаг создает прямую ссылку на имя столбца, которое не существует в запросе.
Пример. У вас есть запрос из текстового файла, в котором одно из имен столбцов — Column. В запросе есть шаг, который переименовывает этот столбец в date. Но в исходном текстовом файле произошло изменение, и у него больше нет заголовка столбца с именем Column , так как он был изменен вручную на Date. Power Query не удается найти заголовок столбца с именем Column, поэтому он не может переименовать столбцы. Отображается ошибка, показанная на следующем рисунке.
Возможные решения: существует несколько решений для этого случая, но все они зависят от того, что вы хотите сделать. В этом примере, так как правильный заголовок столбца Date уже поступает из текстового файла, можно просто удалить шаг, который переименовывает столбец. Это позволит выполнять запрос без этой ошибки.
Другие распространенные ошибки на уровне шага
При объединении или объединении данных между несколькими источниками данных может возникнуть ошибка Formula.Firewall , например, показанная на следующем рисунке.
Эта ошибка может быть вызвана рядом причин, таких как уровни конфиденциальности данных между источниками данных или способом объединения или объединения этих источников данных. Дополнительные сведения о диагностике этой проблемы см. в брандмауэре конфиденциальности данных.
Ошибка уровня ячейки
Ошибка на уровне ячейки не препятствует загрузке запроса, но отображает значения ошибок в ячейке. При выборе пробела в ячейке отображается область ошибок под предварительным просмотром данных.
Средства профилирования данных помогают более легко выявлять ошибки на уровне ячеек с помощью функции качества столбца. Дополнительные сведения: средства профилирования данных
Обработка ошибок на уровне ячейки
При возникновении ошибок на уровне ячеек Power Query предоставляет набор функций для их обработки путем удаления, замены или сохранения ошибок.
В следующих разделах указанные примеры будут использовать тот же пример запроса, что и начальная точка. В этом запросе есть столбец Sales с одной ячейкой с ошибкой, вызванной ошибкой преобразования. Значение внутри этой ячейки было NA, но при преобразовании этого столбца в целое число Power Query не удалось преобразовать НС в число, поэтому отображается следующая ошибка.
Удаление ошибок
Чтобы удалить строки с ошибками в Power Query, сначала выберите столбец, содержащий ошибки. На вкладке "Главная" в группе "Уменьшить строки" выберите "Удалить строки". В раскрывающемся меню выберите "Удалить ошибки".
Результат этой операции даст вам таблицу, которую вы ищете.
Замена ошибок
Если вместо удаления строк с ошибками необходимо заменить ошибки фиксированным значением, это также можно сделать. Чтобы заменить строки с ошибками, сначала выберите столбец, содержащий ошибки. На вкладке "Преобразование" в группе "Любой столбец " выберите "Заменить значения". В раскрывающемся меню выберите "Заменить ошибки".
В диалоговом окне "Замена ошибок " введите значение 10 , так как вы хотите заменить все ошибки значением 10.
Результат этой операции даст вам таблицу, которую вы ищете.
Сохранение ошибок
Power Query может служить хорошим средством аудита для выявления строк с ошибками, даже если вы не исправите ошибки. Здесь могут быть полезны ошибки keep . Чтобы сохранить строки с ошибками, сначала выберите столбец, содержащий ошибки. На вкладке "Главная" в группе "Уменьшить строки" выберите "Сохранить строки". В раскрывающемся меню выберите "Сохранить ошибки".
Результат этой операции даст вам таблицу, которую вы ищете.
Распространенные ошибки на уровне ячеек
Ошибки преобразования типов данных
Обычно активируется при изменении типа данных столбца в таблице. Некоторые значения, найденные в столбце, не удалось преобразовать в нужный тип данных.
Пример. У вас есть запрос, содержащий столбец с именем Sales. Одна ячейка в этом столбце содержит значение NA в качестве значения ячейки, а остальные имеют целые числа в качестве значений. Вы решили преобразовать тип данных столбца из текста в целое число, но ячейка со значением NA приводит к ошибке.
Возможные решения. После идентификации строки с ошибкой можно либо изменить источник данных, чтобы отразить правильное значение, а не NA, либо применить операцию "Заменить" , чтобы указать значение для любых значений NA , вызывающих ошибку.
Ошибки операций
При попытке применить операцию, которая не поддерживается, например умножение текстового значения на числовое значение, возникает ошибка.
Пример. Вы хотите создать настраиваемый столбец для запроса, создав текстовую строку, содержащую фразу "Total Sales: " сцеплено со значением из столбца Sales . Ошибка возникает из-за того, что операция объединения поддерживает только текстовые столбцы, а не числовые.
Возможные решения. Перед созданием этого настраиваемого столбца измените тип данных столбца Sales на текст.
При обновлении данных могут возникать ошибки двух типов.
Удаленного Однако ошибки, которые приходят из удаленного внешнего источника данных, являются другими. Произошла что-то в системе, которая может быть на улице, на полпути по всему миру или в облаке. Для таких типов ошибок требуется другой подход. Распространенные удаленные ошибки:
Не удалось подключиться к службе или ресурсу. Проверьте подключение.
Не удалось найти файл, к который вы пытаетесь получить доступ.
Сервер не отвечает и, возможно, находится в состоянии обслуживания.
Это содержимое не доступно. Возможно, он был удален или временно недоступен.
Подождите. данные загружаются.
Получить сведения о справке На сайте Office справки и обучения. Она не только содержит большой объем справки, но и сведения об устранении неполадок. Дополнительные сведения см. в устранении и обходных решениях недавних проблем в Excel для Windows.
Использование технического сообщества Используйте веб-Community Майкрософт для поиска обсуждений, относящихся к вашей проблеме. Весьма вероятно, что вы не первый, кто испытывает проблему, другие люди занимаются ее решением и даже могут найти решение. Дополнительные сведения см. в Microsoft Excel Community и Office Answers Community.
Поиск в Интернете Используйте предпочитаемую поисковую система для поиска дополнительных сайтов в Интернете, которые могут предоставлять обсуждения или подсказки. Это может быть отнимает много времени, но это может привести к более широкой сети для того, чтобы найти ответы на наиболее сложные вопросы.
Обратитесь в Office поддержки На этом этапе, скорее всего, вы понимаете проблему гораздо лучше. Это поможет вам сосредоточиться на беседе и сократить время, затраченное на поддержку Майкрософт. Дополнительные сведения см. в Microsoft 365 и Office службе поддержки клиентов.
Возможно, вам не удастся устранить проблему, но вы можете точно определить, в чем заключается проблема, чтобы помочь другим понять ситуацию и решить ее за вас.
Проблемы со службами и серверами Скорее всего, причина — периодические ошибки сети и связи. Лучше всего подождите и попробуйте еще раз. Иногда проблема просто утихает.
Изменения расположения или доступности База данных или файл были перемещены, повреждены, переведены в автономный режим на обслуживание или аварийно сбой базы данных. Дисковые устройства могут быть повреждены, а файлы будут потеряны. Дополнительные сведения см. в этой Windows 10.
Изменения в проверке подлинности и конфиденциальности Неожиданно может произойти, что разрешение больше не работает или в параметр конфиденциальности было внося изменение. Оба события могут препятствовать доступу к внешнему источнику данных. Обратитесь к администратору или администратору внешнего источника данных, чтобы узнать, что изменилось. Дополнительные сведения см. в настройкахи разрешениях источника данных и Настройка уровней конфиденциальности.
Открытые или заблокированные файлы Если открыт текст, CSV или книга, изменения, внесенные в файл, не включаются в обновление до тех пор, пока файл не будет сохранен. Кроме того, если файл открыт, он может быть заблокирован и к нему нельзя получить доступ, пока он не будет закрыт. Это может произойти, если другой человек использует версию Excel. Попросите их закрыть файл или проверить его. Дополнительные сведения см. в статьи Разблокировкафайла, заблокированного для редактирования.
Изменения схем на заднем Кто-то изменяет имя таблицы, имя столбца или тип данных. Это почти никогда не разумно, может иметь огромное влияние и особенно опасно для баз данных. Одной из них является то, что группа управления базами данных наила правильные средства контроля, чтобы избежать этого, но происходят спапцы.
Блокирование ошибок при сложении запросов Power Query пытается повысить производительность, когда это возможно. Для более производительности и емкости часто бывает лучше выполнить запрос к базе данных на сервере. Этот процесс называется сгибом запроса. Тем не менее Power Query блокирует запрос, если существует вероятность компрометации данных. Например, слияние определено между таблицей книги и SQL Server таблицей. Для конфиденциальности данных книги за установлено SQL Server конфиденциальность данных организации. Поскольку политика конфиденциальности является более строгой, чем в организации, Power Query блокирует обмен информацией между источниками данных. Сгиб запроса происходит за кадром, поэтому вас может удивить, когда возникает ошибка блокировки. Дополнительные сведения см. взадачах Основные сведения о сгибе запросов, Сгибзапросов и Сгиб с помощью диагностики запросов.
Часто с помощью Power Query вы можете точно определить, в чем заключается проблема, и устранить ее самостоятельно.
Переименованные таблицы и столбцы Изменения исходных имен таблиц и столбцов или столбцов почти наверняка приводят к проблемам при обновлении данных. Запросы используют имена таблиц и столбцов для формировать данные практически на каждом этапе. Не изменяйте или удаляйте исходные имена таблиц и столбцов, если только их не нужно использовать в источнике данных.
Изменения типов данных Изменение типа данных иногда может привести к ошибкам или непредвиденным результатам, особенно в функциях, для которых в аргументах требуется определенный тип данных. Примерами могут быть замена текстового типа данных в числовой функции или попытка вычисления с нечисловой типом данных. Дополнительные сведения см. в теме Добавление и изменение типов данных.
Преобразования Вы попытались преобразовать ячейку, содержащую 0, в целое число.
Математические Вы пытаетесь умножить текстовое значение на числовое значение.
Объединения Вы попытались объединить строки, но одна из них числовая.
Безопасно экспериментируйте и итерации Если вы не уверены, что преобразование может иметь отрицательное влияние, скопируйте запрос, проверьте изменения и итерации с помощью вариантов команды Power Query. Если команда не работает, просто удалите созданное вами шаг и попробуйте еще раз. Чтобы быстро создать образец данных с одной схемой и структурой, создайте Excel таблицу из нескольких столбцов и строк и импортировать их (выберите данные > Из таблицы илидиапазона). Дополнительные сведения см. в таблицах Создание таблицы и Импорт из Excel таблицы.
Когда вы впервые будете понять, что можно делать с данными в редакторе Power Query, вам может показаться, что вы ребенок в конфетном магазине. Но не хочется есть все конфеты. Вы хотите избежать преобразования, которое может непреднамеренно вызывать ошибки обновления. Некоторые операции, например перемещение столбцов в другое место таблицы, не должны приводить к ошибкам в обновлении, так как Power Query отслеживает столбцы по их именам.
Другие операции могут привести к ошибкам обновления. Одним из общих правил может быть ваш световой свет. Не внося существенных изменений в исходные столбцы. Чтобы безопасно воспроизвести столбец, скопируйте исходный столбец с командой(Добавитьстолбец, Настраиваемый столбец, Дублировать столбец и так далее), а затем внести изменения в скопированную версию исходного столбца. Вот операции, которые иногда могут привести к ошибкам обновления, и некоторые из лучших методик, которые помогут ухладить работу.
Повышение эффективности за счет максимально ранней фильтрации данных в запросе и удаления ненужных данных для уменьшения лишней обработки. Кроме того, с помощью автофильтра можно искать или выбирать определенные значения, а также использовать фильтры для определенных типов, доступные в столбцах даты, даты и времени и времени (например, Месяц,Неделя,День).
Типы данных и заглавные колонок столбцов
Power Query автоматически добавляет в запрос два шага сразу после первого шага: "Продвиганые заглавные колонок", которая преобразует первую строку таблицы в заглавный, и Changed Type(Измененный тип), который преобразует значения из типа Данных Any в тип данных на основе проверки значений из каждого столбца. Это удобно, но иногда может потребоваться явно контролировать это поведение, чтобы предотвратить ошибки случайного обновления.
Дополнительные сведения см. в статьях Добавление и изменение типов данных и Повысить или понизить их в строках и столбцах.
Переименование столбца
Избегайте переименования исходных столбцов. Используйте команду Переименовать для столбцов, добавленных другими командами или действиями.
Разделить столбец
Разделение копий исходного столбца, а не исходного столбца.
Объединение столбцов
Объединять копии исходных столбцов, а не исходных.
Удаление столбца
Если нужно сохранить небольшое количество столбцов, используйте выбор столбца, чтобы сохранить нужные.
Рассмотрим разницу между удалением столбцов и удалением других столбцов. Когда вы удаляете другие столбцы и обновляете данные, новые столбцы, добавленные в источник данных после последнего обновления, могут остаться незащищенными, так как они будут считаться другими столбцы при повторном выполнении в запросе шага Удалить столбец. Такая ситуация не возникает при явном удалите столбец.
Наконечник Скрыть столбец (как в Excel) не Excel. Однако если у вас много столбцов и вы хотите скрыть многие из них, чтобы сосредоточиться на своей работе, вы можете сделать следующее: удалить столбцы, запомнить созданный шаг, а затем удалить его перед загрузкой запроса обратно на таблицу.
Дополнительные сведения см. в статье Удаление столбцов.
Замена значения
При замене значения источник данных не редактируется. Вместо этого нужно изменить значения в запросе. При следующем обновлении данных ищемые значения могут немного измениться или перестать быть там, поэтому команда Заменить может не работать так, как планировалось изначально.
Дополнительные сведения см. в области Замена значений.
Pivot и Unpivot
При использовании команды Столбец сводной сводной столбца при сводном столбце может возникнуть ошибка, при этом не агрегируются значения, но возвращается больше одного значения. Такая ситуация может возникнуть после операции обновления, которая меняет данные несмежным образом.
Используйте команду Открепить другие столбцы, если известны не все столбцы и вы хотите, чтобы новые столбцы, добавленные во время обновления, также были неотвечены.
Используйте команду Открепить только выбранный столбец, если вы не знаете количество столбцов в источнике данных и хотите, чтобы выбранные столбцы оставались неотвеченными после обновления.
Дополнительные сведения см. в статьях Сводные столбцы и Ото всех столбцов.
Предотвращение ошибок Если внешним источником данных управляет другая группа в организации, им необходимо знать о вашей зависимости от них и избегать изменений в их системах, которые могут привести к проблемам ниже. Фиксировать влияние на данные, отчеты, диаграммы и другие артефакты, которые зависят от данных. Настройте линии связи, чтобы убедиться в том, что они понимают последствия, и примите необходимые меры, чтобы обеспечить бесперебойную работу. Находите способы создания элементов управления, которые минимизируют ненужные изменения и предугадать последствия необходимых изменений. Конечно, это легко сказать и иногда сложно сделать.
Future-proof with query parameters Используйте параметры запроса для уменьшения изменений, например расположения данных. Вы можете создать параметр запроса, чтобы заменить новое расположение, например путь к папке, имя файла или URL-адрес. Существуют и другие способы уменьшения проблем с помощью параметров запроса. Дополнительные сведения см. в теме Создание запроса с параметрами.
Аналогично тому, как в Excel и языке DAX есть IFERROR функция, Power Query имеет собственный синтаксис для тестирования и перехвата ошибок.
Как упоминалось в статье об ошибках в Power Query, ошибки могут отображаться на уровне шага или ячейки. В этой статье основное внимание уделяется перехвату ошибок и управлению ими на основе собственной логики.
Чтобы продемонстрировать эту концепцию, в этой статье будет использоваться книга Excel в качестве источника данных. Представленные здесь понятия применяются ко всем значениям в Power Query, а не только к тем, которые поступают из книги Excel.
Применение условной логики на основе ошибок
Пример источника данных для этой демонстрации — книга Excel со следующей таблицей:
Обратите внимание, что ошибки из книги Excel отображаются со значением [Error] в каждой ячейке.
В этом случае цель состоит в создании нового столбца "Окончательная ставка ", который будет использовать значения из столбца "Стандартная ставка ". При возникновении ошибок он будет использовать значение из столбца "Специальный тариф " корреспондента.
Добавление настраиваемого столбца с синтаксисом try otherwise
Чтобы создать новый настраиваемый столбец, перейдите в меню "Добавить столбец " и выберите "Настраиваемый столбец". В окне "Настраиваемый столбец" введите формулу try [Standard Rate] otherwise [Special Rate] . Присвойт этому новому столбцу окончательную ставку.
Приведенная выше формула попытается оценить столбец "Стандартная ставка" и выдаст его значение, если ошибки не найдены. Если ошибки найдены в столбце "Стандартная ставка ", выходные данные будут значениями, определенными после инструкции otherwise , которая в данном случае является столбцом "Специальная ставка ".
После добавления правильных типов данных во все столбцы таблицы на следующем рисунке показано, как выглядит окончательная таблица.
Перехват ошибки и try применение пользовательской условной логики
Одновременно можно выделить только одну ячейку, чтобы можно было эффективно видеть только компоненты ошибки одного значения ошибки. Здесь вы создадите новый настраиваемый столбец и используйте try выражение.
Добавление настраиваемого столбца с синтаксисом try
Чтобы создать новый настраиваемый столбец, перейдите в меню "Добавить столбец " и выберите "Настраиваемый столбец". В окне "Настраиваемый столбец" введите формулу try [Standard Rate] . Присвойт этому новому столбцу имя "Все ошибки".
Выражение try преобразует значения и ошибки в значение записи, указывающее, обрабатывает ли try выражение ошибку или нет, а также правильное значение или запись ошибки.
Вы можете развернуть этот вновь созданный столбец со значениями записей и просмотреть доступные поля для развертывания, щелкнув значок рядом с заголовком столбца.
Эта операция предоставит три новых поля:
- Все ошибки.HasError — показывает, было ли значение из столбца "Стандартная ставка " ошибкой.
- Все ошибки.Значение— если значение из столбца "Стандартная ставка " не содержит ошибок, этот столбец будет отображать значение из столбца "Стандартная ставка ". Для значений с ошибками это поле будет недоступно, и во время операции развертывания этот столбец будет иметь null значения.
- Все ошибки.Error — если значение из столбца "Стандартная ставка " содержит ошибку, в этом столбце отображается запись об ошибке для значения из столбца "Стандартная ставка ". Для значений без ошибок это поле будет недоступно, и во время операции развертывания этот столбец будет иметь null значения.
Для дальнейшего изучения можно развернуть столбец All Errors.Error , чтобы получить три компонента записи об ошибке:
Добавление условного столбца
После сохранения только столбцов "Учетная запись", "Стандартная ставка", " Специальная ставка" и "Окончательная ставка " и добавления правильного типа данных для каждого столбца на рисунке ниже показано, как выглядит окончательная таблица.
Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.
Таблицы Excel
Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.
Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.
Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.
Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.
Запросу присваивается имя таблицы Excel, которая является источником данных. Поэтому желательно сразу дать таблице говорящее название. Не обязательно, конечно, но желательно. В противном случае рекомендуется переименовать сам запрос, чтобы затем его можно было легко найти среди других запросов книги.
Данные находятся в Power Query. Новые значения, внесенные в исходную таблицу, автоматически попадут в запрос после его обновления. Далее в редакторе Power Query делают обработку данных и выгружают либо в виде таблицы Excel, либо оставляют в памяти Excel в виде подключения.
Именованный диапазон Excel
Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.
Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.
Здесь перечислены все именованные диапазоны, формулы и таблицы. Среди них есть и только что созданный Отчет.
Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.
Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.
Динамический именованный диапазон Excel
Решить данную проблему можно, создав динамический именованный диапазон. Это такой диапазон, который задается формулой и автоматически расширяется до последней заполненной ячейки.
Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:
Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.
Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.
Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.
Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:
После ввода формулы (нажатием Enter) Power Query обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.
Название запроса не подхватывается, поэтому придется изменить самостоятельно.
Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.
Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.
Теперь в запрос будут попадать новые строки, несмотря на то, что исходные данные не являются таблицей Excel.
Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.
Консолидация данных из разных таблиц Excel
Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.
Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.
Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос
Затем в строке формул вводим знакомую команду
Power Query показывает все таблицы в текущей книге.
Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).
Если есть лишние столбцы, то их можно не выводить, сняв соответствующую галку. Также лучше убрать галку напротив опции Использовать исходное имя столбца как префикс. Нажимаем Ok.
Все таблицы находятся на одном листе, а рядом колонка с названием источника, откуда взята каждая строка.
Данные загружены. Можно приступать к их обработке. Ограничимся преобразованием названий таблиц в настоящую дату, чтобы затем использовать для сведения данных по месяцам.
Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.
Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.
В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.
Подчеркивание удаляется из названия.
Поиск и замена здесь работает так же, как и в обычном Excel.
Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.
Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.
Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.
Главная → Закрыть и загрузить.
Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).
Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.
Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!
При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.
Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.
Снова выгружаем таблицу в Excel и на этот раз все в порядке.
Сделаем с помощью сводной таблицы маленький отчет по месяцам.
Прошло время, и в файл добавили новую таблицу с продажами за апрель.
Требуется обновить сводный отчет. Представьте на минуту, как это происходит в обычном Эксель: таблица копируется в самый низ общего источника, продлевается колонка с датой, изменяется диапазон для сводной таблицы, обновляется весь отчет.
А вот, как это выглядит при использовании Power Query.
Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).
На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.
Вот за это мы так любим Power Query.
Power Query – это инструмент MS Excel, предназначенный для импорта из самых различных источников и обработки данных. Впервые появился в 2013 году и был доступен в виде специальной надстройки, которую и сейчас можно скачать с официального сайта Microsoft и установить на Excel 2010-2013. После установки и подключения на ленте Excel появится соответствующая вкладка.
Далее будем использовать привычное название Power Query.
На самом деле в Excel и раньше можно было импортировать данные. Для этого в той же вкладке Данные была и есть целая группа команд Получение внешних данных.
Однако их возможности и удобство использования сильно ограничены.
После появления Power Query в среде пользователей Excel произошло потрясение, сравнимое с появлением сводных таблиц. Это не шаг, а прыжок вперед, благодаря которому любой аналитик (и обычный пользователь Excel), имеющий дело с большими и обновляемыми данными из разных источников, может ускорить свою работу в десятки раз. Да, в десятки, если не в сотни. Ведь как раньше делался, скажем, отчет? Импортируются данные (из разных источников), очищаются, связываются вместе с помощью формул типа ВПР, затем делаются необходимые расчеты, все агрегируется с помощью сводных таблиц в краткий отчет. Периодически эти действия нужно повторять, т.к. традиционными методами (без VBA) очень трудно автоматизировать все шаги. Сегодня этому кошмару пришел конец. В Power Query достаточно один раз все настроить и далее все операции импорта, обработки и выгрузки данных повторяются нажатием одной кнопкой обновления.
Power Query работает на специальном языке программирования под названием M, с помощью которого записываются последовательные шаги обработки данных. Однако есть и пользовательский редактор с кнопками, поэтому быть программистом не обязательно. Здесь уместна аналогия с записью обычных макросов. Включили запись, произвели действия, закончили запись. В любой момент запустили выбранный макрос.
Вкратце алгоритм работы Power Query таков:
1. импорт данных из выбранных источников данных
2. обработка полученных данных
Список возможных источников довольно разнообразный: от текстовых файлов до внешних баз данных и интернета. Также можно легко присоединиться к данным внутри самого MS Excel.
На этапе обработки производят операции по очистке, связыванию, группировке, математическому преобразованию и т.д. Специфика работы именно с такими, плохо организованными и неочищенными данными, объясняет набор инструментов Power Query. Частично они повторяют то, что есть в Excel, но есть и новые, которые значительно расширяют привычный функционал Эксель. Важнейшей особенности работы в Power Query является то, что все шаги записываются. Это дает возможность затем нажатием одной кнопки повторить все операции. Объединяя возможность подключения к данным внутри Excel и новые методы их обработки, мы получаем дополнительные инструменты, которые делают работу в Excel удобнее и быстрее.
На последнем этапе запроса обработанные данные выгружаются в указанное место либо создается только соединение (часто запросы – это только промежуточный этап обработки данных). Но об этом в другой раз.
В качестве наглядного примера рассмотрим следующую задачу. Имеются данные, которые нужно транспонировать, то есть строки сделать столбцами, а столбцы строками.
В целом это не проблема, т.к. в Excel существует минимум два способа транспонирования.
Первый и самый быстрый способ – воспользоваться Специальной вставкой, поставив галочку напротив опции транспонировать.
Отличный вариант, но одноразовый. В смысле, нет никакой связи между результатом и источником. Поэтому при любом изменении данных все нужно повторить снова. Это минус.
Второй способ транспонирования – воспользоваться функций ТРАНСП. Это формула массива, поэтому для ее вставки нужно вначале указать точный диапазон и ввести с помощью комбинации Ctrl + Shift + Enter.
Теперь при изменении данных в источнике автоматически обновится и транспонированный диапазон. Но здесь также есть серьезные недостатки. Во-первых, для вставки формулы ТРАНСП нужно заранее подсчитать, сколько строк и столбцов занимает диапазон, что, мягко говоря, не всегда удобно. Во-вторых, при изменении размера диапазона механизм перестает работать, т.к. транспонированный диапазон зафиксирован и не будет расширяться вслед за источником.
Итого получается, что мы не можем сделать динамическое транспонирование данных в изменяющемся диапазоне. Так да не так. С появлением Power Query задача решается быстро, без шума и пыли.
Транспонирование таблицы средствами Power Query
Первым делом нужно сделать запрос на источник данных. Нас интересуют данные из этой же книги Excel. Power Query не видит адреса обычных ячеек, а только именованные диапазоны и Таблицы Excel. Как правило, используют Таблицы Excel. Для преобразования обычного диапазона в таблицу рекомендую горячую комбинацию клавиш Ctrl + T.
Открывается окно редактирования Power Query.
Выглядит, как другая программа, но это только отдельное окно внутри Excel. Интерфейс состоит из пяти частей:
1. Инструменты редактирования – лента, на которой находятся команды Power Query.
2. Строка формул – здесь записывается код языка М для выделенного в данный момент шага обработки.
3. Запросы – скрываемая панель для навигации между запросами текущей книги.
4. Панель результата – место, где отображается результат обработки данных на этапе выделенного шага.
5. Параметры запроса – панель с названием запроса (можно изменять) и перечнем созданных шагов, которые также можно редактировать.
Выделив любой из шагов, мы увидим состояние данных на соответствующем этапе.
Название запроса лучше всего изменить на более говорящее. Довольно часто в книге используют сразу несколько запросов, поэтому в них нужно ориентироваться. Назовем «Транспонирование».
Из предыдущего рисунка видно, что мы еще ничего не сделали, а два шага уже записаны. Как так? Все просто. Первый шаг – это обращение к источнику, а второй автоматическое определение типа данных у каждого столбца. Поэтому все в порядке.
Вернемся к условию задачи. Нужно транспонировать вот эту таблицу.
Обратим внимание, что заголовки перенеслись из заголовков Таблицы Excel, которую мы использовали в качестве источника. Однако транспонирование происходит без заголовков. Поэтому, чтобы избежать потери названий столбцов, «опустим» их названия в первую строку таблицы Преобразование – Таблица – Использовать первую строку в качестве заголовков – Использовать заголовки как первую строку.
Таблица с данными получит такой вид.
Теперь можно транспонировать. Используем команду Преобразование – Транспонировать.
Таблица мгновенно изменяется.
Сделаем первую строку назад заголовками. Можно через Преобразование – Таблица — Использовать первую строку в качестве заголовков либо через кнопку в верхнем левом углу от таблицы.
Получим конечный результат обработки.
Задача решена. Все шаги преобразования данных записаны и видны справа.
Осталось измененные данные вернуть в Excel с помощью команды Главная – Закрыть – Закрыть и загрузить.
Если ее нажать, то результат загрузится на новый лист эксель и будет представлять из себя Таблицу Excel с названием, как у запроса. Но давайте пока зайдем в раскрывающийся список, чтобы посмотреть опции выгрузки. В раскрывающемся списке выберем Закрыть и загрузить в… Откроется следующее окно.
Если выбрать Только создать соединение, выгрузки не произойдет. Такой вариант применяют, если требуется дальнейшая обработка или использование этого запроса. Для выгрузки в Excel можно выбрать Новый лист либо указать конкретный диапазон. Если установить галочку Добавить эти сведения в модель данных, то результат запроса даже без выгрузки в Excel можно будет использовать в модели данных или Power Pivot. Этот вариант позволяет обрабатывать миллионы (миллионы!) строк, т.к. на обработку данных в памяти требуется гораздо меньше ресурсов. Оставляем все по умолчанию и жмем Загрузить. В процессе выгрузки таблица имеет серенький цвет, а когда выгрузка завершена, становится зелененькой.
Вот и все, дело сделано, мы получили транспонированную таблицу исходных данных.
Самое интересное происходит далее. Если добавить новые данные, то для повторения всех действий достаточно обновить запрос через правую кнопку в панели запросов (см. чуть ниже), либо во вкладке Данные – Подключения – Обновить все.
Добавим в исходную таблицу данные о продажах во втором квартале.
А теперь обновим запрос.
Это просто праздник какой-то! (с).
Обратим внимание, что справа в окне Excel появляется панель для управления существующими запросами.
Их может быть много, но у нас только один. Сразу под названием видно, сколько загружено строк. Здесь же указываются ошибки, если они есть. Это важно для контроля. Если подвести курсор мыши к названию, то откроется окно с кратким описанием запроса и командами управления снизу.
Можно вновь войти в редактирование запроса, удалить его и т.д. Эти же и некоторые другие команды появятся в контекстном меню после кликания по названию запроса правой кнопкой мыши.
Перечислим наиболее часто используемые среди них.
Изменить – команда открытия окна редактирования. Эквивалентно двойному нажатию левой кнопки мыши по самому запросу.
Обновить – обновление выбранного запроса (если нужно обновить только один запрос, а не все).
Загрузить в… – изменение места загрузки (в таблицу, модель или создания только соединения)
Дублировать – сделать копию выбранного запроса.
Другие команды не менее важны, но их рассмотрим в другой раз.
Итак, мы узнали, что такое Power Query. На примере транспонирования данных увидели, насколько он облегчает и ускоряет работу в Excel.
Читайте также: