Power Query Практический №11. Нужные столбцы с нужного листа

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

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

• YouTube
• VK

Ссылки:

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

Описание

У нас есть файл, из которого нужно извлечь нужные столбцы с определенного листа.

Если мы привяжемся к имени листа, то запрос не сработает, если в следующий раз в файле листы будут названы иначе. Наша задача найти нужный лист без использования названия листа в формулах.

На нужном листе необходимо извлечь только столбцы, у которых есть слово «выручка» в имени. Если мы просто выделим столбцы, то запрос не добавит возможные новые столбцы со словом «выручка». Нам необходимо получить нужные столбцы не указывая их внучную.

Решение

Чтобы найти среди листов Excel лист с заголовком «Выручка» мы создадим условный столбец, который проверит наличие этого слова в заголовках.

Дальше нам останется лишь привести полученную таблицу к нужному виду.

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

• Folder.Files
• Excel.Workbook
• Table.AddColumn
• List.ContainsAny
• Record.ToList
• List.Select
• Text.Contains
• Comparer.OrdinalIgnoreCase
• Table.SelectRows
• Table.Skip
• Table.PromoteHeaders
• List.Select
• Table.ColumnNames
• Table.SelectColumns
• Table.TransformColumnTypes
• Int64.Type
• Table.ReplaceErrorValues
• List.Transform

Код

В первом запросе мы подключимся к папке и получим перечень доступных источников:

let
   path = Excel.CurrentWorkbook(){[Name = "Параметры"]}[Content]{
      [Параметр = "Путь"]
   }[Значение],
   source = Folder.Files(path)
in
   source

Решаем задачу:

let
   get_book = Excel.Workbook(source{[Name = "И_KPI.xlsx"]}[Content]),
   table_add_col = Table.AddColumn(
      get_book,
      "Записи",
      each List.ContainsAny(
         Record.ToList([Data]{0}),
         List.Select(
            Record.ToList([Data]{0}),
            each Text.Contains(_, "выручка", Comparer.OrdinalIgnoreCase)
         )
      )
   ),
   rows_select = Table.SelectRows(table_add_col, each ([Записи] = true)),
   get_table = rows_select[Data]{0},
   rows_skip = Table.Skip(get_table, 1),
   table_headers = Table.PromoteHeaders(rows_skip),
   list_headers = List.Select(
      Table.ColumnNames(table_headers),
      each Text.Contains(_, "выручк", Comparer.OrdinalIgnoreCase)
   ),
   cols_select = Table.SelectColumns(
      table_headers,
      {"Адрес ТТ", "Код ТТ"} & list_headers
   ),
   types = Table.TransformColumnTypes(
      cols_select,
      {
         {"Код ТТ", Int64.Type},
         {"План по выручке", type number},
         {"Факт по выручке", type number},
         {"Прогноз по выручке", type number},
         {"%вып по выручке", type number},
         {"План по выручке 3М", type number},
         {"Факт по выручке 3М", type number},
         {"Прогноз по выручке 3М", type number},
         {"%вып по выручке 3М", type number}
      }
   ),
   table_replace_errors = Table.ReplaceErrorValues(
      types,
      List.Transform(Table.ColumnNames(types), each {_, 0})
   )
in
   table_replace_errors

Этот урок входит в Практический курс Power Query

Номер урока	Урок	Описание
1	Обработка типичной выписки	В этом уроке мы обработаем типичную банковскую выписку.
2	Интересная консолидация	В этом уроке мы будем практиковать объединение таблиц по вертикали.
3	Множественная консолидация при несовпадении заголовков	Объединим таблицы по вертикали, когда заголовки таблиц не совпадают, но порядок всегда одинаков.
4	Таблица дат	Для построения отчетов в модели данных Excel вам нужно создать таблицу дат. Сейчас вы узнаете как это сделать в Power Query.
5	Продажи год назад	В этом уроке мы узнаем как в Power Query получить таблицу с продажами прошлого года напротив текущих.
6	Консолидация книг и листов одновременно	В этом уроке мы научимся объединять все листы всех книг, т. е. выполним двухуровневую консолидацию.
7	Столбец общей суммы в PQ и PP	В этом уроке мы создадим столбец, в котором будет находиться общая сумма всех строк таблицы. Так же разберем эту же операцию в Power Pivot.
8	Скученные данные 3	В этом уроке мы обработаем еще один файл со скученными данными. На этот раз в одном столбце находятся даты и номенклатура. Нужно разбить этот столбец на два.
9	Строки преобразовать в столбцы	У нас есть таблица, в которой один столбец имеет скученные данные. Нам нужно скученный столбец преобразовать в столбцы.
10	Пивот, анпивот, группировка	В этом уроке мы попрактикуем анпивот, условную логику и группировку.
11	Нужные столбцы с нужного листа	В этом уроке мы научимся извлекать нужные столбцы с нужного листа не используя название самого листа.
12	Объединение, группировка, транспонирование, анпивот	В этом уроке на очень интересном примере из реальной жизни попрактикуем несколько техник Power Query: группировка, анпивот, объединение таблиц по горизонтали, транспонирование.
13	Нарастающий итог 4, много группировок	В этом уроке мы изучим еще 1 способ создать столбец нарастающего итога в Power Query. В отличие от других способов здесь мы не будем пользоваться формулами. Все сделаем при помощи пользовательского интерфейса.
14	Количество позиций в строке	В этом уроке мы посчитаем количество наименований, перечисленных в одной ячейке.
15	List Contains, Пользовательская функция	В этом уроке повторим создание пользовательских функций и рассмотрим еще 1 пример использования функции List.Contains.
16	ABC анализ	Научимся выполнять ABC анализ в Power Query.