Как перейти к определенной строке таблицы в Power Query

Скачать файл с исходными данными, используемый в видеоуроке:

  Перейти к указанной строке в Power Query.xslx (40,2 KiB, 272 скачиваний)

На днях на одном форуме появился вопрос "Как в Power Qwery перейти к определенной строке таблицы в редакторе запросов?"
Вопрос хоть и не сложный, но мне он показался интересным и поэтому я решил его рассмотреть подробнее.
Для начала определимся - зачем? Как правило нужно это для того, чтобы посмотреть данные конкретной строки, добраться до которой прокруткой тот еще квест. Скажем, это будет строка номер 562109. В листе Excel это делается просто: в поле адреса(то, что чуть левее строки формул) мы вводим A562109 и жмем Enter:

Все, мы перескочили на нужную строку. В Power Query такого счастья нет и без всяческих ухищрений придется прокручивать таблицу мышкой до мозолей на пальцах.

Вариант 1. Столбец индекса и фильтр
Этот вариант для тех, кто привык все делать кнопками и не пользоваться расширенным редактором.
Добавляем в нашу таблицу столбец индекса с 1:
Добавить столбец(Add Column) -Столбец индекса(Index Column) -От 1(From 1):

и потом в добавленном столбце индекса просто отбираем при помощи фильтра нужный номер строки. Если строка слишком далеко, то надо раскрыть кнопку фильтра -Числовые фильтры -Равно. И указать номер нужной строки:

На мой взгляд самый простой и самый эффективный способ. Но имеет один недостаток: у нас добавляется лишний столбец и меняется итоговое отображение результата запроса. А это не очень хорошо, если строку нам надо время от времени смотреть и анализировать, но при этом так же иметь возможность работать с изначальной таблицей дальше.

Вариант 2. Расширенный редактор
Этот вариант подразумевает добавление шага через расширенный редактор. Как попасть в расширенный редактор:
Главная(Home) -Расширенный редактор(Advanced Editor)

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

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Наименование", type text}, {"Производитель", type text}})
in
    #"Changed Type"

В итоге в запрос загрузится таблица:

И далее есть несколько способов отображения нужной строки. Я буду показывать на примере вывода 10-ой строки.

1. Используем функцию Table.Skip
Прописываем собственный шаг в расширенном редакторе после загрузки таблицы(хотя это может быть любой шаг запроса, во время которого нам необходимо получить конкретную строку):

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Skip_row = Table.Skip(Source,9),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Наименование", type text}, {"Производитель", type text}})
in
    #"Changed Type"

Что делает функция Table.Skip: она пропускает указанное количество строк. Иными словами показывает таблицу без первых N строк. Первым аргументом мы указываем таблицу(как правило это шаг в запросе, после которого надо показать нужную строку). Вторым аргументом указываем количество строк, которое необходимо убрать. Т.е., чтобы показать первой строкой строку 10 мы должны задать число на 1 меньше: 9. В итоге мы получим таблицу, которая начинается с нужной нам. По сути желаемое достигнуто - перед нами нужная строка. Но она не одна - после неё идут остальные строки таблицы. Если они не нужны, то можно добавить еще одну функцию - Table.FirstN.

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Skip_row = Table.FirstN(Table.Skip(Source, 9), 1),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Наименование", type text}, {"Производитель", type text}})
in
    #"Changed Type"

Table.FirstN отбирает из указанной таблицы заданное количество строк. Мы указываем 1, т.к. нам нужна только первая строка. Но можно указать и другое число, если нужно вернуть не одну строку, а больше.
И еще один момент: в запросе выше мы добавили шаг Skip_row. И перейдя на этот шаг непосредственно из визуального редактора PowerQuery мы можем видеть только нашу строку, при этом результат запроса останется прежним. Если же цель запроса именно отбор конкретной строки, то надо этот шаг сделать последним и указать что именно его выводить как итоговый:

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Наименование", type text}, {"Производитель", type text}}),
    Skip_row = Table.FirstN(Table.Skip(Source, 9), 1)
in
    Skip_row

Хочу так же отметить, что такой вариант(через Table.FirstN и Table.Skip) можно сделать и с панели:
Главная(Home) -Сократить строки(Reduce Rows) -Удалить строки(Remove Rows) -Удаление верхних строк(Remove Top Rows). Указываем 9.
Главная(Home) -Сократить строки(Reduce Rows) -Сохранить строки(Keep rows) -Сохранить верхние строки(Keep Top Rows). Указываем 1.

2. Используем возможности индексного обращения к записям
Здесь использовать будем тот же запрос, что и выше:

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Наименование", type text}, {"Производитель", type text}})
in
    #"Changed Type"

В PowerQuery к записям можно обращаться напрямую по их номерам. Только стоит всегда помнить, что нумерация здесь начинается с 0. Поэтому, если нам необходима строка 10 мы должны указать 9:

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    row = Source{9},
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Наименование", type text}, {"Производитель", type text}})
in
    #"Changed Type"

Думаю, здесь все вполне понятно:
- Source - шаг в запросе, после которого надо показать нужную строку
- {9} - номер строки в этой таблице, отсчитывая с 0
Но при таком варианте мы увидим не таблицу, а запись. А разница здесь в том, что по сути мы увидим нашу строку в "перевернутом"(транспонированном) виде:

Чтобы получить строку в привычном виде так, как она есть в таблице, можно использовать функцию Table.FromRecords, которая берет указанный список записей и преобразует его в таблицу:

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    FromRecords_row = Table.FromRecords({Source{9}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Наименование", type text}, {"Производитель", type text}})
in
    #"Changed Type"

В итоге получим то, что и требовалось:

В приложенном к статье файле Вы найдете один запрос со всеми описанными решениями и комментариями напротив них.
Скачать файл с готовым запросом:

  Перейти к указанной строке в Power Query.xslx (40,2 KiB, 272 скачиваний)

    Видеоуроки

Поиск по меткам