Столбец сведения power query не агрегировать не работает

Работа с ошибками в Power Query

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

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

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

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

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

• Причина ошибки: первый раздел перед двоеточием. В приведенном выше примере причина ошибки — expression. Error.
• Сообщение об ошибке: раздел непосредственно после причины. В приведенном выше примере сообщение об ошибке — столбец «Column» таблицы не найден.
• Сведения об ошибке: раздел сразу после строки Details: String. В приведенном выше примере сведения об ошибке — Column.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Агрегированные данные из столбца (Power Query)

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

Предварительный просмотр данных таблицы «Заказы» в веб-канале OData «Northwind»

В этом примере агрегируется столбец Order_Details из таблицы «Заказы Northwind». Данные импортируется из образца веб-канала OData Northwind.

В Excel выберите Главная> новый источник > другие источники > из канала OData.

В диалоговом окне Канал OData введите URL-адрес канала OData Northwind.

В области Навигатор выберите таблицу Orders (Заказы) в левой области, данные будут предварительно просмотреться справа, а затем выберите Transform Data (Преобразовать данные).

Появится редактор Power Query.

В режиме предварительного просмотра данных прокрутите список вправо и щелкните значок рядом со Order_Details данными.

Таблица — это пример структурированного столбца, который можно развернуть, чтобы увидеть

больше столбцов. Дополнительные сведения см. в статье Работа со столбцом со структурой списка,записи или таблицы.

В списке Столбец таблицы:

Выберите параметр Агрегировать.

Наведите курсор на агрегатный элемент функции, например Агрегаты UnitPrice.

В списке агрегатных функций выберите одну или несколько агрегатных функций. Например, Sum и Average.

Power Query расширяет столбец Order_Details, заменяя его значениями «Сумма UnitPrice» и «Среднее значение unitPrice».

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

В области Параметры запроса в области Примененные шаги выберите Источник , а затем Параметры изменения. Появится диалоговое окно Источник данных, которое зависит от внешнего источника данных. В этом примере это диалоговое окно Канал OData. Заметьте этот источник данных.

Выберите Главная > последниеисточники , а затем выберите источник данных из списка, который соответствует тому, что вы заме добавили на шаге 1. Появится диалоговое окно Навигатор.

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

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

Источник

Столбцы pivot (Power Query)

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

При своде вы берете два исходных столбца и создаете новую пару атрибута и значения, которая представляет точку пересечения новых столбцов:

Атрибуты Одинаковые значения из одного столбца исходных атрибутов (в зеленом цвете слева) будут совмещены в несколько новых столбцов (в зеленом цвете справа).

Значения из исходного столбца без свербов (синего цвета слева) распределяются по соответствующим новым столбцам в качестве значений (синим цветом справа).

Вот пример данных, используемых в этой процедуре.

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

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

Выберите преобразовать > столбец .

В диалоговом окне Столбец pivot в списке Столбец значений выберите Amt.

Выберите Дополнительные параметры, а затем выберите функцию Агрегированного значения. В примере выберите Сумма.

Доступны такие параметры: Don’t aggregate, Count (all), Count (не пусто), Minimum, Maximum , Median, Sumи Average.

Совет Если вы не хотите подсчитывать ячейку без значения, выберите элемент Количество (не пустая). Например, если в столбце десять ячеек и две пустые, то количество будет 8.

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

Вот пример данных, используемых в этой процедуре.

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

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

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

Выберите преобразовать > столбец .

В диалоговом окне Столбец pivot в списке Столбец значений выберите Product (Товар).

Выберите Дополнительные параметры, а затем выберите функцию Агрегированного значения. В примере выберите не агрегировать.

Столбец «Положение» из исходной таблицы высвещен для получения значений из исходного столбца «Товар» на пересечении столбца «Страна» и каждого нового столбца «Положение».

В этом примере мы используем пример из раздела «Сводка и агрегировать столбец»:

Если вы сводные данные в столбце Date, выберите Amt в списке Столбец значений, а затем в дополнительных параметрах выберитеНе агрегировать для функции агрегированного значения ,вы получите следующее сообщение об ошибке:

«Expression.Error: В переименовке было слишком много элементов, чтобы завершить операцию».

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

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

Источник