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

Power Query. Ошибки. Разные названия листов

Все уроки курса Работа с ошибками в Power Query на этой странице и в этом плейлисте.

Описание

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

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

предыдущая запись

Power Query. Ошибки. Введение

следующая запись