Файлы к уроку:
- Для спонсоров Boosty
- Для спонсоров VK
Ссылки:
- Страница курса
- Плейлист YouTube
- Плейлист ВК
Описание
Вы ходите выполнить добавление таблиц друг под другом с предварительной обработкой, но получаете ошибку Expression.Error: Столбец таблицы не найден.
Решение
В этом уроке мы повторим/изучим следующее:
- Консолидация txt
- Функция Folder.Files
- Expression.Error
- Table.SelectColumns
- Аргумент MissingField.Type
В данном примере у нас есть шаг, в котором мы отбираем нужные нам столбцы, а остальные удаляем.
У нас есть множество источников, но некоторые столбцы отсутствуют в некоторых источниках. В таком случае мы хотим увидеть значение null в этом столбце. Для этого в запросе примере мы воспользуемся параметром MissingField.Type для функции Table.SelectColumns.
Примененные функции
- Table.Skip
- Table.PromoteHeaders
- Table.SelectRows
- Table.SelectColumns
- MissingField.Ignore
- Folder.Files
- Table.AddColumn
- Table.RenameColumns
- Table.ExpandTableColumn
- Table.ColumnNames
Код
Запрос-пример:
let
source = Csv.Document(
Параметр1,
[
Delimiter = " ",
Columns = 25,
Encoding = 65001,
QuoteStyle = QuoteStyle.None
]
),
rows_skip = Table.Skip(source, 4),
tab_headers = Table.PromoteHeaders(
rows_skip,
[PromoteAllScalars = true]
),
rows_select = Table.SelectRows(tab_headers, each ([Дата] <> "Итого")),
cols_select = Table.SelectColumns(
rows_select,
{
"Дата",
"PosID",
"Магазин",
"Тип оплаты",
"Вид оплаты",
"Код",
"Номенклатура",
"Качество",
"Признак",
"Количество",
"Цена",
"Сумма"
},
MissingField.Ignore
)
in
cols_selectПоследующее преобразование данных после консолидации:
let
source = Folder.Files(Путь),
rows_select_1 = Table.SelectRows(source, each ([Extension] = ".txt")),
rows_select_2 = Table.SelectRows(
rows_select_1,
each [Attributes]?[Hidden]? <> true
),
tab_add_col = Table.AddColumn(
rows_select_2,
"Преобразовать файл",
each #"Преобразовать файл"([Content])
),
cols_rename = Table.RenameColumns(tab_add_col, {"Name", "Source.Name"}),
cols_select = Table.SelectColumns(
cols_rename,
{"Source.Name", "Преобразовать файл"}
),
col_expand = Table.ExpandTableColumn(
cols_select,
"Преобразовать файл",
Table.ColumnNames(#"Преобразовать файл"(#"Пример файла"))
)
in
col_expandЭтот урок входит в курс Работа с ошибками в Power Query
| Номер урока | Урок | Описание |
|---|---|---|
| 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 в Excel и Power BI: сбор, объединение и преобразование данных.
Предыдущий раздел К содержанию Следующий раздел
Несовпадающие таблицы в контексте этой главы — это таблицы, которые описывают одни и те же семантические сущности и факты, но имеют различающиеся названия столбцов. Например:
Рис. 1. Пример несоответствия имен столбцов
Скачать заметку в формате Word или pdf, примеры в формате архива (внутри несколько файлов Excel без поддержки макросов)
В большинстве случаев Power Query объединяет таблицы в новую таблицу, которая включает расширенный набор всех столбцов из исходных таблиц. Каждая строка из первой таблицы копируется в объединенную таблицу, причем пустые значения отображаются в столбцах, находящихся только во второй таблице. Каждая строка из второй таблицы будет скопирована таким же образом с пустыми значениями в столбцах, которые являются исключительными для первой таблицы. Это называется разделением данных. Вместо объединения значений из двух таблиц в один столбец Power Query сохраняет исходные столбцы с унаследованными несвязанными данными.
Рис. 2. Несоответствующие таблицы
Устранение несоответствующих названий столбцов
Загрузить файлы: C04E01 — Accessories.xlsx и C04E01 — Bikes.xlsx. Стоимость товара указана в столбце Cost для Bikes и в столбце StandardCost в таблице Accessories. Откройте новую рабочую книгу в Excel и импортируйте две исходные книги в режиме Только создать подключение. В редакторе PQ выберите запрос Accessories и выполните команду Добавить запросы –> Добавить запросы в новый. Обратите внимание, что в новом запросе Добавить1 столбцы Cost и StandardCost включены в объединенные результаты:
Рис. 3. Объединенный запрос; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Выберите запрос Accessories и переименуйте столбец StandardCost на Cost. Теперь, при повторном выборе запроса Append1 видно, что столбцы объединены корректно.
Объединение несоответствующих таблиц из одной папки
Допустим четверо менеджеров ведут запасы товаров. При попытке объединить их данные обнаруживается, что они используют разные имена столбцов:
Рис. 4. Имена столбцов в четырех категориях продуктов
Загрузите файлы C04E02 — Accessories.xlsx, C04E02 — Bikes.xlsx, C04E02 — Components.xlsx, C04E02 — Clothing.xlsx в отдельную папку. В Excel откройте новую рабочую книгу, пройдите Данные –> Получить данные –> Из файла –> Из папки. В окне Обзор выберите папку, в которой сохранили эти 4 файла (я назвал ее Товары) и нажмите Открыть, хотя ни один файл не выбран. В следующем окне файлы из папки будут представлены четырьмя строками. Пройдите Объединить –> Объединить и преобразовать данные. В окне Объединить файлы раскрывающееся меню Пример файла позволяет выбрать один из исходных файлов и применить его формат и заголовки ко всем файлам в папке:
Рис. 5. Выбор файла для заголовков
Оставьте выбор по умолчанию, выделите Sheet1 и кликните Ok.
В окне редактора PQ прокрутите вниз, пока строки не изменятся от Accessories до Bikes. Ряд столбцов содержат нулевые значения. В редакторе PQ перейдите на панель Запросы и выберите запрос Преобразовать пример файла. Он играет роль примера запроса. Этот запрос сгенерирован в результате объединения файлов из папки. После выбора примера запроса можно увидеть на главной панели редактора PQ, что данные из таблицы Accessories отображаются правильно.
Рис. 6. Запрос Преобразовать пример файла
При объединении файлов из папки Power Query создает:
- функцию Преобразовать файл,
- пример файла,
- Параметр 1 (Пример файла) и
- запрос Преобразовать пример файла
- запрос Товары; этот запрос будет назван по имени папки, из которой вы ведете импорт.
Эти элементы помогают объединять несколько файлов из папки на основе выбранных данных. Основное преобразование для каждого файла реализовано в функции. Для того чтобы настроить преобразование для каждого файла, можно вносить изменения в пример запроса. Эти изменения распространяются на функцию и применяются ко всем файлам. Всякий раз при объединении файлов из папки найдите запрос, который начинается с Преобразовать пример файла. Этот запрос рассматривает как изменение каждого файла, так и его добавление.
Предположение об одинаковом порядке столбцов
В редакторе PQ выберите запрос Преобразовать пример файла и переименуйте его в Products Sample. На панели Примененные шаги удалите последний шаг Повышенные заголовки. После этого объединенная таблица будет иметь общие столбцы с именами Column1, Column2, Column3 и т.д. Если все таблицы имеют одинаковую последовательность столбцов, то данные в добавленной таблице будут корректными.
Выберите запрос Товары и просмотрите объединенную таблицу. Обратите внимание, что при переходе от Accessories к Bikes данные отражаются корректно. Правда появилась ошибка Столбец «Product» таблицы не найден. На панели Примененные шаги удалите последний шаг Измененный тип. Теперь в объединенной таблице отсутствуют пропущенные значения. Можно убедиться в этом, выполнив прокрутку снова и удостоверившись, что включены все значения в таблице Accessories.
Теперь можно повысить первую строку до уровня заголовка, выбрав команду Использовать первую строку в качестве заголовков. Осталось избавиться от промежуточных заголовков. На панели Запросы выберите запрос Products Sample. Пройдите Добавление столбца –> Столбец индекса. Имена столбцов каждой таблицы имеют индекс ноль. Вернитесь к запросу Товары и отфильтруйте строки с нулевым индексом. Для этого кликните мышью на элементе управления фильтром в последнем столбце, который помечен символом 0. Снимите галочку напротив значения 0, нажмите Ok.
Удалите первый столбец (столбец «C04E02 — Accessories.xlsx») и последний столбец (столбец 0). Можно также повысить надежность скрипта, изменив удаление столбца с именем «C04E02 — Accessories.xlsx» на удаление первого столбца. Измените код:
|
= Table.RemoveColumns(#»Строки с примененным фильтром», {«C04E02 — Accessories.xlsx», «0»}) |
…на:
|
= Table.RemoveColumns(#»Строки с примененным фильтром», {Table.ColumnNames(#»Строки с примененным фильтром»){0}, «0»}) |
Теперь можно загрузить объединенную таблицу на лист Excel и приступать к анализу.
Простая нормализация
Если нельзя полагаться на согласованный порядок следования столбцов, можно попробовать применить нормализацию имен столбцов. Нормализация – это изменение текста заголовков, устраняющее незначительные различия. Например, можно заменить все символы подчеркивания пробелами и использовать только заглавные буквы.
Загрузите файлы C04E03 — Accessories.xlsx, C04E03 — Bikes.xlsx, C04E03 — Components.xlsx, C04E03 — Clothing.xlsx. Откройте новую книгу в Excel и пройдите Данные –> Получить данные –> Из файла –> Из папки. В окне Обзор выберите папку, в которой сохранили эти 4 файла и нажмите Открыть, хотя ни один файл не выбран. В следующем окне файлы из папки будут представлены четырьмя строками. Пройдите Объединить –> Объединить и преобразовать данные. В окне Объединить файлы выделите Sheet1 и кликните Ok.
Выберите запрос Преобразовать пример файла и переименуйте его в Products Sample. При выбранном запросе Products Sample щелкните на значке fx в строке формул. На панели Примененные шаги создается новый шаг – Пользовательская1, который имеет формулу:
Рис. 7. Код М, соответствующий щелчку на значке fx
Шаг Повышенные заголовки был последним шагом на панели Примененные шаги перед тем, как выполнен щелчок на кнопке fx.
#»Повышенные заголовки – переменная, используемая для вывода этого шага. Поскольку данная переменная возвращает таблицу с несовпадающими именами столбцов, к ней можно применить функцию Table.TransformColumnNames с помощью метода Text.Lower, что позволит задать строчные буквы в именах столбцов. Для этого измените формулу в строке на:
|
= Table.TransformColumnNames(#»Повышенные заголовки», Text.Lower) |
Нажмите Enter и обратите внимание, что все имена столбцов переведены в нижний регистр. Если предпочтительнее заглавные буквы в именах столбцов, то можно в предыдущей формуле заменить функцию Text.Lower на Text.Proper.
Для замены символа подчеркивания в именах столбцов пробелами снова щелкните на значке fx в строке формул. На панели Примененные шаги появился новый шаг, Пользовательская2, а в строке формул:
Примените функцию Table.TransformColumnNames для замены подчеркивания пробелами в именах столбцов, изменив формулу следующим образом:
|
= Table.TransformColumnNames(Пользовательский1, each Replacer.ReplaceText(_, «_», » «)) |
Нажмите Enter и обратите внимание на то, что все имена столбцов теперь содержат пробелы вместо подчеркивания.
Выберите запрос Товары и удалите последний шаг Измененный тип. Заметьте, что все файлы корректно сведены вместе, без каких-либо дополнительных признаков пропущенных значений.
Таблица преобразования
К сожалению, простой нормализации может не хватить для исправления разношерстных заголовков. Для обозначения одного по сути столбца разные менеджеры используют 4 заголовка: ID, Product_Number, Product_num и Product Number. Нормализовать имена этих столбцов с помощью простых операций над текстом нельзя. Вместо этого можно сформировать таблицу преобразования исходных имен в нормализованные.
Рис. 8. Таблица преобразования исходных имен в нормализованные
Все несоответствия, которые вы будете выявлять при работе с исходными файлами, удобно отражать в таблице во внешней книге Excel. Постепенно будут появляться новые несоответствия, и формироваться новые строки таблицы преобразования. При подключении таблицы преобразования к запросам не нужно редактировать запрос для нормализации новых несоответствующих имен столбцов. Вместо этого просто добавьте в таблицу преобразования новые строки и обновите отчет.
Рассмотрим несколько методов, позволяющих работать с таблицами преобразования.
Методика транспонирования
Для нормализации несовпадающих столбцов с помощью таблицы преобразования необходимо временно преобразовать имена столбцов в таблицу с одним столбцом, объединить таблицу преобразования, заменить несоответствующие имена столбцов требуемыми целевыми и преобразовать имена столбцов обратно в заголовки.
Загрузите файл C04E04 — Conversion Table.xlsx с таблицей преобразования. Загрузите файлы C04E04 — Accessories.xlsx, C04E04 — Bikes.xlsx, C04E04 — Components.xlsx, C04E04 — Clothing.xlsx в отдельную папку, например Products. Откройте новую книгу в Excel и пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C04E04 — Conversion Table.xlsx. Нажмите Импорт. В окне Навигатор выберите Header_Conversion. Кликните Загрузить –> Загрузить в…, выберите опцию Только создать подключение. Теперь, при наличии таблицы преобразований в качестве нового запроса, можно загрузить содержимое папки Products.
Пройдите Данные –> Получить данные –> Из файла –> Из папки. Выберите папку Products и нажмите Открыть, хотя ни один файл не выбран. В следующем окне файлы из папки будут представлены четырьмя строками. Пройдите Объединить –> Объединить и преобразовать данные. В окне Объединить файлы выделите Sheet1 и кликните Ok.
На панели Запросы выберите Преобразовать пример файла и переименуйте его в Products Sample. Напомним, что этот запрос является примером обработки одного файла из папки. Введенные здесь изменения повлияют на таблицу, добавленную в запросе Products. Переименуйте запрос Products в Appended Products. Пройдите Главная –> Закрыть и загрузить. Сохраните рабочую книгу Excel. Затем создайте три ее копии; они пригодятся при выполнении следующих заданий.
Первый способ нормализации имен столбцов основан на команде Преобразование –> Транспонировать. С помощью транспонирования каждая ячейка в столбце X и строке Y будет помещена в столбец Y и строку X. При манипулировании значениями имен столбцов выполнить это в редакторе Power Query гораздо проще, если имена столбцов представлены вертикально в столбце.
Однако при попытке транспонирования таблицы обнаруживается, что исходные имена столбцов не сохраняются после преобразования. Для сохранения имен столбцов необходимо понизить имена столбцов до первой строки, а затем транспонировать таблицу. Для этого откройте сохраненную книгу и запустите редактор Power Query. Например, пройдя Данные –> Получить данные –> Запустить редактор Power Query. На панели Запросы выберите запрос Products Sample, удалите шаг Повышенные заголовки. Теперь заголовки примера таблицы находятся в первой строке. Пройдите Преобразование –> Транспонировать. Теперь столбец Column1 включает имена столбцов, которые необходимо нормализовать.
Рис. 9. Запрос Products Sample после транспонирования столбцов в строки
При выбранном запросе Products Sample пройдите Главная –> Объединить запросы. Будьте внимательны, ранее мы использовали команду Добавить запросы. В окне Слияние выберите столбец Column1 в таблице Products Sample (1), в раскрывающемся меню выберите запрос Header_Conversion (2), выберите столбец Source (3):
Рис. 10. Окно Слияние
Проверьте, что Тип соединения выбран Внешнее соединение слева, кликните Ok. В запрос Products Sample добавится новый столбец Header_ Conversion с объектами таблицы (Table). Разверните столбец Header_Conversion щелкнув мышью на элементе управления справа от его заголовка, и установив опции:
Рис. 11. Окно Развернуть
Столбец Header_Conversion преобразуется в столбец Target:
Рис. 12. Столбец Target
Значение null означает, что запрос Header_ Conversion не содержал значений для замены, т.е. исходные заголовки в столбце Column1 отвечают требованиям. Добавим условный столбец для копирования целевых значений из строк, которые должны быть нормализованы, и исходных значений из прочих строк.
Пройдите Добавление столбца –> Условный столбец. Настройте параметры:
Рис. 13. Окно Добавление условного столбца
Удалите столбцы Column1 и Target. Переместите столбец New Column Names на место первого столбца таблицы. Просто перетащите и опустите его с правой на левую сторону или воспользуйтесь советом. Если в таблице много столбцов, а нужно переместить столбец в начало, вместо перетаскивания столбца можно выделить столбец и на вкладке Преобразование выбрать команду Переместить –> В начало. Также можно щелкнуть правой кнопкой мыши на заголовке столбца и выбрать в контекстном меню команду Переместить –> В начало.
Пришло время транспонировать таблицу обратно. Выполните Преобразование –> Транспонировать, и Преобразование –> Использовать первую строку в качестве заголовков.
Перейдите к запросу Appended Products и удалите последний шаг Измененный тип. Изучите запрос Appended Products в редакторе PQ или на листе Excel. Объединение должно быть выполнено корректно, несмотря на несоответствие в заголовках в четырех исходных файлах.
Отмена свертывания, слияние и повторное сведение
Метод транспонирования работает только при небольших наборах данных. Power Query поддерживает таблицы с 16 384 столбцами. Так что у вас не получится транспонировать таблицу с более чем 16 384 строками. Даже если испробовать этот метод на 15 000 строках, потребление памяти и низкая частота обновления наводят на мысль о необходимости альтернативного метода.
Откройте копию файла, сохраненного ранее. Напомню, мы остановились на шаге, когда переименовали запрос Products в Appended Products. Запустите редактор Power Query. Выберите запрос Products Sample. Пройдите Добавление столбца –> Столбец индекса. Кликните правой кнопки мыши на столбце индекса и выберите Отменить свертывание других столбцов. Power Query создаст новую таблицу с тремя столбцами: Индекс, Атрибут и Значение. В столбце Индекс сохраняется исходный идентификатор строки, что поможет далее вернуть таблицу в исходное состояние. Столбец Атрибут включает имя столбца, а столбец Значение содержит исходные значения, которые имелись в таблице. Теперь можно выполнить подстановку в столбце Атрибут.
Рис. 14. Отменить свертывание других столбцов
Примените последовательность слияния, описанную выше. Пройдите Главная –> Объединить запросы. Настройте окно Слияние, как на рис. 10. Разверните столбец Header_Conversion, как показано на рис. 11. Добавьте условный столбец с параметрами, как на рис. 13. Удалите столбцы Атрибут и Target. Переместите столбец New Column Names на место второго столбца таблицы.
Для отмены развертывания выберите столбец New Column Names, пройдите Преобразование –> Столбец сведения. В окне Столбец сведения выберите Значение (вместо Индекс), откройте раздел Расширенные параметры. Установите Не агрегировать, кликните Ok. Удалите столбец Индекс. Перейдите к запросу Appended Products, удалите шаг Измененный тип. Наслаждайтесь качественно скомбинированной таблицей!
Применение языка M для нормализации имен столбцов
Это наиболее эффективный метод нормализации имен столбцов. Будет создана пользовательская функция, которая нормализует имена столбцов в соответствии с правилами из таблицы преобразования. Выполним одно изменение, которое упрощает код и ускоряет поиск в таблице преобразования. Ранее таблица преобразования была сформирована для пар значений Source / Target. Транспонируйте эту таблицу и используйте первую строку в качестве заголовков. Таким образом, новая таблица преобразования включит значения Source в качестве заголовков и значения Target в качестве первой строки.
Откройте копию файла, сохраненного ранее. Запустите редактор Power Query. На панели Запросы выберите Header_Conversion. Пройдите Преобразование –> Транспонировать, а затем Использовать первую строку в качестве заголовков. Удалите шаг Измененный тип, созданный автоматически. В нем нет необходимости, но этот шаг может привести к ошибкам обновления в будущем.
Выберите запрос Products Sample и щелкните мышью на значке fx. Измените формулу:
|
= #»Повышенные заголовки» |
на:
|
= Table.TransformColumnNames(#»Повышенные заголовки», each try Table.Column(Header_Conversion, _){0} otherwise _) |
Нажмите Enter, изучите заголовки. Они нормализованы. Выберите запрос Appended Products и убедитесь, что все несоответствующие таблицы объединены правильно.
Обратите внимание на второй аргумент функции
|
each try Table.Column(Header_Conversion, _){0} otherwise _ |
Комбинация each и _ используется в качестве ярлыка для определения функции. В этом случае Table.TransformColumnNames требуется функция в качестве второго аргумента, и с помощью each и символа подчеркивания выполняется вызов этой функции для каждого имени столбца.
Для получения доступа к ячейке первой строки в одном из столбцов таблицы преобразования указан параметр {0}. В языке M все основано на индексах, начинающихся с нуля, поэтому для доступа к первой ячейке задан нуль. Фигурные скобки служат для доступа к элементу в списке по его нулевому индексу.
Допустим, столбец Column1 находится в таблице Header_Conversion. Для получения доступа к списку значений в Column1 применяется следующая формула:
|
Header_Conversion[Column1] |
В этом случае имя столбца становится входным параметром для определенной функции с помощью символа подчеркивания. Для получения доступа к каждому из столбцов (в виде списков) по их именам в таблице Header_Conversion можно написать следующую функцию:
|
each Table.Column(Header_Conversion, _) |
Заметим, что здесь применяется функция Table.Column. Следующая формула не будет работать:
|
each Header_Conversion[_] |
С помощью этой формулы механизм языка M выполнит поиск столбца, действительное название которого представляет собой символ подчеркивания _. В результате получим следующую ошибку:
|
Expression.Error: The column ‘_’ of the table wasn‘t found (Столбец ‘_‘ таблицы не найден) |
Чтобы избежать появления подобной ошибки, применим функцию Table.Column, которая получает таблицу и имя столбца и возвращает нужный столбец в виде списка.
Напомним, что таблица преобразования была предназначена для хранения не всех возможных имен столбцов, а только несоответствующих имен столбцов. Поэтому выражение включает элементы try/otherwise. Если значение Source не обнаружено в таблице Header_Conversion, часть try потерпит неудачу, запуская другую часть, которая вернет значение Source.
Функцию…
|
= Table.TransformColumnNames(#»Повышенные заголовки», each try Table.Column(Header_Conversion, _){0} otherwise _) |
…можно прочитать ее как инструкцию на естественном языке: «Возьмите таблицу, приведенную на шаге Повышенные заголовки, и преобразуйте имена столбцов по следующему правилу: для каждого столбца найдите его в качестве имени столбца в таблице преобразования и верните соответствующее значение в ячейку таблицы преобразования. Если этого сделать невозможно (значит, такого имени столбца в таблице преобразования нет), то верните имя столбца Повышенные заголовки в качестве выходных данных».
|
Запрос Power Query — переименование колонки |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Я знаю, что были темы по этой ошибке, но я просмотрел их, и они не помогли. Я пытаюсь добавить пользовательский столбец с абсолютным значением столбца «Сумма».
Шаг 1:
Шаг 2 (вызывает ошибку):
Основываясь на других потоках, это может показаться опечаткой, однако здесь это не так. Кроме того, у меня есть 4 таких файла Excel, которые я импортирую с одинаковыми шагами — 2 работают, а 2 — нет.
Очень благодарен за любые идеи.
Изменить: вот полный код из расширенного редактора
let
Source = SharePoint.Files("https://netorgftxxxxxx.sharepoint.com/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "FName LName")),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows", each ([Name] <> "2019-07-29T08_13_08+00_00Customer_List.xlsx" and [Name] <> "2019-07-29T08_13_08+00_00Invoice_Listing_TTM.xlsx" and [Name] <> "2019-07-29T08_13_08+00_00Revenue_by_Customer_for_Dashboard.xlsx")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows5", each Text.Contains([Name], "Company")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows2",{{"Date modified", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", let latest = List.Max(#"Changed Type"[Date modified]) in each [Date modified] = latest),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Expense")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows3",{"Content"}),
Content = #"Removed Other Columns"{0}[Content],
#"Imported Excel" = Excel.Workbook(Content),
#"Removed Other Columns1" = Table.SelectColumns(#"Imported Excel",{"Data"}),
Data = #"Removed Other Columns1"{0}[Data],
#"Removed Columns" = Table.RemoveColumns(Data,{"Column1"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",4),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each "Company"),
#"Filtered Rows4" = Table.SelectRows(#"Added Custom", each ([Transaction Type] = "Bill" or [Transaction Type] = "Expense")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows4",{{"Amount", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Adjusted Expense Amount", each Number.Abs([Amount]))
in
#"Added Custom1"
3 ответа
Лучший ответ
Как оказалось, проблема была в строке 8 кода, которая не была достаточно конкретной. Импортируются файлы двух типов, что вызывает ошибку. Спасибо всем за помощь!
0
battery514
23 Сен 2019 в 15:58
Я полагаю, у вас есть значения в столбце Amount, которые выдают ошибку при использовании функции Abs(). Это может произойти, если у вас есть текст в столбце, поскольку функция Abs() ожидает числовые форматы.
Либо сначала очистите столбец Amount (чтобы у вас были только числовые форматы), либо добавьте дополнительную проверку в ваше выражение M.
Это например:
= if Value.Is(Value.FromText([Column1]), type number)
then Number.Abs(Number.From([Column1]))
else null
0
Strawberryshrub
19 Сен 2019 в 04:36
Это должен быть один из следующих случаев:
- Опечатка в столбце Количество.
- (более вероятно) Вы ссылаетесь на неправильный шаг. Таблица на шаге «Измененный тип 1» не содержит столбец «Сумма».
0
Aleksei Zhigulin
19 Сен 2019 в 13:30