Ошибка power query expression error

RLL Пользователь Сообщений: 19 Регистрация: 08.05.2018	Всем привет. Возникает ошибка в запросе. Файл по ссылке: пример Софт Excel 2013 Power Query. В чем причина?

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI

#2 25.10.2018 12:53:46 Не только лишь все могут выгрузить ваш файл в рабочее время, мало кто может это сделать. Если файл такой тяжелый, то покажите хотя бы код запроса, скриншот ошибки и шаг на котором возникает эта ошибка. Также рекомендую предложить новое название темы с текстом ошибки, данное название слишком обобщенное и не очень удовлетворяет правилам форума. Если под ошибкой подразумевается текст из предыдущей закрытой темы  Power Query.Expression.Error: Не удается преобразовать значение 8121530545 в тип Text То у вас проблема в том, что вы в какой-то формуле пытаетесь объединить числовые значения с текстовыми. Скорее всего вы это делаете при помощи знака «&». PQ не умеет на лету преобразовывать типы данных, поэтому он вам ругается. Перед шагом, на котором вы производите объединение нужно изменить тип данных на текстовый, либо в формуле с объединением вместо числового значения, например [СтолбецСЧислами], нужно писать Код Text.From( [СтолбецСЧислами] ) Изменено: PooHkrd — 25.10.2018 13:13:40 Вот горшок пустой, он предмет простой…

Anton555 Пользователь Сообщений: 699 Регистрация: 16.08.2018

#3 25.10.2018 14:33:42 вам выше все правильно сказали Код let     Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],     #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Периметр", type text}}),     #"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(#"Измененный тип", {{"Периметр", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Периметр") in     #"Разделить столбец по разделителю" Изменено: Anton555 — 25.10.2018 14:33:50

Anton555 Пользователь Сообщений: 699 Регистрация: 16.08.2018

#4 25.10.2018 14:49:31 чтобы подсократить кол-во строк Код let     Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],     #"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([Периметр] <> null)),     #"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Строки с примененным фильтром",{{"Периметр", type text}}), {{"Периметр", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Периметр") in     #"Разделить столбец по разделителю" и для чего вы такой большой диапазон для «таблицы1» выбрали? с А1 и до А1ххххххх ? Изменено: Anton555 — 25.10.2018 14:49:39

RLL Пользователь Сообщений: 19 Регистрация: 08.05.2018	Так как у меня нет опыта в программировании, вы не могли бы подробнее разъяснить свои действия?

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI	Откройте ваш запрос, перейдите на второй по счету шаг с названием «Измененный тип» потом нажмите на кнопку в левом верхнем углу столбца как на скриншоте и выберите тип данных для столбца «Текст». Все должно наладиться. Изменено: PooHkrd — 25.10.2018 16:15:41 Вот горшок пустой, он предмет простой…

Anton555 Пользователь Сообщений: 699 Регистрация: 16.08.2018	#7 25.10.2018 17:00:18 без этой строки Код #"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([Периметр] <> null)), не наладится, т.к. таблица1 имеет диапазон до самого конца листа и будет выдавать ошибку  — что-то типа excel не может загрузить все строки, т.к. они не помещаются в диапазон листа

RLL Пользователь Сообщений: 19 Регистрация: 08.05.2018	Спасибо всем, я так и сделал. Администратор, возможно ли удалить файл, так как в нем содержатся личные сведения?

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	Удалил, но боты не дремлют, страница уже могла быть проиндексирована… И здесь-то удалено, а сами по ссылке файл оставили. Сколько людям пишем — готовьте файлы-примеры. А лень,  рабочие файлы показывают…

RLL Пользователь Сообщений: 19 Регистрация: 08.05.2018

КБМ Пользователь Сообщений: 16 Регистрация: 17.02.2018	#11 07.05.2021 15:35:03 Цитата RLL написал: Возникает ошибка в запросе. У меня подобная ошибка вышла даже при типе данных всех столбцов текст. Причина оказалась в ошибке в некоторых ячейках (error). Пришлось заменять ошибки на null, но это делать тоже надо аккуратно, — понимать что явилось причиной, что бы не потерять данные. Изменено: vikttur — 07.05.2021 15:46:11

Introduction:
In Power Query, it is very common to encounter various errors. In this article, we are going to show you some common scenarios related to the expression. error for data processing.

Examples:
1. Expression. Error: We cannot apply field access to the type XXX( Date, Number).

Scenario 1: Get a list larger than itself.
The Original table.

