Описание
В этом уроке мы решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. В процессе решения мы вспомним и повторим следующие формулы (функции):
- Ссылка на запись (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