БЛОГ ФИНВЕР

Эпоха Power BI

Управленческий учет уже никогда не будет прежним
13 декабря 2018, Елена Позднякова
Приветствую всех, кто работает с информацией! Если Вы еще не знакомы с Power BI, то сегодня Вам предстоит сделать удивительное открытие! Приготовьтесь в тому, что теперь ваша жизнь разделится на "до и после Power BI".
Ведь речь пойдет о новой технологии работы с данными, чем мы все использовали прежде.
Забудьте всё, что Вы знали об excel; забудьте про долгую и нудную обработку таблиц и ячеек.
Встречайте сверхзвуковую скорость и возможности программиста в руках обычного экономиста! И заметьте, Вам не нужно учиться писать программный код!
Наступила новая эпоха, господа! Эпоха Power BI. И управленческий учет уже никогда не будет прежним...

Как вымирают динозавры: макросы, ВПР и сводные таблицы...

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

Макрос - это алгоритм действий, записанный пользователем. Когда при обработке однотипных файлов выполняются однотипные операции, самые продвинутые пользователи программы записывают с помощью макроредактора (встроенного в Excel) последовательность действий. Запись делается один раз и этой последовательности присваивается сочетание горячих клавиш. В дальнейшем, при нажатии этих клавиш, макрос запускается и программа повторяет эти операции с каждым последующим файлом самостоятельно и быстро, уже без участия человека.
ВПР - это функция Excel, которая называется Вертикальный просмотр. Функция ищет заданные значения (или диапазон значений) в столбце и возвращает любые данные из строки, которая соответствует ячейке с найденным значением в столбце. Таким образом, функция ВПР позволяет связать две таблицы.
Сводная таблица - это инструмент обработки данных в Excel. Сводная таблица может автоматически сортировать, рассчитывать суммы и получать средние значения.
Эволюция шла пока мы спали... В то время, как экономисты писали макросы, разбирались с ВПР или учились создавать сводные таблицы, - в Excel появились новые надстройки, которые вывели все эти процессы на принципиально новый уровень.
Но мы тогда об этом не знали.
Сначала в версии Excel 2010 года появилась новая надстройка - Power Query. Это событие прошло незамеченным для большинства пользователей. Дело в том, что это была надстройка, а надстройки в Excel имеют такую особенность, что их нужно дополнительно устанавливать, иначе их в программе не видно. Так вот, Power Query никто не устанавливал, потому что никто про него просто не знал.

Power Query - это технология подключения к данным (ЗАПРОСЫ).
Что он умеет:
1) подключение к данным из разных источников
2) фиксация шагов в редакторе запросов (этот функционал, кстати, похож на макрос, но, в отличие от макроса, который записывается целым куском - здесь конструктор)
3) операции над запросами: запросы можно дублировать, объединять и изменять.
Сами данные Query не изменяет. Использует новый язык с коротким названием: М.
Дальше - больше. Появилась принципиально новая технология для хранения и обработки данных - Power Pivot.

Возможно, Вы и ранее слышали об ограничении числа строк в Excel. Но наверняка не воспринимали это серьезно. Ну когда нам накопить такой документ в 1 048 576 строк?
Однако сейчас такие базы легко возникают. Карта передвижения авто, информация о поведении пользователей на сайте, регистрация других событий. Ограничение давно достигнуто и превышено.

Power Pivot - это технология хранения данных, которая позволяет снять все ограничения и значительно повысить скорость обработки.
Pivot так же, как и Query, является надстройкой и появился он тоже в 2010 году.

Если очень коротко, то Pivot - это прокачанная сводная таблица, которая включает только СТОЛБЦЫ и СВЯЗИ между ними (в отличие от Excel, который использует ячейки).
У Pivota есть свой язык DAX, это язык функций и формул, который отличается от привычного языка формул Excel (но не сильно).

Power Pivot заменил и ВПР, и сводные таблицы на принципиально новом уровне, но и это еще не всё!
В 2013 в Excel появилась надстройка Power View - подсистема для построения интерактивных визуализаций и красивых отчетов.

Спорим, Вы тоже про нее не знали и продолжали создавать деревянные экселевские диаграммы по старинке?
А еще через 2 года, в 2015, все эти 3 повера: Power Query, Power Pivot и Power View - были выделены и объединены в отдельной программе Power BI.

И это уже не Excel, а новый продукт и новый шаг в развитии бизнес-аналитики и котором пойдет речь в нашей статье.

Потрясающие возможности Power BI