Error formula: List.Select( #"Changed Type"[value],each _> [value])

Right formula: let _a=[value] in List.Select( #"Changed Type"[value],each _> _a)

The cause:
[value] cannot be accessed within List, and we could fix this by storing [value] in a variable.
The result as follows:

2. Expression. Error: There weren’t enough elements in the enumeration to complete the operation.

Scenario 2: When we pivot the rows with the same name. (here we pivot [Attribute] column which have same name)
The original table:

Pivot column then will occur an error.

The cause:
When the rows with the same value are pivoted, there will be duplicated column names, which is a throwing error.
In this article, the error is understandable, because there are too many elements to
complete the operations.
Solutions:

a) Add [index] column by grouping &expanding it.

= Table.Group(#"Sorted Rows", {"Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table }})

b) Select [Attribute] column then click pivot column.

c) The result as follows:

3. Expression. Error: We cannot apply operator XXX to types XXX and XXX.

Scenario 3 [value] * 10 to form a new column.
Original table:

Error formula: [value]*10
Right formula: Number.FromText( [value])*10

The cause:
When the multiplication of numbers and numbers is used as the multiplication of text and numbers, that will leads to an error. Therefore, you should convert the text type to a numeric type .

The final show:

Hope this article helps everyone with similar questions.

Author:Yalan Wu

Reviewer: Kerry Wang & Ula Huang

Импорт «плавающего» фрагмента в Power Query

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

То есть мы заранее не можем с определенностью сказать, начиная с какой строки и сколько именно строк нужно импортировать. А это проблема, т.к. эти параметры жестко прописываются в М-коде запроса. И если сделать запрос по первому файлу (импорт 5 строк начиная с 4-ой), то он уже не будет правильно работать со вторым.

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

В основе решения, которое я хочу предложить, лежит идея, что в наших данных есть некие ключевые слова или значения, которые можно использовать как маркеры (признаки) начала и конца нужного нам блока данных. В нашем примере началом станет строка, начинающаяся со слова Артикул, а концом – строка со словом Итого. Такую проверку строк легко реализовать в Power Query с помощью условного столбца – аналога функции ЕСЛИ (IF) в Microsoft Excel.

Давайте посмотрим, как это сделать.

Сначала загрузим содержимое нашего текстового файла в Power Query стандартным способом – через команду Данные – Получить данные – Из файла – Из текстового / CSV-файла (Data – Get Data – From file – From text/CSV file) . Если Power Query у вас установлена как отдельная надстройка, то соответствующие команды будут на вкладке Power Query:

Как всегда, при импорте можно выбрать символ-разделитель столбцов (в нашем случае это табуляция), а после импорта можно убрать автоматически добавленный шаг Измененный тип (Changed Type) , т.к. нам еще рано назначать типы данных для столбцов:

Теперь с помощью команды Добавление столбца — Условный столбец (Add Column — Conditional Column) добавим столбец с проверкой двух условий – на начало и конец блока — и выводом любых различных значений в каждом случае (например чисел 1 и 2). Если ни одно из условий не выполняется, то выводим null:

После нажатия на ОК получим такую картину:

Теперь идем на вкладку Преобразование и выбираем команду Заполнить – Вниз (Transform – Fill – Down) – наши единички и двойки протянутся вниз по столбцу:

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

Останется только поднять первую строку в шапку командой Использовать первую строку в качестве заголовков на вкладке Главная (Home – Use First Row as Headers) и удалить ненужный более условный столбец, щелкнув по его заголовку правой кнопкой мыши и выбрав команду Удалить столбец (Delete Column) :

Задача решена. Теперь при изменении данных в исходном текстовом файле, запрос теперь будет самостоятельно определять начало и конец «плавающего» фрагмента нужных нам данных и импортировать каждый раз правильное количество строк. Само-собой, подобный подход работает и в случае импорта XLSX, а не TXT-файлов, а также при импорте сразу всех файлов из папки командой Данные — Получить данные — Из файла — Из папки (Data — Get Data — From file — From folder) .

Источник

Expression error не удается преобразовать значение null

Имеется хмл файл из которого необходимо извлечь таблицу для дальнейшей обработки в эксель. Есть столбцы с «вложенными таблицами» например: Table.ТехническиеХарактеристики, Table.ВесогабаритныеХарактеристики, Table.ПараметрыETIM.ПараметрыETIM. Раскрываю стандартно: нажимаю на 2 стрелочки в заголовке столбца. ОК.

Получается каша непригодная для обработки.

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

Имеется хмл файл из которого необходимо извлечь таблицу для дальнейшей обработки в эксель. Есть столбцы с «вложенными таблицами» например: Table.ТехническиеХарактеристики, Table.ВесогабаритныеХарактеристики, Table.ПараметрыETIM.ПараметрыETIM. Раскрываю стандартно: нажимаю на 2 стрелочки в заголовке столбца. ОК.

Получается каша непригодная для обработки.

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

