Check-moscow.ru

Финансы и учет
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как вставить или показать Power View в Excel?

Power View: исследование, визуализация и представление данных

Важно В Excel для Microsoft 365 Excel 2021 Power View будет удален 12 октября 2021 года. В качестве альтернативы можно использовать интерактивный визуальный эффект, предоставляемый Power BI Desktop, который можно загрузить бесплатно. Вы также можете легко импортировать рабочие книги Excel в Power BI Desktop.

Power View — это интерактивный инструмент для изучения, визуализации и представления данных, который позволяет легко создавать отчеты по мере необходимости. Функцию Power View можно найти в Microsoft Excel 2013, а также в Microsoft SharePoint 2010 и 2013 (как часть надстройки SQL Server 2012 Reporting Services с пакетом обновления 1 (SP1) для корпоративной версии Microsoft SharePoint Server).

Карта с гистограммой в Power View

Функции связанных значений в DAX: RELATED и RELATEDTABLE в Power BI и Power Pivot

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы поговорим про функции RELATED и RELATEDTABLE в Power BI и Power Pivot.

Именно эти функции позволяют, находясь в одной таблице, дотянуться до значений в другой таблице через внутренние связи DAX, настроенные во вкладке «Связи» в Power BI или Excel (Power Pivot).

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция RELATED в Power BI и Power Pivot

RELATED () — находясь в одной таблице, позволяет в рамках контекста строки получить связанное значение из второй таблицы по связи «Многие к одному».

Синтаксис: RELATED ([Столбец])

Ниже представлена формула DAX, в которой используется RELATED.

В Power BI Desktop имеются 2 исходные таблицы «Менеджеры Продажи» и «Менеджеры Отделы»:

Исходные таблицы

Между ними настроена связь по полю «Менеджер» по типу «Многие к одному», то есть, много менеджеров может находится в одном отделе:

Связь многие к одному

Попробуем в таблицу «Менеджеры Продажи» через связь добавить третий столбец [Отделы]:

Столбец Отделы

Для этого создадим в Power BI Desktop во вкладке «Моделирование» вычисляемый столбец и постараемся в его формуле прописать столбец [Отделы] из связанной таблицы «Менеджеры Отделы»:

В этой формуле возникла ошибка:

Ошибка формулы создания столбца

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

Перепишем формулу для столбца, рассчитанного с помощью RELATED :

И вот теперь, все прошло удачно. Функция RELATED позволила нам, находясь в одной таблице, дотянуться до значения другой таблицы по связи «Многие к одному» и создать соответствующий новый столбец:

Успешная работа формулы на основе функции RELATED

Теперь, давайте рассмотрим противоположную ситуацию. Сейчас мы будем находиться в другой таблице «Менеджеры Отделы» и в ней нам нужно будет создать новый столбец с подсчетом количества продаж каждым менеджером.

Читайте так же:
Как заменить первый n символ или n-е вхождение символа другим в Excel?

То есть, нам нужно подсоединиться через связь к таблице «Менеджеры Продажи» и там посчитать количество продаж каждого менеджера. Это количество мы можем посчитать при помощи DAX функции COUNTWROS, которая считает количество строк. Ну а подсоединяться к другой таблице через связь мы будем с помощью RELATED.

Объясним эту формулу:

И мы вновь ошиблись:

Ошибка с использованием DAX функции RELATED

На самом деле ошибка закономерна, так как функция RELATED работает по связи «Многие к одному», что у нас было соблюдено в первом примере и что мы нарушили сейчас. Так как в данном примере у нас уже связь другая, а именно «Один ко многим» (один отдел может в себе содержать много менеджеров) и с этой связью RELATED уже не работает.

Именно в этот момент появляется вторая функция связи DAX: RELATEDTABLE.

DAX функция RELATEDTABLE в Power BI и Power Pivot

RELATEDTABLE () — находясь в одной таблице, возвращает связанную таблицу значений из второй таблицы по связи «Один ко многим», где одна строка соответствуем многим строкам.

Синтаксис: RELATEDTABLE (‘Таблица’)

R ELATEDTABLE, в отличие от RELATED, не возвращает одно скалярное значение, она возвращает связанную таблицу значений, с помощью которой можно подсчитать количество строк:

Схема работы функции RELATEDTABLE

Уточните формулу из предыдущего примера и замените функцию RELATED на RELATEDTABLE:

Теперь все в порядке — формула для примера таблицы RELATEDTABLE прекрасно работала и вычисляла количество продаж для каждого из менеджеров:

Пример формулы с участием DAX функции RELATEDTABLE

На этом, с разбором функций связи языка DAX в Power BI и Power Pivot — RELATED и RELATEDTABLE, все.

Пожалуйста, оцените этот пункт:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Любые вопросы по материалу этой статьи задавайте ниже в комментариях. Я обязательно отвечу вам. В общем, просто оставляйте свои отзывы здесь, и я буду рад их получить.

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

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

DAX функции REPLACE и SUBSTITUTE в Power BI

Как заменить фрагмент текста на другой в Power BI или Power Pivot? Функции DAX REPLACE и SUBSTITUTE