Встречайте бесплатный инструмент, который делает из экономиста - волшебника
Лучше один раз увидеть, чем сто раз читать теорию! А еще лучше увидеть три раза :)
Поэтому я подготовила для Вас 3 примера визуализаций, созданных в Power BI, и только потом начнется теоретическая часть.
Смотрите пример визуализации
"Золотое кольцо России"
Золотое кольцо - это туристический маршрут по древним городам, в которых сохранились уникальные архитектурные памятники
Визуализация начинается с карты, на которой расположены 8 основных городов Золотого кольца. Размер метки на карте зависит от численности населения в городе. При наведении мышки на город, всплывает подсказка с названием города и численностью населения. Можно изменять масштаб карты с помощью колеса прокрутки мыши.
На карточке справа можно отметить дополнительно Расширенное кольцо, в которое входят еще 17 городов.
При этом, в зависимости от выбранного среза (Золотое кольцо/Расширенное кольцо), карта автомасштабируется и в левом поле отображается полный перечень выбранных городов.
Обратите внимание, что желтые карточки - это срезы (или фильтры), а голубые карточки - это данные: число выбранных городов и численность населения. Данные на странице изменяются в зависимости от выбранного среза.
Эту визуализацию я сделала специально, чтобы показать Вам возможности Power BI. Эта визуализация хранится на сайте Power BI и доступна по ссылке.
Основой для визуализации стала экселевская табличка всего из трех колонок. Времени на изготовление такой визуализации нужно совсем немного (при наличии опыта, минут 10 -15).
Ниже можно посмотреть таблицу с данными, на основании которой сделана эта визуализация.
Еще один пример
"Курс доллара за 20 лет"
Высший пилотаж: я взяла данные прямо с сайта Центрального банка и загрузила их в Power BI
На основном графике представлен курс доллара к рублю за 20 лет: с 1998 по 2018 год. При наведении курсора на конкретную точку графика всплывает подсказка с датой и курсом доллара на этот день.
Над графиком слева расположена область с ползунком, передвигая который можно изменять период, отображаемый на графике.
Справа две карточки: минимальный и максимальный курс доллара за выбранный период (данные зависят от выбранного среза).
Эту визуализацию я загрузила прямо к себе на сайт, получив в Power BI html-код.
Загрузить данные из интернета, с сайта cbr.ru, оказалось совсем несложно. При подготовке этой визуализации я воспользовалась видеороликом от Станислава Салостея (там приведена инструкция именно для этого случая). Вот ссылка.
И третий пример
официальный, от разработчика
Это официальный пример от разработчика, он доступен для скачивания на сайте Microsoft
Отчет называется "Продажи и маркетинг". В этом примере анализируется производственная компания VanArsdel Ltd. Из отчета можно узнать долю компании на рынке, объем продукции, продаж и материалов.

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

Если щелкнуть на любой показатель на странице, то страница воспринимает выделение, как фильтр, и все остальные отчеты на странице будут показаны с применением данного фильтра.
Вы и сами можете скачать любой пример PBIX файла и открыть его через свою Power BI: посмотреть, изменить, опубликовать.
А теперь переходим к теории. Не удивляйтесь тому, что я несколько раз, в разных вариациях, повторю в данной статье описание и объяснение терминов, типа Query, DAX, Pivot...
Я хочу, чтобы они стали для Вас старыми друзьями уже сегодня (хотя вчера Вы про них даже и не слышали). Иначе будет сильный соблазн бросить эту тему из-за большого количества непонятных и незнакомых терминов.
А кто это тут незнакомый? Query? Да это же обычная надстройка для Excel! :) :) :)
Идею Вы поняли, приступим.

Основные возможности Power BI

Читать обязательно и очень внимательно, здесь самая суть (без этой основы Вы дальше запутаетесь)!
Итак, очень коротко, что умеет программа и как она это делает:
1
Power BI забирает любые данные из любых источников в формате "как есть" и преобразует в нужный формат
Это делает модуль Power Query (редактор запросов).
Данные можно забирать из файлов с локального компьютера, из интернета, из бухгалтерской программы, из SQL-сервера и так далее (есть пустой запрос, поэтому можно забрать любые данные откуда угодно).

Все выбранные источники программа запоминает и устанавливает с ними связь (для возможности дальнейшего обновления).

Далее Power Query преобразует данные из формата "как есть" в формат модели данных.
Модель данных - это несколько столбцовых таблиц, связанных между собой логическими связями. Столбцовые таблицы - это формат хранения информации (адрес - только столбец, в отличие от excel, где адрес состоит из столбца и строки).
Алгоритм преобразования выполняется пользователем один раз, путем выполнения последовательных действий - как макрос. В процессе Power Query запоминает все действия и записывает программный код. Запрос можно изменять.

Впоследствии, при нажатии кнопки "Обновить" все данные из выбранных источников обновляются в базе данных.
Возможна настройка автоматического обновления и потоковых данных.
2
Power BI хранит полученную базу данных и выполняет любые операции над данными помощью функций и формул
Базу данных хранит модуль Power Pivot (этого названия Вы не увидите в программе Power BI, но все же знайте, что он там есть!).

База данных называется МОДЕЛЬ ДАННЫХ. Модель включает таблицы. Таблицы включают столбцы. Между таблицами устанавливаются связи (через столбцы). Устанавливать связи не обязательно, программа допускает наличие НЕ связанных таблиц.

