Как ссылаться на отдельную ячейку таблицы в Power Query?

Адаптированный перевод текста Referencing Individual Cell Values From Tables In Power Query, автор Chris Webb (Крис Вебб).

Об авторе

Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.

Крис любезно разрешил нам переводить его статьи на русский язык. И мы представляем первую статью.

Данная статья относится к надстройке Power Query в Excel 2010/2013, к группе Скачать и преобразовать вкладки Данные в Excel 2016, и к экрану Get Data в Power BI Desktop. Термин «Power Query» используется в том же контексте, что и в предыдущих статьях.

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

Ссылка на значение ячейки в Редакторе запросов

Предположим, что источник данных – таблица Excel, подобная такой:

p1.jpg

Импортируем её в Power Query. Чтобы получить данные из ячейки второго столбца второй строки щелкаем по ней ПКМ и выбираем пункт Детализация углублением:

p2.jpg

Готово, мы получили 5 в ответе:

p3.jpg

Обратите внимание, что это значение 5 не то же самое, что и в ячейке таблицы. Запрос Power Query может вернуть любой тип данных. В данном случае будет возвращено значение целочисленного типа, а не значение типа таблица. Если вывести результаты этого запроса на лист Excel, то мы увидим отформатированную таблицу. Но если использовать результаты этого запроса в качестве входных данных для другого (например, как фильтр в SQL-запросе), то иметь данные целочисленного типа удобнее, чем таблицу из одной строки и столбца.

Ссылка на значение ячейки в Редакторе кода

Вот код для действий на скриншотах выше. Вероятно, вы догадались как он работает.

let  
Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
"Измененный тип" = Table.TransformColumnTypes(  
Источник,  
{{"poleA", Int64.Type},  
{"poleB", Int64.Type},  
{"poleC", Int64.Type}}  
),  
poleB = #"Измененный тип"{1}[poleB] in poleB  

Рассмотрим эти три шага: – Источник – получаем данные из таблицы Excel
– “Измененный тип” – устанавливаем тип данных для трех столбцов в целочисленный
– poleB – возвращает значение ячейки из второй строки столбца В (строки начинаются с 0).

Код позволяет ссылаться на отдельные ячейки таблицы, используя систему координат из имени столбца и номера строки, еще раз повторим, что нумерация строк начинается с нуля. Поэтому выражение:

“Измененный тип”{1}[poleB]

вернёт значение ячейки из второй строки столбца poleB., т.е 5. Аналогично, выражение

“Измененный тип”{0}[poleC]

вернёт значение 3, соответствующее первой строке столбца poleC.

Отметим, что ссылки на столбец и строку могут идти в любом порядке, и выражение #”Измененный тип”{1}[poleB] вернёт то же самое, что и

“Измененный тип”[poleB]{1}

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

Ссылка на отсутствующие строку или столбец

Что произойдёт если использовать ссылку на отсутствующий столбец и/или строку? Конечно, мы получим сообщение об ошибке. Вернёмся к нашему примеру и запишем:

“Измененный тип”{4)[poleB]

и

“Измененный тип”{1)[poleD]

Оба выражения вернут ошибку, т.к. в таблице нет 5-ой строки и столбца poleD.

p4.jpg

Однако вместо ошибки можно получить значение NULL, используя оператор “?” после ссылки. Например, выражение

“Измененный тип”{1}[poleD]?

вернёт null вместо сообщения об ошибке:

p5.jpg

Но будьте осторожны! Выражение

“Измененный тип”{4}?[poleB]

по-прежнему возвращает ошибку, но не потому что отсутствует пятая строка, а потому что ссылка на пятую строку вернёт null, а у него нет столбца poleB.

p6.jpg

Решением может быть изменение порядка ссылок:

“Измененный тип”[poleB]{4}?

или применение оператора “?” для обеих ссылок:

“Измененный тип”{4}?[poleB]?

p7.jpg

К сожалению, применение оператора “?” не позволит избежать ошибок, если использовать отрицательные значения в ссылках строк.

Эффект первичного ключа

Знаете ли вы, что таблицы Power Query могут содержать первичный ключ (т. е. один или несколько столбцов, значения которых уникально идентифицируют каждую строку), определяемый самой надстройкой? Нет? Неудивительно, это вовсе не очевидно из пользовательского интерфейса. Однако, существует несколько ситуаций, когда Power Query определяет первичный ключ для таблицы, в том числе:

  1. Когда импортируются данные из таблицы реляционной базы данных, подобной SQL Server, и таблица уже имеет первичный ключ.
  2. Когда используется кнопка Удалить повторения чтобы убрать повторяющиеся значения из столбца или столбцов, скрытно вызывается функция Table.Distinct()
  3. Когда к таблице применяется функция Table.AddKey()

Наличие первичного ключа влияет на работу функционала пункта Детализация углублением, и даёт другой способ ссылок на отдельные ячейки.

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

p8.jpg

Если вы загрузите таблицу в Power Query, щелкните ПКМ по заголовку столбца poleKey и выберете пункт Удалить повторения, то установите этот столбец первичным ключом.

p9.jpg

(Кстати, можно использовать функцию Table.Keys(), чтобы увидеть, какие ключи определены для таблицы Power Query).

Убрав дубликаты, повторим действия с пунктом Детализация углублением. Получим следующее:

p10.jpg

let  
Источник = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],  
"Измененный тип" = Table.TransformColumnTypes(  
Источник,  
{{"poleA", Int64.Type},  
{"poleB", Int64.Type},  
{"poleC", Int64.Type}}),  
"Удаленные дубликаты" = Table.Distinct(  
"Измененный тип",   
{"poleKey"}),  

"Строка 2" = "Удаленные дубликаты"{[poleKey="Строка 2"]}[poleB]  

in  
"Строка 2

Обратите внимание на последний шаг, это важно! Вместо ссылки по номеру строки идёт ссылка по первичному ключу.

“Удаленные дубликаты”{[poleKey=”Строка 2”]}[poleB]

p11.jpg

Можно продолжать использовать нотацию на основе номера строки, но если таблица имеет столбец с первичными ключами, то можно использовать нотацию с первичным ключом.

Замечания напоследок о производительности

Возможность ссылок на отдельные значения невероятно полезна в определенных типах запросов и расчётов. Однако стоит помнить, что зачастую существует несколько способов решения задачи, и не все они одинаково хороши.
Одно очевидное применение техники описанной в статье – запись предыдущих вычислений там, где необходимы ссылки на значения предыдущей строки таблицы. Но по опыту известно, что запись расчетов, использующих ссылки на строку/столбец не даёт осуществлять Query Folding («квэри фолдинг» — термин, означающий передачу тяжелых операций по обработке запросов на сторону сервера при работе с совместимой базой данных, на текущий момент это MS SQl, прим. пер.), и ведет к снижению производительности.
Возможно, альтернативные подходы (некоторые описаны в статьях Implementing Common Calculations In Power Query и Join Conditions in Power Query, Part 2: Events-In-Progress, Performance and Query Folding) будут лучшим выходом.

Нет каких-то общих правил, которые можно посоветовать, вы должны сами попробовать разные способы.

Следующая статья

Комментирование закрыто.

Новогодняя распродажа курсов по Power BI

Подписка на рассылку NeedForData

Подпишитесь на нашу рассылку, чтобы не пропустить новые статьи!

Подписка на рассылку NeedForData

Бесплатная шпаргалка по визуализации
Плакат Как что визуализировать

Плакат Как что визуализировать

© 2018    NeedForData