DAX функции ROW и TOPN

DAX таблицы итоговых и максимальных значений: функции ROW и TOPN (для Power BI и Power Pivot)

Формулы DAX

Формулы и функции DAX в Power BI и Power Pivot: правила форматирования

Как вставить или показать Power View в Excel?

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

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

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

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

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

Читайте так же:
Как добавить вторичную ось в сводную диаграмму в Excel?

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

«Измененный тип»<1>[poleB]

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

«Измененный тип»<0>[poleC]

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

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

«Измененный тип»[poleB]

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

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

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

В результате обе формулы возвращают ошибки, так как в таблице нет столбцов 5-й строки и столбцаD.

При нажатии на значок «?» вместо ошибки вы получите значение NULL. оператор после ссылки. Например, выражение

«Измененный тип»<1>[poleD]?

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

Будьте внимательны. Экспрессия

«Измененный тип»<4>?[poleB]

По-прежнему возвращает ошибку, но не потому, что пятая строка отсутствует, а потому что ссылка на пятую строку возвращает ноль, и у нее нет столбца PoleB.

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

«Измененный тип»[poleB]<4>?

Или путем применения оператора «? на обе ссылки»:

«Измененный тип»<4>?[poleB]?

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

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

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

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

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

После удаления дубликатов повторите операцию с деталью «Углубление». Результат следующий:

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

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

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

Читайте так же:
Как выделить повторяющиеся строки в нескольких столбцах в Excel?

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

Фильтрация Power View

Вы можете отфильтровать значения, отображаемые в Power View, определив критерии фильтрации.

Перейдите на вкладку TABLE (Таблица) в фильтрах.

Нажмите кнопку Количество медалей.

Щелкните значок режима строчного файла справа от счетчика медалей.

Выберите значение больше или равно из выпадающего списка в разделе Отображать элементы со значением больше или равно.

Введите 1000 в поле, расположенное ниже.

Нажмите кнопку применить фильтр.

Приблизительно 1000 медалей или более отображаются под названием поля. В Power View будут отображаться только те записи, в которых Medal Count> = 1000.

Power Query: стероиды для MS Excel и Power BI

image

Как я уже упоминал во введении, я хочу изучить некоторые возможности бесплатного, очень полезного, но малоизвестного дополнения для MS Excel под названием Power Query.

Power Query позволяет брать данные из различных источников (таких как файлы csv, xls, json, текстовые файлы, папки, содержащие эти файлы, различные базы данных, различные API, такие как Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и многие другие), создавать из этих данных повторяющиеся последовательности обработки и загружать их в таблицы Excel или в саму модель данных.

Вот подробная информация обо всех этих замечательных вариантах под катом.

Совместимость и технические подробности

Power Query свободно доступен для всех версий Windows Excel 2010 и 2013 и встроен по умолчанию в Windows Excel 2016. Power Query недоступен для пользователей macOS X (но даже без него Mac Excel неприятен на ощупь, и опытные пользователи, включая меня, в основном работают с обычным Excel через Parallels или запуская его на удаленной машине Windows).

Power Query также встроен в новый продукт бизнес-аналитики Power BI, и ходят слухи, что Power Query будет использоваться и в других продуктах Microsoft. Другими словами, у Power Query светлое будущее, и тем, кто знаком с технологией Microsoft (и не только с ней), пора вступать в игру.

Как оно работает

При установленной программе Power Query в интерфейсе Excel 2010-2013 появляется новая вкладка с тем же названием.

В новом Excel 2016 функция Power Query доступна на вкладке «Данные» в блоке «Получение и преобразование» на вкладке «Данные».

Сначала в интерфейсе Excel мы выбираем конкретный источник данных, из которого хотим получить данные, и открывается само окно Power Query с предварительным просмотром первых нескольких строк загруженных данных (область 1). В верхней части окна находится лента с командами для обработки данных (область 2). А в правой части экрана (область 3) находится панель с последовательностью всех действий, которые применяются к данным.

Возможности Power Query

Power Query имеет множество функций, и я хочу остановиться на некоторых из них.

Power Query замечателен тем, что позволяет подключаться к множеству различных источников данных. Поэтому вы можете загружать данные из файлов CSV, TXT, XML, JSON. Кроме того, процесс выбора параметров для загрузки CSV-файлов более гибкий и удобный, чем в стандартных инструментах Excel: кодировка обычно автоматически выбирается правильно, и вы можете указать символ, разделяющий столбцы.

Читайте так же:
Как добавить / вставить определенный символ каждые x символов в ячейки?

Объединение файлов лежащих в папке

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

Текстовые функции

  1. Разделите столбец по символам или по количеству символов. И в отличие от Excel, вы можете задать максимальное количество столбцов, а также направление подсчета символов: слева, справа.
  2. Изменение регистра ячеек в столбце
  3. Подсчет количества символов в ячейках столбца.

Числовые функции

Столбцы с числовыми значениями можно применять, нажимая кнопки на ленте:

  1. Арифметические операции
  2. Преобразование в степени, вычисление логарифмов, корней, простых чисел
  3. Тригонометрические операции
  4. Округление до заданного значения
  5. Определение четности и т.п.

