Power Query Ошибки №8. Разные имена листов 2

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

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

• Table.PromoteHeaders
• Table.ColumnNames
• List.Contains
• Table.FirstValue
• Ссылка на ячейку в Power Query
• Вложенные функции Power Query

Подготовка

Как и в прошлом уроке наша задача — это создать такой запрос Power Query, который будет работать вне зависимость от того как названы листы.

Скачайте приложенные файлы по ссылке выше.

Откройте файл РазныеНазвания_Пустой.xlsx.

Запустите редактор запросов.

Решим эту задачу двумя способами.

Способ 1

Убедитесь, что остался только шаг Источник, остальные шаги удалите.

Добавьте столбец с формулой Table.PromoteHeaders([Data]). Этой формулой мы продублировали табличный столбец, но в таблицах подняли заголовки.

Теперь добавим столбец, в котором получим заголовки таблиц этого столбца. Добавьте столбец с формулой Table.ColumnNames([Пользовательская]).

Теперь проверим, в какой строке находится строка со значением «created_on». Для этого создадим столбец с формулой List.Contains([Пользовательская.1], «created_on»).

В новом столбце оставьте только значение TRUE. Останется только одна строка, которая и является правильной. Осталось только развернуть таблицу из этой строки.

Чтобы развернуть таблицу добавим шаг с формулой #»Строки с примененным фильтром»[Пользовательская]{0}.

Способ 2

Решим эту же задачу другим способом. Начнем с шага Источник. Добавьте столбец с формулой Table.FirstValue([Data])=»created_on».

Table.FirstValue([Data]) — формула возвращает значение из первой строки первого столбца таблицы из столбца Data. Если к этой формуле мы приписываем =»created_on», то мы проверим является ли значение из первой строки первого столбца равным «created_on».

При помощи фильтра оставим только значения TRUE. Останется только одна строка. Развернем ее так же, как и в способе 1.

Готов. Как в первом, так и во втором случае мы сделали такое подключение, которое будет работать при любых именах листов.

Примененные функции

• Table.AddColumn
• Table.PromoteHeaders
• Table.ColumnNames
• List.Contains
• Table.SelectRows
• Table.FirstValue

Код

Первый способ:

let
   source = Excel.Workbook(File.Contents(path), null, true), 
   tab_add_col_1 = Table.AddColumn(
      source, 
      "Пользовательская", 
      each Table.PromoteHeaders([Data])
   ), 
   tab_add_col_2 = Table.AddColumn(
      tab_add_col_1, 
      "Пользовательская.1", 
      each Table.ColumnNames([Пользовательская])
   ), 
   tab_add_col_3 = Table.AddColumn(
      tab_add_col_2, 
      "Пользовательская.2", 
      each List.Contains([Пользовательская.1], "created_on")
   ), 
   rows_select = Table.SelectRows(
      tab_add_col_3, 
      each ([Пользовательская.2] = true)
   ), 
   get_tab = rows_select[Пользовательская]{0}
in
   get_tab

Второй способ:

let
   source = Excel.Workbook(File.Contents(path), null, true), 
   tab_add_col = Table.AddColumn(
      source, 
      "Пользовательская", 
      each Table.FirstValue([Data]) = "created_on"
   ), 
   rows_select = Table.SelectRows(
      tab_add_col, 
      each ([Пользовательская] = true)
   ), 
   tab_headers = Table.PromoteHeaders(rows_select[Data]{0})
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 и получаете ошибку «Перед использование этого соединителя необходимо установить один или несколько дополнительных компонентов»