Сообщение Имеется хмл файл из которого необходимо извлечь таблицу для дальнейшей обработки в эксель. Есть столбцы с «вложенными таблицами» например: Table.ТехническиеХарактеристики, Table.ВесогабаритныеХарактеристики, Table.ПараметрыETIM.ПараметрыETIM. Раскрываю стандартно: нажимаю на 2 стрелочки в заголовке столбца. ОК.

Получается каша непригодная для обработки.

Приложил пример с хмл файлом. В итоге необходима таблица в которой развернуты все столбцы за счет добавления новых столбцов. Автор — bikerlf
Дата добавления — 19.02.2021 в 19:26

Источник

Язык М Power Query. Типы logical, null и binary

Эта заметка завершает обзор примитивных типов языка М: текст, число, дата/время, логический, бинарный и null.[1]

Рис. 1. Выражение if возвращает логическое значение

Скачать заметку в формате Word или pdf, примеры в формате Excel

Логический

Тип logical хранит логические значения: true и false. Логический литерал можно получить преобразованием подходящей текстовой строки или числа. «false» преобразуется в false, «true» – в true. Число 0 – в false, любое другое число – в true.

Логический литерал также можно получить в результате сравнения или используя оператора if.

Листинг 1[2]

Листинг 2

Иногда логические значения не так очевидны, как true/false. Следующие пары 1/0, yes/no, Y/N, is something/is not something по сути, являются логическими значениями. Не позволяйте форме маскировать содержание значений. Если вы видите столбец, состоящий из подобных значений, преобразуйте его в тип logical. Как Power Query, так и Excel, работают лучше, когда тип столбца соответствует типу данных значений столбца.

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

Итак, возможны, по крайней мере, два способа обработки null. Поскольку нельзя поддерживать несколько вариантов поведения, разработчики языка должны выбрать один из них. В M прямые сравнения (= и <>), когда аргумент равен null, возвращают true / false:

Источник

Expression.Error: мы не можем преобразовать значение null в тип Text. Подробности: Значение = Тип = Тип

Я хочу объединить несколько файлов с разными заголовками, и я нашел это видео, которое идеально подошло. Я добрался до 12-й минуты, и это не удалось, и я получаю эту ошибку «Expression.Error: мы не можем преобразовать значение null в тип Text. Подробности: Value = Type = Type»

мой код для запроса мощности = Table.TransformColumnNames(stuff_Table, each List.Accumulate(Table.ToRecords(Headings), _ , (state, current)=>Text.Replace(Text.Upper(state), current [BEFORE],current[AFTER]) ))

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

Похоже, вы пытаетесь применить преобразование, которое принимает ТЕКСТ в качестве входных данных для данных типа NULL. Это означает, что PowerQuery ожидает текстового ввода, но ничего не получает. Вам необходимо убедиться, что он получает текстовый ввод (путем адаптации вашего источника и замены null на текстовое значение), или что ваша формула PowerQuery явно обрабатывает нулевые значения (обычно с помощью некоторого варианта «if field = null, затем« пустое »поле else «).

Text.Replace(Text.Upper(state) ==> Скорее всего, поле состояния равно NULL как минимум в одном экземпляре (одной строке) вашего источника. Может быть, файл шаблона Tableau?

Попробуйте заменить пустые поля на «null», «пустой», «без состояния» или даже на пробел «»). Это можно сделать либо вручную в источнике (преобразовать столбец или создать новый столбец), либо в PowerQuery.

Быстрый поиск в Google по запросу «Power query replace Null with text» дает ряд вариантов.

Источник

Регулярные выражения (RegExp) в Power Query

Если вы хотя бы чуть-чуть знакомы с регулярными выражениями, то рекламировать вам их не нужно. Если же вы не совсем в теме, то регулярные выражения (Regular Expressions = RegExp = «регэкспы» = «регулярки») — это язык, где с помощью специальных символов и правил производится поиск нужных подстрок в тексте, их извлечение или замена на другой текст. Это очень мощный и красивый инструмент, на порядок превосходящий по возможностям все остальные способы работы с текстом.

Я уже подробно и с кучей примеров из жизни описывал, как можно добавить поддержку регулярных выражений в Excel с помощью несложных макросов — если вы ещё не читали эту статью, то крайне рекомендую с ней ознакомиться прежде чем продолжать. Откроете для себя много нового, гарантирую 🙂

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

Встроенных функций для работы с RegExp’ами в Power Query, к сожалению, нет и официальная справка и техподдержка Microsoft отвечают на этот вопрос отрицательно. Однако, есть способ обойти это ограничение 🙂

Суть метода

Главная идея проста до безобразия.

В списке встроенных возможностей Power Query есть функция Web.Page. Описание этой функции на оф.сайте справки Microsoft предельно лаконично:

В переводе это будет: «Возвращает содержимое документа HTML, разбитого на составные структуры, а также представление полного документа и его текста после удаления тегов.» Так себе описание, прямо скажем.

Обычно эта функция используется при импорте данных из веба и автоматически подставляется, например, когда мы выбираем на вкладке Данные команду Из интернета (Data — From web) . Мы даём функции в качестве аргумента веб-страницу, а она возвращает нам её содержимое в виде таблиц, вычистив предварительно все теги.

Что в справке НЕ написано, так это то, что помимо языка разметки HTML функция Web.Page поддерживает скрипты на языке JavaScript, который сейчас повсеместно используется на веб-сайтах в интернете. А JavaScript, в свою очередь, всегда умел работать с регулярными выражениями и имеет встроенные функции для RegExp’ов! Так что для реализации регулярок в Power Query нам нужно будет скормить функции Web.Page в качестве аргумента маленькую программку на JavaScript, которая и сделает за Power Query всю работу.

Как это выглядит на чистом JavaScript

Подробных руководств по работе с регулярными выражениями на JavaScript в интернете — масса (например, раз, два).

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

• var str = ‘Оплата по счетам 123 и 789 за колбасу»; — создаем переменную str и присваиваем ей исходный текст, который будем анализировать.
• var pattern = /d+/gi; — создаем регулярное выражение и помещаем его в переменную pattern.
  Выражение начинается знаком слэш (/).
  Само выражение здесь, для примера, это d+ — обозначает любую последовательности цифр.
  Через дробь после выражения идут дополнительные параметры (модификаторы) поиска — их можно указывать в любом порядке:
  • g — означает глобальный поиск, т.е. после нахождения совпадения нужно не останавливаться, а продолжать поиск до конца текста. Если этот модификатор не задан, то наш скрипт выдаст только первое совпадение (123)
  • i — поиск без учёта регистра букв
  • m — многострочный поиск (применяется, когда исходный текст разбит на несколько строк)
• var result = str.match(pattern).join(‘;’); — выполняем поиск в исходном тексте (str) по заданному регулярному выражению (pattern) и помещаем результаты в переменную result, сцепив их через точку с запятой с помощью команды join
• document.write(result); — выводим на экран содержимое переменной result

Также обратите внимание, что текстовые строки (за исключением регулярного выражения) в JavaScript заключаются в апострофы, а не в кавычки, как в Power Query или VBA.

На выходе этот скрипт выдаст нам в качестве результата все числа, найденные в исходном тексте:

Краткий курс по JavaScript закончен, всем спасибо. Надеюсь, вы ухватили логику 🙂

Осталось перенести эту конструкцию в Power Query.

Функция поиска и извлечения текста по регулярному выражению в Power Query

1 . Открываем Excel и создаем новый пустой запрос Power Query на вкладке Данные — Получить данные / Создать запрос — Из других источников — Пустой запрос (Data — Get data / New query — From other sources — Blank query) . Если у вас старая версия Excel 2010-2013 и Power Query у вас не встроена, а была установлена как отдельная надстройка, то всё это будет на вкладке Power Query, а не Данные.

2 . В открывшемся пустом окне редактора запросов в правой панели сразу вводим имя нашей будущей функции (например, fxRegExpExtract)

3 . Идём на вкладку Просмотр — Расширенный редактор (View — Advanced Editor) , стираем весь М-код пустого запроса и вставляем туда код нашей суперфункции:

Следите за руками:

В первой строке мы говорим, что в нашей функции будет три текстовых аргумента: txt — исходный анализируемый текст, regex — шаблон регулярного выражения, delim — символ-разделитель для вывода результатов.

Далее вызываем функцию Web.Page, формируя у нее в аргументе описанный выше код на JavaScript. В код вклеиваем и подставляем наши аргументы-переменные.

. нужен, чтобы «провалиться» в нужную нам таблицу с результатами. Дело в том, что функция Web.Page в качестве результата выдает несколько вложенных друг в друга таблиц, повторяющих структуру веб-страницы. Без этого фрагмента М-кода наша функция выдавала бы на выходе это:

. и нам пришлось бы несколько раз щелкать мышью по слову Table , последовательно «проваливаясь» в дочерние вложенные таблицы в столбцах Children:

Вместо всей этой котовасии мы сразу в коде нашей функции указываем какая вложенная таблица и столбец (Text) нам нужны.

Вот, собственно, и всё секреты. Осталось нажать на кнопку Готово в окне Расширенного редактора, куда мы вставили наш код, и можно приступать к самому вкусному — пробовать нашу функцию в работе.