Функции для работы с датами, временем и продолжительностью

С помощью кнопок на ленте можно применять колонки значений даты и времени:

  1. Автоматическое определение введенного формата даты (большая проблема в Excel)
  2. Получение номера месяца, дня недели, количества дней или часов в периоде и т.п. одним щелчком мыши.

Unpivot — Pivot

В интерфейсе Power Query есть функция «Unpivot», которая позволяет одним щелчком мыши привести данные со столбцовой метрикой по периодам в форму, пригодную для использования в электронных таблицах (я знаю, что это трудно написать — см. пример). Кроме того, существует функция с обратным действием Pivot. Гифка

Операция Merge — смерть ВПР

Функция VLOOKUP в Microsoft Excel является одной из наиболее широко используемых. Одна таблица может быть заполнена данными из другой таблицы с помощью одного ключа. Гораздо более удобной альтернативой именно этой функции в PowerQuery является операция слияния. С помощью этой операции объединение нескольких таблиц в одну по ключу (по простому ключу или по сложному, когда нужно сопоставить несколько столбцов) выполняется буквально за 7 щелчков мыши без ввода с клавиатуры.

Эта операция аналогична соединению в sql и может быть настроена на различные типы соединений — внутреннее (по умолчанию), левое внешнее, правое внешнее, полное внешнее.

Upd.Мне сказали, что Power Query не может выполнять приблизительное соединение, но vpn может. Чистая правда, альтернатив нет. Gif

Подключение к различным базам данных. Query Folding.

Что также замечательно в Power Query, так это то, что он может подключаться к любым базам данных, от MS SQL и MySQL до Postgres и HP Vertica. Предварительный просмотр данных отображается в интерфейсе Power Query, и все операции, выполняемые в этом интерфейсе, прозрачно переводятся на язык запросов к базе данных.

Power Query также имеет концепцию сворачивания запросов: при подключении к совместимой базе данных (в настоящее время MS SQl) Power Query попытается выполнить тяжелую обработку данных на стороне сервера и получит только обработанные данные. Эта функция значительно повышает производительность многих процессов.

Язык программирования “М”

Дополнение Power Query является интерпретатором для нового языка программирования M scripting.

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

Читайте так же:
Как дважды щелкнуть ячейку и добавить 1 к значению этой ячейки в Excel?

Кроме того, мы всегда можем видеть и редактировать код самого написанного скрипта. Результат будет выглядеть следующим образом:

Язык M работает не так, как формулы Excel или MDX, и не так, как Visual Basic. Хотя его очень легко изучить, он открывает огромные возможности для манипулирования данными, которых невозможно достичь с помощью графического интерфейса пользователя.

Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api

Немного освоив язык «М», я смог написать программы на Power Query, которые способны подключаться к API Яндекса. С помощью заданных настроек мы получаем данные из Metarika и Google Analytics. На гитхабе я выложил программы PQYandexMetrika и PQGoogleAnalytics, лицензированные под GPL. Я призываю вас использовать их. Было бы здорово, если бы эти программы могли обновляться энтузиастами.

Для Google Analytics существует множество подобных экспортеров в различных реализациях, но для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным и даже бесплатным 🙂

Power Query может генерировать заголовки для запросов post и get и извлекать данные из Интернета. При должном уровне квалификации Power Query может быть объединен практически с любым API. В своих исследованиях я в основном получаю данные о звонках клиентов из API CallTouch, API службы мониторинга компьютерной активности Rescuetime, а также из анализа веб-страниц для получения фактической информации.

Еще раз про повторяемость и про варианты применения

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

Я работаю в сфере веб-аналитики и контекстной рекламы. С тех пор как я познакомился с Power Query, я провожу больше времени в интерфейсах, чем в самом Excel. Это более удобно для меня. В то же время возросло использование другой замечательной надстройки MS Excel — PowerPivot.

  1. Разбор семантики для жирных проектов,
  2. Подготовка частотных словарей,
  3. Создание дашбордов и отчетов для веб-аналитики для анализа отдельных элементов,
  4. Восстановление целей производительности в системах веб-аналитики, если они не настроены в проекте,
  5. Сглаживание вероятностных прогнозов с помощью методов Андрея Белоусова (+Bayes: ),
  6. Проверка контекстной рекламы с помощью данных статистики K50,
  7. И многие другие специальные аналитические задачи, которые могут быть выполнены только один раз.

Это система bi, о которой я рассказывал в Yac/M 2015 (видео), которую я полностью сделал с помощью Power Query и загрузил данные в PowerPivot.

Пару слов про локализацию

На сайте Microsoft для российских пользователей Power Query загружается по умолчанию с переведенным на русский язык интерфейсом. К счастью, локализаторы не пошли настолько далеко, чтобы перевести язык программирования на русский (как они сделали это с совместной работой формул в Excel), но они очень усложнили жизнь пользователям, используя двусмысленные переводы. И я призываю вас скачать, установить и использовать английскую версию Power Query. Поверьте, это будет гораздо понятнее.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector