Файлы к уроку:
Ссылки:
Описание
В этом уроке мы решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. В процессе решения мы вспомним и повторим следующие формулы (функции):
- Ссылка на запись (Record)
- Record.ToList
- List.Contains
- Ссылка на значение
Подготовка к решению
Скачайте приложенные файлы. Посмотрите файлы-источники (названия начинаются на «2019»). В каждой книге 2 листа. Представьте, что вам присылают каждую неделю файлы с обновленными данными. Все вроде в порядке. Названия столбцов те же, количество столбцов совпадает, но не совпадают названия листов. Названия всегда разные. В одном файле данные находятся на листе «Лист1», а в другом файле на листе «Данные». Как составить универсальный запрос, который бы нормально работал вне зависимости от того, как назвали листы?
Решение проблемы разных имен листов в Power Query
Откройте файл РазныеНазвания_Пустой.xlsx. Здесь уже заготовлено подключение к одной из книг-источников. Вы видите содержание книги — 2 листа.
Как понять, к какому листу подключиться не указывая его имя? В нужно нам листе находятся данные, а первый столбец таблицы называется «created_on». Значит нам нужно всего лишь воспользоваться условной логикой: если на листе в первой строке первого столбца написано «created_on», то это нужный нам лист и его нужно развернуть. Решим задачу в несколько шагов.
1. Получить список значений первой строки. Воспользуемся ссылкой на объект Record. Чтобы получить Record первой строки таблицы создадим новый столбце с формулой [Data]{0}
2. Теперь преобразуем запись в лист. Создадим новый столбец с формулой Record.ToList([Пользовательская])
3. Определим содержит ли столбец «Пользовательская.1» значение «created_on». Создадим новый столбец с формулой List.Contains([Пользовательская.1], «created_on»)
4. Отфильтруем значения FALSE в новом столбце.
5. Развернем таблицу из единственной оставшейся строки.
Теперь попробуйте подключиться к другому файлу-источнику. Подключение работает с обоими файлами не смотря на то, что названия листов везде разные
Примененные функции
- Table.SelectRows
- Table.AddColumn
- Record.ToList
- List.Contains
- Table.PromoteHeaders
Код
let
source = Excel.Workbook(File.Contents(path), null, true),
rows_select_1 = Table.SelectRows(source, each [Kind] = "Sheet"),
tab_add_col_1 = Table.AddColumn(
rows_select_1,
"Пользовательская",
each [Data]{0}
),
tab_add_col_2 = Table.AddColumn(
tab_add_col_1,
"Пользовательская.1",
each Record.ToList([Пользовательская])
),
tab_add_col_3 = Table.AddColumn(
tab_add_col_2,
"Пользовательская.2",
each List.Contains([Пользовательская.1], "created_on")
),
rows_select_2 = Table.SelectRows(
tab_add_col_3,
each ([Пользовательская.2] = true)
),
get_table = rows_select_2[Data]{0},
tab_headers = Table.PromoteHeaders(
get_table,
[PromoteAllScalars = true]
)
in
tab_headersЭтот урок входит в курс Работа с ошибками в 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 и получаете ошибку «Перед использование этого соединителя необходимо установить один или несколько дополнительных компонентов» |