Вот вам пара примеров для затравки.

Пример 1. Извлекаем номер счета и дату из описания платежа

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

Грузим таблицу в Power Query стандартным образом через Данные — Из таблицы / диапазона (Data — From T able/R ange) .

Затем добавляем вычисляемый столбец с нашей функцией через Добавление столбца — Вызывать настраиваемую функцию (Add Column — Invoke Custom Function) и вводим её аргументы:

В качестве регулярного выражения (аргумент regex) используем шаблон:

. в переводе на человеческий язык означающий:

числа от 3 до 5 разрядов (номера счетов)

фрагменты вида «2-разрядное число — точка — 2-разрядное число — точка — 4-разрядное число» , то бишь даты вида ДД.ММ.ГГГГ.

В качестве символа-разделителя (аргумент delim) вводим точку с запятой.

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

Останется его разделить по точке с запятой с помощью команды Главная — Разделить столбец — По разделителю (Home — Split column — By delimiter) и мы получим то, что хотели:

Пример 2. Извлекаем адреса эл.почты из текста

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

. откуда нам нужно вытащить встречающиеся там адреса эл.почты (для наглядности я выделил их в тексте красным).

Как и в прошлом примере, грузим таблицу в Power Query стандартным образом через Данные — Из таблицы / диапазона (Data — From T able/R ange) .

Затем добавляем вычисляемый столбец с нашей функцией через Добавление столбца — Вызывать настраиваемую функцию (Add Column — Invoke Custom Function) и вводим её аргументы:

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

В качестве разделителя (delim) можно ввести точку с запятой и пробел.

Жмём на ОК и получаем столбец с извлеченными из исходной текстовой «каши» адресами эл.почты:

Как говорится: «нет такой хорошей вещи, которую нельзя было бы сделать ещё лучше». Power Query и сам-то по себе крут, а уж в сочетании с регулярными выражениями даёт нам совершенно нереальную мощь и гибкость в обработке любых текстовых данных. Надеюсь, Microsoft когда-нибудь добавит поддержку RegExp в обновлениях Power Query и Power BI и все описанные выше танцы с бубном останутся в прошлом. Ну, а пока — так.

Источник

1 Power Query Ошибки №1. Измененный тип, Неверная фильтрация в UI Почему возникает ошибка Expression.Error Столбец таблицы не найден? Одна из причин — шаг Измененный тип.
Почему в вашей итоговой таблицы не все данные, которые должны там оказаться? Вероятно вы совершили ошибку неверной фильтрации в пользовательском интерфейсе. 2 Power Query Ошибки №2. Фиксированная ширина столбца Excel В этом уроке вы узнаете как отключить автоматическое определение ширины столбцов таблицы Excel при обновлении запроса Power Query. 3 Power Query Ошибки №3. Formula Firewall, запрос ссылается на… Вы работаете в Power Query, но при попытке обновить все возникает ошибка Formula Firewall, запрос ссылается на… Что делать? Как исправить? 4 Power Query Ошибки №4. Доступ к веб-содержимому В этом уроке вы узнаете как избавиться от надоедливого окна доступ к веб-содержимому. 5 Power Query Ошибки №5. Количество столбцов CSV При импорте CSV получается неверное количество столбцов. 6 Power Query Ошибки №6. Не получается изменить тип данных (DataFormat.Error) При попытке изменить тип данных с текстового на дату возникает ошибка DataFormat.Error. 7 Power Query Ошибки №7. Разные имена листов В этом уроке мы решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. 8 Power Query Ошибки №8. Разные имена листов 2 В этом уроке мы другими способами решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. 9 Power Query Ошибки №9. Разные имена столбцов, Table.TransformColumnNames В этом уроке мы разберем проблему, когда нам присылают файлы, в которых всегда столбцы названы по-разному. 10 Power Query Ошибки №10. Как развернуть все столбцы В этой задаче мы научимся разворачивать все столбцы табличного столбца не перечисляя каждый из них хардкодом. 11 Power Query Ошибки №11. Подключиться к последнему файлу В этом уроке мы научимся подключаться к самому свежему файлу из нужной нам папки с нужным названием. 12 Power Query Ошибки №12. Консолидация и MissingField.Type Бывает вы ходите выполнить добавление таблиц друг под другом с предварительной обработкой, но получаете ошибку Expression.Error: Столбец таблицы не найден. 13 Power Query Ошибки №13. Удалить пустые столбцы Как без хардкода автоматически удалить пустые столбцы в Power Query, которые есть в импортируемой таблицы. Разберем 2 способа. Один из них прост до безобразия, а для второго понадобится функция Table.Profile. 14 Power Query Ошибки №14. Удалить лишние пробелы В этом уроке мы разберем еще 1 способ удалить лишние пробелы в текстовом столбце Power Query. Для этого мы повторим и изучим несколько новых функций.
Text.SplitAny
List.Select
Character.FromNumber
Text.Combine 15 Power Query Ошибки №15. Плохо структурированный TXT Как быть, если текстовый файл разбивается на столбцы неправильно? 16 Power Query Ошибки №16. При округлении не совпадает общая сумма Разберем ошибку, которая возникает при необходимости разбить число на определенные доли, а результаты округлить до двух знаков после запятой. В такой ситуации может получиться, что сумма слагаемых не будет равняться исходному числу. 17 Power Query Ошибки №17. Удаление дубликатов, Подводные камни Table. Distinct Что может пойти не так при использовании функции удаления дубликатов. 18 Power Query Ошибки №18. Удаление дубликатов 2, Подводные камни Table. Distinct Что может пойти не так при использовании функции удаления дубликатов. Другое решение данной проблемы. 19 Power Query Ошибки №19. Неверная разбивка на столбцы При импорте данных из CSV или TXT разбивка на столбцы происходит неправильно. 20 Power Query Ошибки №20. Пустая таблица при импорте Excel (XLSX, XLS) Вы пытаетесь подключиться к книге Excel, но импортируется пустая таблица. 21 Power Query Ошибки №21. Подключение к PostgreSQL, Установка драйвера Вы пытаетесь подключиться к PostgreSQL и получаете ошибку «Перед использование этого соединителя необходимо установить один или несколько дополнительных компонентов»

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

Предыдущая заметка     Следующая заметка

Рис. 1. Три поля записи error; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

Сообщение об ошибке

В Power Query каждое выражение должно что-то возвращать. Как правило, это значение. Но выражение также может вызвать ошибку – особый способ указать, что не получилось вернуть значение. Один из способов вызвать ошибку – создать запись с ключевым словом error. Такая запись имеет три поля: причина, сообщение и подробности. Поля с любыми другими именами будут проигнорированы.

Листинг 1[2]

= error [ Reason = «Business Rule Violated», Message = «Item codes must start with a letter», Detail = «Non-conforming Item Code: 456» ]

Все три поля являются необязательными. Если поле Reason отсутствует, причина ошибки будет иметь значение по умолчанию – Expression.Error. Запись ошибки можно также создать с помощью функции Error.Record. В отличие от описанного выше подхода, в Error.Record атрибут Reason является обязательным.

Листинг 2

= error Error.Record( «Business Rule Violated», «Item codes must start with a letter», «Non-conforming Item Code: 456» )

Оба приведенных выше примера приводят к эквивалентной ошибке, изображенной на рис. 1 Глядя на рисунок, видно, как три поля/параметра соотносятся с отображаемым сообщением.

Вместо записи error также может принимать строку. Результирующее сообщение об ошибке будет иметь значение предоставленной строки, а его причина – значение Expression.Error.

Листинг 3

Рис. 2. Строка в error

Ярлык с многоточием

Существует также оператор быстрого доступа для создания ошибок, который пригодится во время разработки. Допустим, вы хотите протестировать запрос, часть кода которого еще не написана. Поскольку каждое выражение должно возвращать значение, или вызывать ошибку, вы не можете протестировать свой запрос, не поместив что-то в качестве заполнителя в нереализованные участки кода. Используйте оператор многоточия (…). При вызове … выдает ошибку Expression.Error: Значение не задано. Вот фрагмент кода, в котором не реализована ветвь else:

Листинг 4

let a = 6, Result = if a = 5 then true else ... in Result

Когда условие (а = 5) принимает значение false, вызывается «…», что приводит к ошибке. Обратите внимание, ключевое слово error не используется. Оператор многоточия как определяет, так и вызывает ошибку.

Особое поведение

Что именно происходит, когда возникает ошибка? Какое поведение возвращает ошибку, а не значение? Рассмотрим выражение:

В обычных условиях сначала выполняется функция GetValue(). Затем полученное значение передается в someFunction(), которая возвращает финальный результат. Предположим, GetValue() выдает ошибку. Дальнейшее выполнение выражения прекращается. someFunction() не вызывается. Ошибка GetValue() становится итогом выражения. Такое поведение также известно, как повышение. Ошибка передается тому шагу, с которого была вызвана someFunction().

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

Сдерживание ошибок

Если ошибка возникает в выражении, которое что-то определяет (поле записи, ячейку таблицы, переменную в выражении let, …), ошибка содержится в этом чём-то. Последствия ошибки ограничены этим чем-то и логикой, которая пытается получить доступ к значению этого чего-то. Ниже последствия ошибки GetValue содержатся в той части запроса, на которую она повлияла. Ошибка не остановила выполнение запроса. Запрос завершился успешно и вернул запись. Два поля – FieldB и FieldC – вернули ошибку, потому что они являются чем-то, затронутым ошибкой.

Листинг 5

let GetValue = () => error «Something bad happened!», DoSomething = (input) => input + 1, Result = [ FieldA = 25, FieldB = DoSomething(GetValue), FieldC = FieldA + FieldB ] in Result

Рис. 3. Результат запроса

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

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

let Data = GetDataFromWebService() // повышенная ошибка in { List.Sum(Data[Amount]), List.Max(Data[TransactionDate]) }

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

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

Листинг 6

let GetValue= () => error «Something bad happened!», SomeFunction = (input) => input + 1 in SomeFunction(GetValue())

Сдерживание против исключения

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

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

На самом деле, из-за лени M, если к ячейке с ошибкой не будет обращений, то ошибка и не возникнет.

Листинг 7

let Data = #table({«Col1»}, {{«SomeValue»}, { error «bad» }}) in Table.RowCount(Data) // возвращает 2

Хотя одна ячейка и содержит ошибку, запрошенные данные (количество строк), не требуют вычисления значения ошибочной ячейки, поэтому выражение вернет значение 2.

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

Листинг 8

let Data = #table({«Col1»}, {{«SomeValue»}, { error «bad» }}) in [ RowsWithErrors = Table.RowCount(Table.SelectRowsWithErrors(Data)), RowsWithoutErrors = Table.RowCount(Table.RemoveRowsWithErrors(Data)) ]

Рис. 4. Список, содержащий количество строк с ошибками и без

Обработка ошибок

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

try ExpressionToTry otherwise FallbackExpression

… первый, try with otherwise, пытается выполнить ExpressionToTry. Если это выражение возвращает значение, всё Ok, переходим к следующему шагу запроса. Когда выражение выдает ошибку, вычисляется выражение otherwise и возвращается его значение.

try Number.FromText(input) otherwise 0

Если Number.FromText возвращает значение, оно и будет результатом выражения. Когда Number.FromText выдает ошибку, try обращается к части otherwise, и возвращает 0. Другими словами, если входные данные могут быть преобразованы в число, возвращается это число; в противном случае возвращается значение по умолчанию – 0.

Имейте в виду, что ошибки будут обработаны только в выражении, расположенном непосредственно справа от try. Если ошибку возвращает выражение otherwise, эта ошибка не будет обработана предшествующим try. Но… поскольку otherwise само по себе является выражением, try можно поместить внутрь него, чтобы обработать ошибку, вызванную otherwise.

try GetFromPrimary() otherwise try GetFromSecondary() otherwise «Возникли проблемы с обоими серверами. Возьми отгул на остаток дня :)»

Проблема с конструкцией try with otherwise в том, что она неразборчива: любая ошибка возвращает альтернативное значение. Иногда последующие действия зависят от типа ошибки. Для этих ситуаций подойдет второй вариант – простое выражение try.

Эта форма всегда возвращает запись. Если выражение завершилось успешно, эта запись имеет вид:

[ HasError = false, Value = (значение выражения ExpressionToTry) ]

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

[ HasError = true, Error = (запись, описывающая возникшую ошибку) ]

Например:

Листинг 9

let DoSomething = () => 45, Result = try DoSomething() in Result // [HasError = false, Value = 45]

Рис. 5. Запись, возвращаемая try, если нет ошибки

Листинг 10

let DoSomething = () => error «bad», Result = try DoSomething() in Result // [HasError = true, Error = [Reason = «Expression.Error», Message = «bad», Details = null]

Рис. 6. Запись, возвращаемая try, если есть ошибка

Запись, помещенная в поле Error, содержит ровно три поля: Reason, Message и Details.[3] Это верно, даже если в записи, первоначально использовавшейся для определения ошибки, отсутствовало одно или несколько из этих полей (помните, они необязательны при определении ошибки), или если она включала дополнительные поля.

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

let Primary = try GetDataFromPrimary(), Source = if Primary[HasError] = false then Primary[Value]     /* если Primary возвращает значение, используй его */ else if Primary[Error][Reason] = «External Source Error» and Primary[Error][Message] = «Server is unreachable»     then GetDataFromSecondary()     /* если ошибка Primary вызвана тем, что его источник недоступен,             запроси данные с сервера Secondary */ else error Primary[Error]     /* если Primary вернул иную ошибку, верни её             в качестве результата запроса */ in Source

Используя try with otherwise, мы бы запросили Secondary, если Primary выдаст любую ошибку, а не только когда основной сервер недоступен:

try GetDataFromPrimary() otherwise GetDataFromSecondary()

Масштаб (область действия)

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

let Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}) in try Data otherwise 0

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

try будет полезен в следующем случае, но его эффект может быть не таким, как предполагал разработчик.

Листинг 11

let Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}) in try List.Sum(Data[Amount]) otherwise 0

List.Sum суммирует значения в столбце [Amount] таблицы Data. Если выражение, определяющее значение элемента, вызывает ошибку, она повышается. List.Sum прекращает суммирование и возвращает ошибку. try обрабатывает эту ошибку, возвращая 0 вместо суммы элементов списка. Скорее всего, цель у разработчика была иная. Он хотел заменить элементы с ошибками на 0, и суммировать числовые значения. Необходимо применить try так, чтобы обработка ошибок велась на уровне ячеек таблицы. Кажется, что можно сделать так:

Table.TransformColumns(Data, (input) => try input otherwise 0)

Однако, эта логика не улавливает ошибки, вызванные выражениями значений ячеек. Дело в том, что аргументы вычисляются до того, как их значения будут переданы в функцию. Если оценка приводит к ошибке, функция не вызывается. Вместо этого ошибка передается шагу, который вызвал функцию. В нашем случае, если выражение значения столбца выдает ошибку, функция преобразования (input) => … не вызывается, поэтому try не может обработать ошибку. Вместо этого ошибка передается обратно в Table.TransformColumns.

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

Листинг 12

let Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}), ErrorsReplacedWithZero = Table.AddColumn( Data, «NewAmount», (row) => try row[Amount] otherwise 0 ), RemoveOldAmount = Table.RemoveColumns(ErrorsReplacedWithZero, {«Amount»}), RenameNewAmount = Table.RenameColumns(RemoveOldAmount, {«NewAmount», «Amount»}) in List.Sum(RenameNewAmount[Amount]) // возвращает 10

Это довольно сложно. Но пример хорошо иллюстрирует общий подход к использованию try на уровне ячеек. Если же, как в нашем пример, вы просто хотите заменить любую ошибку значением по умолчанию, используйте Table.ReplaceErrorValues.

Листинг 13

let Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}), ErrorsReplacedWithZero = Table.ReplaceErrorValues( Data, {{«Amount», 0}} ) // заменяет ошибки в столбце Amount нулями in List.Sum(ErrorsReplacedWithZero[Amount]) // возвращает 10

Применить try к элементам списка сложнее. Для списков нет функции List.ReplaceErrorValues. Самым простым решением может быть преобразование списка в таблицу, обработка ошибки, а затем обратное преобразование таблицы в список.

Листинг 14

let Data = {10, error «help!», error «save me!»}, #»Преобразовано в таблицу» = Table.FromValue(Data), #»Замененные ошибки» = Table.ReplaceErrorValues(#»Преобразовано в таблицу», {{«Value», 0}}), Value = #»Замененные ошибки»[Value], #»Вычисленная сумма» = List.Sum(Value) in #»Вычисленная сумма»

Рис. 7. Сумма элементов списка, содержащего ошибки

Нарушения правил

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

let Data = GetData(), // for testing use: #table({«ItemCode»}, {{«1»}, {«A2»}}) Validated = Table.TransformColumns( Data, { «ItemCode», each if Text.StartsWith(_, «A») then _ else error Error.Record(       «Invalid Data», «ItemCode does not start with expected letter», _ ) } ) in Validated

Такую проверку полезно применить в базовом запросе, на который будут ссылаться несколько других запросов. Это позволит вам выполнить проверку один раз (вспомните принцип Не повторяйся, Don’t repeat yourself), гарантируя, что пользователи, пытающиеся использовать ошибочные данные, будут предупреждены о наличии аномалий.

Другой вариант – добавить столбец со значениями true и false, в зависимости от того, соблюдается ли правило:

let Data = GetData(), // for testing use: #table({«ItemCode»}, {{«1»}, {«A2»}}) Validated = Table.AddColumn( Data, «ValidItemCode», each Text.StartsWith(_[ItemCode], «A»), type logical ) in Validated

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

В следующей заметке

Я планирую рассказать о том, что остается за кулисами: организации разделов кода и о том, как M предоставляет возможность аннотировать значения дополнительной информацией (метаданными). Однако перед этим обсудим, как работает система типов в Power Query.

[1] Заметка написана на основе статьи Ben Gribaudo. Power Query M Primer (Part 15): Error Handling. Если вы впервые сталкиваетесь с Power Query, рекомендую начать с Марк Мур. Power Query.

[2] Номер листинга соответствует номеру запроса в приложенном Excel файле.

[3] Судя по рис. 6 современная реализация M создает пять полей.