Еще раз, так как это важно:
Данные хранятся в формате Таблиц. В каждую таблицу входит любое число столбцов с данными. Этот раздел называется База данных.
Между таблицами возможно установление логических связей, через столбцы-справочники. Этот раздел называется Связи.

С данными можно совершать любые операции. Функционал мощнейший.
Формулы построены на основе языка DAX. Это язык функций и формул. Формулы ссылаются на конкретные столбцы и таблицы.

Язык DAX похож на язык формул excel, но все же он другой.
3
Power BI создает красивые интерактивные отчеты на основе базы данных
Отчеты в Power BI получили название визуализации.
Несколько визуализаций можно расположить на одном листе. Основная интерактивность заключается в том, что для каждой страницы с несколькими отчетами можно указать срезы (фильтры).
Выбор одной из категорий отчета изменяет срез - и все визуализации на листе изменяются соответствии с этим фильтром.

Типов визуализаций много, они очень красивые и эффектные. Кроме встроенных визуализаций, можно загрузить любые другие визуализации (это очень легко сделать, так как кнопка загрузки доступна прямо из программы).

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

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

"Под капотом" Power BI

Самое понятное объяснение, как это работает
Если вы попробуете разобраться с повер би без предварительной подготовки - будете напоминать блондинку за рулем космического корабля, но как только поймете основы, все окажется очень просто и логично.

Архитектура Power BI

Сразу запомните: программа и сайт - это разные вещи!
Перечисляю, что входит в Power BI:
1
Power BI Desktop - локальная однопользовательская версия.
Это бесплатная программа, которая устанавливается на компьютер. Именно в ней и происходит вся обработка данных!
В Desktop загружаются данные, здесь же хранится база.
Если Вы планируете работать с Power BI, то Вам нужен Desktop.
Посмотреть, как выглядит Power BI Desktop:
Подробностипро Power BI Desktop с сайта разработчика.
СкачатьPower BI Desktop.
Очень важно!!! Когда говорят POWER BI - практически всегда имеют в виду десктоп. И, повторюсь, он совершенно бесплатный.

Чтобы Вам не путаться, всё остальное я объединила в один пункт:
2
Всё остальное
  • Power BI облако
    (официальное название Power BI Services) — это интернет-сайт, на нем размещено приложение для публикации и просмотра отчетов, оно доступно только через интернет. Адрес: https://app.powerbi.com. Чтобы зайти в него, нужно указать свои учетные данные.

    Данное приложение бесплатноe, если пользователь просматривает свои отчеты.

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

    Сразу хочу заметить, что если Вы публикуете свои данные в открытом доступе, то это бесплатно. Как пример, две мои визуализации: "Золотое кольцо России" (размещена на сайте Power BI и доступна по ссылке неограниченному кругу лиц) и "Курс доллара" (размещена здесь, на сайте Финвер).
  • Power BI Mobile
    бесплатное мобильное приложение для просмотра отчетов
  • Power BI Embedded
    специальная редакция сервисов Power BI, ориентированная на разработчиков ПО, желающих использовать BI в собственных программных продуктах, платная
  • Power BI Report Server
    локальный сервер отчётов, очень дорогой!!!
  • Шлюзы PBI
    обеспечивают доступ web-приложений, например, Power BI Services к локальным данным без необходимости ручного обновления
Из всего перечисленного, кроме десктопной программы, максимум, что Вам может понадобиться - это облако и мобильное приложение. - И то, не сразу.

Далее у нас пойдет речь только про Power BI Desktop
3 составляющие Power BI десктоп:
(все они когда-то были надстройками для excel, но сейчас объединены в самостоятельную программу)
Power Query
Редактор запросов
Power Pivot
База данных
Power View
Визуализации
Во время работы в программе всегда задавайте себе вопрос: "В какой из трех составляющих я сейчас нахожусь?"
Шаг 1. Подключение к данным и преобразование модели
В миру: Power Query или макрос нового поколения
Начнем с вопроса, как зайти в редактор Power Query.
1) Кнопка "Изменить запросы" в закладке "Главная" на верхней панели. Откроется редактор Power Query
2) Кнопка "Получить данные". После выбора источника данных появится окно "Навигатор" и варианты: "Загрузить" или "Изменить".
Если нажать "Загрузить", то данные загрузятся в систему и "Редактор Power Query" даже НЕ откроется.
Если нажать "Изменить", откроется "Редактор Power Query".
Вот так он выглядит:
1 - Запросы, 2 - параметры (шаги) запроса, 3 - функция (программный код) каждого шага, 4 - поле предварительного просмотра данных
Основной элемент редактора - это запрос (1).
Запрос - это подключение к источнику данных. Например, к таблице excel на локальном компьютере или к сетевой папке или вообще к стороннему сайту. Запросов может быть несколько.
Новый запрос создается если нажать на "Получить данные" (если не в редакторе) или "Создать источник" (в редакторе).
Запрос можно дублировать, для этого нужно вызвать командное меню нажатием правой кнопки мыши.
Параметры или шаги (2).
Шаги - это этапы алгоритма запроса.
Отображаются те шаги, которые относятся к текущему (выделенному) запросу.
Если переключить запрос, шаги тоже переключатся.
Функция или программный код (3).
Относится к текущему шагу запроса.
Код написан на языке "М". Но не волнуйтесь, учить этот язык не потребуется. Программа сама будет переводить действия пользователя в программный код.
Хотя иногда в коде можно сориентироваться и внести некоторые исправления вручную.
Например, посмотрите на этот код:
Казалось бы, абракадабра, но нет!
Не обязательно быть программистом, чтобы понять, что это ссылка на сайт Банка России (cbr.ru).
Данный код - это путь к сайту. Когда я делала визуализацию "Курсы валют" (которую Вы видели в разделе "Возможности Power BI"), я сходила прямо из программы Power BI Desktop на сайт ЦБ, как на внешний источник, и сделала запрос курсов валюты с 01.01.1998 по текущую дату (это было 08.12.2018). Далее программа автоматически записала код всех этих действий на языке М.
А потом, я этот код открыла, нашла эту текущую дату и изменила в ней год.
Таким образом, запрос больше не ограничен текущей датой и я могу до 8.12.21 им пользоваться и запрашивать данные.

