Не работает power query в excel

Обновлено: 04.07.2024

Эта статья вскоре будет обновлена с учетом новой терминологии.

При использовании Power Query для Excel для создания настраиваемой таблицы с данными из внешних источников, эта ошибка может появиться:

"Администратор Azure Active Directory установил политику, которая не позволит вам использовать эту функцию. Обратитесь к администратору, который может предоставлять разрешения для этой функции от вашего лица."

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

Чтобы устранить эту проблему, администратор Azure Active Directory должен выполнить любую из этих процедур, представленных ниже в этой статье.

Разрешение пользователям давать согласие приложениям, которые получают доступ к данным компании

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

На портале Azure откройте панель Azure Active Directory, затем выберите Параметры пользователя.
Рядом с пунктом Пользователи могут давать приложениям разрешение на доступ к данным компании от их имени выберите Да, затем выберите Сохранить.

Разрешение Power Query получать доступ к данным компании

Альтернативно администратор клиента может предоставить согласие Power Query без изменения разрешений на уровне всего клиента.

Установите Azure PowerShell.
Выполните следующие команды PowerShell:
- Login-AzureRmAccount (и войдите в систему как администратор клиента)
- New-AzureRmADServicePrincipal -ApplicationId f3b07414-6bf4-46e6-b63f-56941f3f4128

Преимущество этого подхода (по сравнению с решением в масштабах всего клиента) заключается в том, что это очень направленное решение. Оно подготавливает только участника-службу Power Query, но не изменяет никакие другие разрешения в клиенте.

Обновление личных данных

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

В Power Apps вы получаете доступ к редактору запросов, выполнив следующие действия.

Перейдите на панель Данные, разверните ее, затем выберите Таблицы.
Выберите многоточие (. ), затем выберите Изменить запросы.
На ленте выберите кнопку Параметры, затем выберите кнопку Экспорт диагностики.

Удаление личных данных

Большинство данные будут удалены автоматически в течение 30 дней. Для данных и метаданных о гибридных веб-приложениях пользователи должны удалять все свои гибридные веб-приложения через Power Apps. Все связанные данные и метаданные будут удалены в течение 30 дней.

Чтобы удалить гибридные веб-приложения из Power Apps:

Удалите проекты интегратора данных, которые могут быть удалены с одноименной вкладки.
Выберите многоточие (. ), затем выберите параметр Удалить.

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

Выберите многоточие (. ), затем выберите Изменить запросы.
На ленте выберите кнопку Параметры.
Выберите кнопку Удалить все запросы.
После подтверждения, что требуется удалить запросы, они удаляются.

Экспорт личных данных

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

Откройте редактор запросов.
На ленте выберите кнопку Параметры.
Выберите кнопку Экспорт диагностики.

В Power Apps вы можете получить доступ к редактору запросов, выполнив следующие действия.

Перейдите на панель Данные, разверните ее, затем выберите Таблицы.
Выберите многоточие (. ), затем выберите Изменить запросы.
На ленте выберите кнопку Параметры, затем выберите кнопку Экспорт диагностики.

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

При обновлении данных могут возникать ошибки двух типов.

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

Не удалось подключиться к службе или ресурсу. Проверьте подключение.

Не удалось найти файл, к который вы пытаетесь получить доступ.

Сервер не отвечает и, возможно, находится в состоянии обслуживания.

Это содержимое не доступно. Возможно, он был удален или временно недоступен.

Подождите. данные загружаются.

Получить сведения о справке На сайте 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-адрес. Существуют и другие способы уменьшения проблем с помощью параметров запроса. Дополнительные сведения см. в теме Создание запроса с параметрами.

В Power Query можно столкнуться с ошибками двух типов:

Ошибки на уровне шага
Ошибки на уровне ячеек

Ошибка на уровне шага

Ошибка на уровне шага предотвращает загрузку запроса и отображение компонентов ошибок в желтой области.

Распространенные ошибки на уровне шага

Не удается найти источник источника данных. ошибка

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

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

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

Столбец таблицы не найден

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

Пример. имеется запрос из текстового файла, в котором одно из имен столбцов имело значение Column. В запросе имеется шаг, который переименовывает столбец в значение Date. Но в исходном текстовом файле были внесены изменения, и он больше не содержит заголовок столбца с именем, так как он был вручную изменен на " Дата". Power Query не удается найти заголовок столбца с именем Column, поэтому он не может переименовать столбцы. Отображается ошибка, показанная на следующем рисунке.

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

Другие распространенные ошибки на уровне шага

При объединении или объединении данных между несколькими источниками данных может появиться Формула. ошибка брандмауэра , как показано на следующем рисунке.

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

Ошибка на уровне ячейки

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

Средства профилирования данных позволяют легко определить ошибки на уровне ячеек с помощью функции «качество столбца». Дополнительные сведения: средства профилирования данных

Обработка ошибок на уровне ячейки

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

В следующих разделах приведенные примеры будут использовать тот же образец запроса, что и начальная точка. В этом запросе имеется столбец Sales , содержащий одну ячейку с ошибкой, вызванной ошибкой преобразования. Значение в этой ячейке равно " НД", но при преобразовании этого столбца в целое число Power Query не удалось преобразовать НД в число, поэтому отобразится следующая ошибка.

Удалить ошибки

Чтобы удалить строки с ошибками в Power Query, сначала выберите столбец, содержащий ошибки. На вкладке Главная в группе уменьшить строки выберите Удалить строки. В раскрывающемся меню выберите пункт удалить ошибки.

В результате этой операции вы получите таблицу, которую вы ищете.

Заменить ошибки

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

В диалоговом окне Замена ошибок введите значение 10 , так как необходимо заменить все ошибки значением 10.

В результате этой операции вы получите таблицу, которую вы ищете.

Не учитывать ошибки

Power Query может служить хорошим средством аудита для обнаружения любых строк с ошибками, даже если эти ошибки не устранены. Именно здесь могут быть полезны ошибки сохранения . Чтобы избежать возникновения ошибок в строках, сначала выберите столбец, содержащий ошибки. На вкладке Главная в группе сокращение строк выберите параметр не учитывать строки. В раскрывающемся меню выберите пункт не учитывать ошибки.

В результате этой операции вы получите таблицу, которую вы ищете.

Распространенные ошибки на уровне ячеек

Ошибки преобразования типов данных

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

Пример. у вас есть запрос, который содержит столбец с именем Sales. Одна ячейка в этом столбце содержит значение « НД » в качестве значения ячейки, а в остальных — целые числа. Вы решили преобразовать тип данных столбца из текстового в целое число, но ячейка со значением НД вызывает ошибку.

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

Ошибки операций

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

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

Возможные решения. перед созданием этого пользовательского столбца измените тип данных столбца Sales на Text.

В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.

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

Например, каждый месяц вы получаете таблицу с ответами на вопросы теста сотрудников компании, где вы работаете в таком виде:

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

Решение

Запуск Power Query

Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.

Добавить кнопки Power Query на панель быстрого доступа

Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:

Изменить запрос
Запросы и подключения
Запустить редактор запросов

Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.

Предварительные настройки Power Query

Откройте редактор запросов любым удобным способом:

Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.

Создаем первый запрос Power Query

Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:

Перед вам появится следующее окно редактора запросов Power Query:

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

Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.

По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.

Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.

Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!

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