Это было небольшое отступление, а теперь вернемся к нашей первой картинке и под номером 4:
Поле предварительного просмотра данных (4).
Самый важный момент заключается в том, что ЭТО НЕ ДАННЫЕ!!!
Это превью - предварительный просмотр. Например, если база данных большая, будет показано ограниченное число строк.

Поле с данными, так же, как и функция, переключается вместе с переключением шагов запроса и показывает, как будут выглядеть данные после выполнения этого шага.
Как появляются новые шаги?
Для этого нужно сделать любую операцию с данными в поле предварительного просмотра.
Например, взять и удалить столбец. Вот что произойдет:
  • появится шаг "Удаленные столбцы";
  • для данного действия будет записан программный код на языке М, который можно увидеть в строке функций;
  • изменится представление данных: не будет столбца (а если переключиться на предыдущий шаг, то там можно этот столбец увидеть).
Программный код в Power Query записывается аналогично макросу в excel.
Но если сравнить Query с макросом, Query окажется намного круче!
Дело в том, что макрос в excel записывается целым куском, сразу от начала и до конца, если ошибешься, придется переписывать всё. Query - это конструктор. Шаги независимы друг от друга. В Query можно добавлять новые шаги в любое место (не только в конец), менять шаги местами и удалять шаги.
Запомните правило: если меняете шаги в середине запроса, то нужно перейти на последний шаг и посмотреть, как в итоге выглядят данные (вдруг все слетело???).
Кстати, а какая конечная цель создания запроса в Power Query?

Во-первых, это подключение к различным источникам данных, чтобы в дальнейшем была возможность загружать измененные данные,
а во-вторых, преобразование любых данных в столбцовую таблицу.
Столбцовой я называю таблицу, у которой данные расположены в столбцах, в отличие от той таблицы у которой данные размещены и в столбцах и в строках.
Посмотрите, вот пример обычной таблицы:
Здесь данные о товаре расположены в строках, периоды в столбцах, доходы/расходы слеплены в один столбец, а еще, в первом и втором столбцах присутствует объединение ячеек :) :) :)
Хорошая, понятная и (что немаловажно) компактная таблица, но наша база данных с такой таблицей работать не сможет.
Нужно все данные развернуть в столбцы: на каждый вид данных создать столбец.
Посмотрите, как должна выглядеть эта таблица в формате базы данных для Power BI:
Данные о доходах и расходах в формате для Power BI - ТОЛЬКО СТОЛБЦЫ
Вообще, функционал у Power Query достаточно широкий. Я покажу, как выполняются самые основные действия на примере преобразования данной таблицы. Кстати, можете скачать эту таблицу и делать вместе со мной
Пример преобразования таблицы по шагам:
1
Сначала в Power BI Desktop нажимаем "Получить данные" и выбираем "Файл" - "Excel" - "Подключить". Далее выбираем свой файл, выделяем в нём нужный лист или таблицу и нажимаем "Изменить данные". Откроется редактор Power Query:
После загрузки файла мы уже видим первые 3 шага нашего запроса: "Источник", "Навигация", и "Измененный тип"
Шаг "Источник" идет первым в любом запросе. Если встать на него, то в строке функций можно посмотреть, откуда берутся данные (путь).
2
Далее, из-за того, что загруженный файл не был заранее отформатирован, как таблица, заголовки были восприняты, как обычные строки.
Переходим в закладку "Преобразование" и выбираем команду: "Использовать первую строку в качестве заголовков".
3
Теперь нужно исправить то, что все объединения ячеек были сняты (Power Query всегда так делает), и из-за этого появились пустые ячейки. Нужно протянуть значения.
Нажмем в закладке "Преобразование" команду "Заполнить в выбранных столбцах по направлению вниз значения пустых ячеек" (она под стрелкой вниз в разделе "любой столбец").
4
Следующий шаг: нужно перевести даты из заголовка столбцов в построчный формат. Такая команда называется "Отменить свертывание столбцов" (значок с двумя табличками и стрелкой). Команду можно выбрать в двух вариантах "Отменить свертывание столбцов" или "Отменить свертывание других столбцов".
В нашем случае удобнее выделить столбцы "Город", "Наименование" и "Статья" и выбрать команду "Отменить свертывание других столбцов".
Кстати, если что-то пошло не так - удалите шаг и начните снова.
5
Теперь нужно разбить числовые значения на 2 столбца: "Доходы" и "Расходы". Сейчас доходы и расходы сведены в один столбец, чтобы это исправить, используется команда "Столбец сведения". Она очень похожа на предыдущую, но расположена чуть ниже.

Встаем на столбец, значения которого должны стать заголовками. Нажимаем команду "Столбец сведения", появится дополнительное окно, в котором нужно выбрать столбец значений, который будет разбиваться по разным столбцам. В нашем случае столбец так и называется "Значение" и нажимаем ОК.
6
И, наконец, последний шаг - это форматы. Нужно проверить, что слева от названия каждого столбца стоит нужный формат. В нашем случае у даты формат АВС - это текст. Исправим на формат даты.
Выделяем столбец и на вкладке "Преобразование" выбираем тип данных "Дата".
На этом работу в Query мы закончили, идем в закладку "Главная" и нажимаем "Закрыть и применить".
В этот момент формируется база данных, которая физически загружается в программу Power BI.
А как будут происходить обновления этих данных?
Query будет обновлять данные, если они изменятся в исходном отчете. Для обновления не нужно открывать сам редактор, достаточно нажать кнопку "Обновить" на вкладке "Главная".
Если исходный файл хранится на одном компьютере, а отчет будет открыт на другом, то, разумеется, связи с данными будут потеряны и обновить их не получится. Сами данные при этом останутся.
Кстати, можно принудительно разорвать связь с источником: для этого нужно зайти в редактор, встать на запрос, правой кнопкой мыши вызвать меню и снять галочку "Включить обновление отчета".
А мы переходим на следующий шаг, к базе данных. Теперь речь пойдет про Power Pivot.

Шаг 2. База данных и всемогущие функции DAX

Power Pivot собственной персоной
Power Pivot - это база данных, которая хранится в Power BI. Структурными элементами базы данных являются таблицы, в состав таблиц входят столбцы с данными. Между таблицами в составе базы возможно установление связей. К таблицам можно добавлять различные вычисления.
Для вычислений используется язык формул DAX.

Сразу про язык DAX: учить этот язык не требуется, но его нужно понять (это не сложно, так как он очень логичный), а также нужно знать, где взять справочную информацию.
Логику языка я объясню дальше по тексту, а на спровочник дам ссылку.
Нам всем очень повезло, что на сегодня подробный справочник по DAX есть на русском языке (от Антона Будуева, ссылка в конце статьи). Я пользуюсь им сама и рекомендую Вам.
Как зайти в Power Pivot?
В отличие от Query, где был свой редактор, словосочетание Power Pivot нам не встретится в Power BI. Подразумевается, что он есть, но он является составной частью программы и не поименован. Поэтому мы тоже далее будем называть его база данных или модель данных.
Давайте посмотрим, как выглядит модель данных в Power BI:
Есть два режима просмотра: данные (база данных) и связи. В режиме просмотра данных (на скриншоте) видны сами данные, которые физически загружены в программу. Сверху - панель формул, которая позволяет создавать новые данные или вычисления. Справа: поля, которые отражают название таблиц и столбцов.
Кроме уже имеющихся в программе данных, которые мы загрузили ранее, здесь можно создавать дополнительные столбцы с данными и меры (формулы).
В чём отличие столбца и меры?
Столбцы - это рассчитанные данные, которые хранятся в базе данных, они увеличивают "вес" базы.
Меры - это просто формулы, они не увеличивают размер базы.
Именно по этой причине всегда, когда есть выбор, создать столбец или меру, рекомендуется создавать меру.
Есть еще отличия в написании формул, об этом далее.
Создать столбец или меру можно правым щелчком мыши по любой ячейке базы данных или по одному из полей с правой стороны. Команды "Создать меру", "Создать столбец"
Давайте создадим столбец в базе данных из предыдущего примера. Создавая столбец, нужно обязательно прописать формулу в строке формул.
Она включает название столбца, знак равенства и значение. Созданный столбец сразу же будет заполнен на основании этой формулы.
Например, мы хотим заполнить весь столбец значениями 1. Столбец назовем Новый.
Формула:
Новый = 1
Вот так он выглядит:
Если переписать формулу, то столбец перезаполнится.
Давайте перезаполним этот новый столбец. Переименуем название столбца на Страна и заполним текстовыми значениями: "Китай"
Текстовые значения в формулах указываются в кавычках.
Формула:
Страна = "Китай"
Если в написании формулы допущена ошибка, то столбец заполнится значениями #ERROR
Кстати, изменить значения или формулы для столбцов, которые были загружены через Редактор запросов Power Ouery - нельзя. Поле для ввода формулы не активно.
А вот созданные внутри базы данных вычисляемые столбцы и меры можно редактировать сколько угодно.
Теперь давайте напишем формулу для столбца, который будет ссылаться на другой столбец. Например, плановые расходы составляют 80% от доходов.
Формула:
Расходы пл = 'Лист1'[Доходы]*0,8
Чтобы при создании столбца сослаться на другой столбец, нужно указать название таблицы в кавычках и столбец в квадратных скобках. Столбец всегда принадлежит какой-то таблице, поэтому ссылка на таблицы перед названием листа обязательна.
Далее, для сравнения я предлагаю создать меру. Такую же точно: плановые расходы составляют 80% от доходов.
Одинаковые названия столбцам и/или мерам давать нельзя, поэтому меру назовем: Расходы пл мера
Формула:
Расходы пл мера = SUM('Лист1'[Доходы])*0,8

Видите, чем они отличаются?!
В формуле для меры появился оператор SUM() - это функция. Дело в том, что сослаться на столбец при создании меры можно только через функцию.
Когда мы создавали столбец, мы ссылались на другой столбец не используя функцию, так как столбец физически рассчитывается и делает это построчно.
Мера же физически не рассчитывается, это просто формула, поэтому требуется уточнение в виде функции, какую операцию совершить над диапазоном.
Запомните правило: Если мера ссылается на столбец, нужно использовать функцию. Например, SUM( )
Посмотрите, как мера и столбец выглядят в программе:
В списке полей меры выделяются значком калькулятора.
Меры содержатся в таблицах: в списке полей видно, что мера создана в таблице Лист1. Но физически мера к таблице не привязана, и ее можно перенести в другую таблицу (если бы она была в этой базе).
Поэтому, при ссылке на меру ссылаться на таблицу не нужно. Мера указывается в квадратных скобках.

Давайте для примера создадим два столбца, каждый из которых вычитает из плановых расходов 1000 рублей. Просто так, чтобы еще раз сравнить меру и столбец. Один столбец будет ссылаться на столбец Расходы пл, а второй на меру Расходы пл мера.
Формулы такие:
Тест = 'Лист1'[Расходы пл]-1000 - здесь ссылались на столбец
Test = [Расходы пл мера]-1000 - здесь ссылались на меру
Как видите, несмотря на то, что создавать меры можно только через функцию, дальше с ними проще работать, особенно если расчетов много.
Профессионалы в повер би создают меры на каждый столбец, а дальше при создании формул работают только с мерами.
Теперь пришло время рассказать о том, что такое связи.
В нашем примере пока всего одна таблица и никаких связей нет, потому что связи могут быть созданы только между таблицами. Связи создаются через столбцы.
Чтобы показать связи я предлагаю рассмотреть другой пример.
Вот таблица, на основании которой я сделала визуализацию "Золотое кольцо России":
Здесь 3 колонки с показателями, всего одна таблица и в отчете тоже нет связей
А что, если бы у меня в качестве исходных данных было 2 таблицы? В одной численность населения по городам, а в другой - города с указанием типа кольца: Золотое кольцо/Расширенное кольцо.
Чтобы не перебивать руками эти две таблицы в одну, можно установить связь по столбцу город.
Вот так выглядят эти связи, если изобразить их в Excel
А вот так выглядят эти связи в Power BI:
При создании связей между таблицами желательно, чтобы хотя бы один столбец содержал уникальные значения, однако и связь "многие ко многим" может использоваться в Power BI
Самый лучший пример таблицы с уникальными значениями - справочник. В больших моделях данных все связи между таблицами строят через справочники.

Все связанные таблицы воспринимаются моделью данных как единая таблица и на их основе можно создавать красивые, интерактивные визуализации. Пора к ним переходить.
Шаг 3. Создание интерактивной визуализации
которой можно делиться и которая обновляется при изменении данных
Создание визуализации я покажу на нашем первом примере с планшетами и ноутбуками. Я удалила все столбцы, которые мы создали для тренировки и создала меру вот с такой формулой:
Прибыль = SUM('Лист1'[Доходы]) - SUM('Лист1'[Расходы])

А еще я изменила данные в источнике на 2019 год и обновила: теперь у меня в таблице 2019 год.

Посмотрите, что у нас есть в режиме визуализации:
Сразу обращаю ваше внимание, что строка формул и поля - это элементы базы данных, я специально выделила их зеленым цветом.
Все остальное относится к визуализации.
В визуализации может присутствовать 2 типа объектов: отчет и срез.
Срез - это фильтр.
Когда срез данных выбран, то все отчеты на листе преобразуются в соответствии с выбранным фильтром.
Страниц может быть несколько.
Я продемонстрирую пошагово создание этой визаулизации. Для этого я создала новый лист.
1
Сначала все выглядит как чистый лист
Чистый лист. Но это страница №2
2
Нам нужно сделать фильтр по товарам. Мышкой выбираем визуализацию срез, а в разделе поля выделяем галочкой "Наименование" (это поле для среза). На этом этапе все выглядит так:
2
Далее нам нужно настроить наш срез.
Все объекты можно перемещать по странице, менять размер.
Для того, чтобы изменить настройки самого объекта, фон, размер текста, отображение заголовка (в общем, всё-всё-всё), используется блок под выбором визуализаций.
Первая закладка - настройка полей, вторая закладка - формат, и третья - аналитика.
Вот здесь:
Например, по умолчанию для этого объекта стоит единичное выделение: или планшеты, или ноутбуки. Если нам нужно все и сразу, в закладке формат "Единичное выделение" нужно отключить.

Далее изменяем все, что нам нужно, на данном срезе: размер шрифта, расположение на странице и т.д, а также создаем второй аналогичный срез по городам.
3
Далее выбираем визуализацию "Карточка" и меру "Прибыль". Получим вот такую визуализацию:
Данные на этой карточке будут изменяться в зависимости от выбранного среза
4
Теперь визуализация "Гистограмма с группировками".
Вот так она выглядит:
Мы выбрали показатели: дата, доходы и наименование. При выделении галочками, эти поля автоматически встают в поля отчета, но при необходимости, их можно перетащить вручную, если автоматически поле определится некорректно.
Над визуализацией есть поле с дополнительными настройками по детализации, я его выделила красным цветом.
Дальше экспериментируйте самостоятельно, ничего сложного здесь нет, сплошное удовольствие.

А мы идем дальше, потому что нам нужно разобрать некоторые технические моменты. Это важно.

Как происходят обновления отчета

и как не запутаться во всех кнопках "ОБНОВИТЬ"
Как происходят обновления, мы рассмотрим для двух случаев:
1) для первичной публикациии
2) для повторного обновления
1
Публикация
бесплатная локальная
Power BI Desktop
Отчет появится с облаке, если в закладке "Главная" нажать кнопку ОПУБЛИКОВАТЬ
Power BI облако
Отсюда можно получить публичную ссылку или код на внешний сайт. Меню "Файл", пункт "Опубликовать в Интернете"
Публичная ссылка или код на внешний сайт
Ссылки и коды всегда будут синхронизированы с облаком.
1
Обновление
бесплатная локальная
Power BI Desktop
Отчет будет обновлен из подключенного источника, если в закладке "Главная" нажать кнопку ОБНОВИТЬ

Но в облаке остается старый вариант отчета, до тех пор, пока не будет повторно нажата кнопка ОПУБЛИКОВАТЬ
Power BI облако
Кнопка ОБНОВИТЬ, которая расположена в облаке БЕСПОЛЕЗНА, она не обновляет сами данные (просто если отчет уже открыт в облаке, и в это время в декстопе нажали Опубликовать, то его можно обновить, то же самое, как обновить страницу браузера в Интернете)
Публичная ссылка или код на внешний сайт
Ссылки и коды всегда будут синхронизированы с облаком.

Как получить свою бесплатную Power BI,

и немного о платной версии
Ну во-первых, Power BI Desktop - бесплатная программа. Вы легко можете скачать ее себе с официального сайта Microsoft.
При использовании программы нужно зарегистрироваться. Power BI принимает для регистрации только корпоративные почтовые адреса (личную почту на доменах mail.ru, yandex.ru, gmail.com и другие подобные использовать нельзя).
Как можно обойти это ограничение, если у Вас нет корпоративной почты или своего домена?
Есть два варианта:
1) зарегистрироваться с личной почты просто на сайте Microsoft, а потом из личного кабинета получить бесплатную лицензию Power BI
2) подобрать публичный домен почты, который все-таки пропускается Power BI (например, rambler.ru)
В чем выгода Micrisoft от предоставления бесплатной программы?, - спросите Вы.

Ответ следующий.
Бесплатный конструктор Power BI Desktop предназначен только для создания отчета. Просматривать отчет более удобно ПОСЛЕ ЕГО ПУБЛИКАЦИИ, просмотр осуществляется в Power BI облаке. Это приложение бесплатно для одного пользователя.
Бесплатно только для одного пользователя, Карл!
Если несколько сотрудников одной организации планируют совместно просматривать отчеты, то для каждого нужно приобрести лицензию ПРОФ (стоимость лицензии на одного пользователя примерно 650 рублей в месяц).
Но есть и хорошая новость: платной является только конфиденциальная работа. Если Вы готовы поделиться своим отчетом со всем миром, то это бесплатно.
Далее я покажу 4 варианта, как можно использовать полный функционал Power BI бесплатно.
Как использовать полный функционал Power BI
БЕСПЛАТНО
4 варианта
  • 1
    Пользоваться одному
    Для одного пользователя все бесплатно: можно создавать отчеты в Power BI Desktop и просматривать их в облаке без ограничений.
  • 2
    Использовать один логин и пароль на несколько человек
    Это модификация предыдущего метода. Один человек создает отчет в десктопе, публикует его в облаке, а затем дает коллегам свой же логин и пароль для доступа в облако.
  • 3
    Делиться не отчетом, а файлом pbix
    От одного пользователя к другому передается полная копия отчета (с базой данных) в формате PBIX. Файл формата pbix может быть открыт только на десктопе. Получатель открывает полученный отчет и публикует его в своем облаке. Таким образом, у каждого есть этот отчет. Но физически это разные отчеты, хоть они и являются полными копиями.

    Недостатки:
    1) большой вес файла
    2) потеря связи с локальным источником данных (если источник в сети, связь останется)
    3) нет связи между отчетами, при любом изменении нужно заново пересылать "весь файл".
  • 4
    Размещать в открытом доступе
    Это не конфиденциально (любой человек может получить доступ).

    Есть два варианта размещения:
    1) получение публичной ссылки. Ссылку можно пересылать неограниченному кругу лиц, данные размещенные в отчете, который открывается по ссылке будут обновляться при публикации новых данных в облаке. Именно такую ссылку я использовала, чтобы показать Вам отчет "Золотое кольцо России" в разделе "Возможности Power BI"

    2) получение кода для вставки на любо сторонний сайт (именно таким способом я вставила визуализацию "Курс валюты" в раздел "Возможности Power BI")

    Чтобы получить ссылку, нужно зайти в свой аккаунт на сайте https://app.powerbi.com, в раздел "Моя рабочая область" - "Отчеты". Открыть нужный отчет (разумеется, что предварительно он должен быть опубликован из десктопной версии), зайти в меню "Файл" и выбрать пункт "Опубликовать в Интернете". После этого будут созданы ссылки.

Где брать информацию по power BI

или немного о русскоязычных звездах Power BI
В англоязычной среде Power BI очень популярна, а на русском языке информации мало. Русскоязычные звезды в этой области только появляются. Здесь я приведу ссылки на самую полезную и качественную информацию. И, разумеется, это будут только бесплатные материалы.
№1. Microsoft. Официальная информация
жаль, что ее так мало...
-1-
Официальная документация по Power BI на сайте Microsoft
-2-
Официальное видео, которое раньше было опубликовано на Youtube канале Microsoft - Россия. Сейчас оно удалено, но я нашла его на другом канале
№2. Станислав Салостей
Самый известный эксперт по Power BI
-1-
Консалтинговая группа Finalytics.pro. Много полезных видео и статей
-2-
Канал Станислава Салостея про бизнес-аналитику. Существует с 2010 года
№3. Антон Будуев
проект BI - Это просто
-1-
Супер!!! Бесплатный качественный справочник со ссылками на примеры и подробные пояснения
-2-
BI - это просто: Power BI, Pivot, DAX.
Пока на канале 327 подписчиков, но я уверена, что скоро он взлетит, как ракета
-3-
Блог про сквозную BI аналитику. Очень полезный.
Знаете еще крутые бесплатные ресурсы по Power BI? - Пишите в комментариях и я с огромным удовольствием дополню этот раздел!

А на этом все на сегодня, надеюсь, что Вам понравилось путешествие в мир технологий бизнес-аналитики будущего.
Теперь прошу Вас оценить материал и ответить на вопрос:
Стала ли Power BI действительно удивительным открытием?
Понравилась статья?
Поделитесь в соцсетях:

Автор: Елена Позднякова. В бухгалтерии и финансах с 1999 года.
Люблю сложные задачи - и простые решения :)
С 2017 года веду блог Финвер об учете, налогах и автоматизации.
Присоединяйтесь к Финвер в telegram и в контакте.

❤️Приглашаю на мои авторские онлайн-курсы на образовательной платформе Stepik ⏬⏬⏬:

✨ Основы бухгалтерского учета и налогообложения, платный (подробности и промокод на скидку здесь)
Этот курс стал победителем премии Stepik Awards 2022❤️ в номинации «Прорыв года»! ⚡️ курс обновлен с учетом изменений 2024 ⚡️

⚡️⚡️⚡️ СКОРО!!!
✨ ПБУ 18 "Учет расчетов по налогу на прибыль", платный (подробности здесь)

✨ ЗАДАЧА ДНЯ ДЛЯ БУХГАЛТЕРА (2024), бесплатный

✨ БУХГАЛТЕРСКИЕ ПРОВОДКИ с нуля за 5 часов, бесплатный

✨ Большой разбор ФСБУ 25 "Бухгалтерский учет аренды", бесплатный

Интенсив по НДС 2022-2023-2024, бесплатный

✨ Практикум НАЛОГИ С ФОТ 2023-2024, бесплатный

✨ Новогодний квест по бухучету и налогам 2023, бесплатный

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