Performs a detailed analysis of information, provides a selection according to various criteria, integration and visual presentation of information on a special portal powerbi.microsoft.com. Power BI services allow you to integrate data from local databases, Excel files and cloud resources and organize access to them for any number of employees, regardless of their location. Power BI technology provides data presentation in tabular and graphical forms in special dashboards and updates of these dashboards automatically in real time. It is possible to create special content packs for working group or the entire organization, including dashboards, reports, and datasets, and offering each user a personalized view of the most important business metrics.

Supports data import from virtually any source, including work excel books, Power BI Designer files, IBM databases, Oracle, Azure SQL, SQL Analysis Services, Google Analytics, Microsoft Dynamics CRM, Salesforce and others. To speed up your work, you can use pre-configured and ready-to-use dashboards for systems such as Salesforce, Google Analytics, and Dynamics.

Power BI offers 2 options for hosting analytical data:

  1. Cloudy. The user can upload the created reports there and they will be available from anywhere in the world.
  2. Local. In this option, data and reports are stored at the user's workplace, access to them is possible through Microsoft Excel.

Dedicated Power BI Mobile apps for Windows, iOS, and Android enable mobile users to instantly access analytic reports created with Power BI. The alert mechanism informs users of any changes to their data. The settings panel allows you to change the presentation of data: configure filters and slicers, combine with other tables.

Visualization and interactive reporting.

Power BI includes the following data visualization features:

  • Tools for data exploration and dashboard building
  • The mechanism of free movement of elements on the panel
  • Easy publication of finished material in Power BI

powerBI andMicrosoftexcel

When solving problems of analysis Microsoft data Excel can be used with Power BI for the following purposes:

  • Preparing and modeling data using the familiar interface and functionality of Microsoft Excel
  • Publish data to Power BI and view it through Excel
  • Connect "on the fly" to Microsoft Excel workbooks on various resources, including OneDrive

Conditional formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

Introduction to Power BI

What is Power BI?
First of all, I want to clarify that Power BI is not part of Excel, but an independent program for business analysis, which will need to be installed separately on a PC. But it's worth it. Power BI itself includes a large set of tools that allow you to process and display all the necessary information about any company indicators (and not only) using interactive visualization. Simply put, with the help of Power BI, you can easily and quickly, without long training and special skills, create beautiful interactive charts based on almost any data. Now it is very fashionable to call such sets of visualizations "dashboards":

A couple of things to know about Power BI first:


Power BI itself is divided into three applications (although you only need to install one):

  • Power BI Desktop- the main application in which reports are generated
  • Power BI Service(Power BI service) - an online platform in which all created reports are placed and sent for publication to the service
  • Power BI for Mobile is an app for smartphones and tablets that allows you to view reports published to the Power BI service

What you need to do and know before you start working in Power BI

  • First you need to install the Power BI Desktop application, because. it is where all the reports are prepared. You can download it from the link: https://powerbi.microsoft.com/en-us/desktop/
  • As soon as the download starts, you will need to enter your data, including e-mail. The same e-mail must be entered when you first start and register the application. I pay special attention that the e-mail must be corporate. Those. no there [email protected], [email protected], [email protected] etc. Otherwise, it may well turn out that it will be possible to create a report, but it will no longer be published
  • After installation and registration, you can start creating your first reports in Power BI

After starting Power BI, the main window will appear. For starters, it’s enough just to be able to navigate the work areas. On the screenshot below, I tried to highlight the main work areas of this window.


Menu button- opens a menu with access to basic commands and settings, such as Publish, Import and Export, Save, Help, exit account Power BI, etc.

– the main tools for working with queries and visual elements are distributed on the tabs of this panel

Region navigation- the main area is the area of ​​visualizations (the first icon from the top), in which all visual reports are built. But you can also go to the table view to view data and change certain field settings (Data types, Sorting, Adding columns, etc.) and to the visual representation of relationships between tables. You can also set up links here.

Visualizations- this is probably one of the main areas of this window. This is where the buttons for creating charts directly are located (they are called visual elements). Clicking on an icon that reflects an approximate view of a visual element automatically adds such an element to the visualization workspace.

Filters- this area allows you to create filtering of data that enters the visual element. For example, having information on Income and Expenses in the data table, you can use filters to build a visual element only for expenses or only for income. The filter area, in turn, is divided into three sections:

  • Visual Level Filters- filters placed here apply exclusively to the visual element to which this filter belongs
  • Page Level Filters- filters placed here apply to all visual elements of the page to which this filter belongs
  • Report Level Filters- extends its action to all visual elements of all pages of the report

fields- all fields of all query tables are listed here. It is on the basis of the data in this area that visual elements are built. Adding data from the fields to a visual element or filter area is done by simply dragging the mouse.

Just below the workspace there is more sheet navigation area, where you can add a new sheet to the report, rename or delete existing ones.

When creating any request (tab home -To get data) another of the main windows will appear - the query window. For those who have worked in Power Query from Excel, there is hardly anything new there, because. Power BI has completely taken over the work with queries from Power Query:

Menu button– opens a menu with access to basic commands such as Close, Save, as well as data source settings

Ribbon panel with a set of commands- as you might guess, this is the main working area, because. it is on this panel on the tabs (Main, Transformation, Add Column, View) that the main tools for working with queries and data are distributed

Request area- This lists all queries loaded into the current data model. Query names can be changed either by double clicking on the query name in the Query Pane or in Property panes of the current request.

Preview area- displayed here preview the type of data that will be loaded into the model after all our actions. With its help, you can visually see the resulting data set and identify errors.

Formula bar(Formula bar) - the text of the last action performed with the query is displayed here. The text is displayed in the built-in M language and can be changed directly in this line. You can show or hide it from the tab View -Formula bar.
Current Request Properties Panel– the properties of the selected request are collected here. You can change the name of the query, and if you expand the All properties item, you can also give a description of the query and not include the query in the data update (by default, all queries loaded into the model are updated with one Refresh button from the main Power BI window).

Panel of operations performed in the request(Applied steps) is a very handy thing. It displays step by step all the actions applied in the request: starting from loading the data and ending with the last transformation. If some operation in the request was made by mistake or simply the parameters were wrong, you can simply delete the last step (or several). We can say that this feature replaces the rollback of actions in office programs ( ctrl+Z). It is also very convenient to learn the syntax of the M language - you can step by step see what operation is called in this language. You can also see the entire text of the request by going to the tab View -Advanced Editor.

Briefly about the workflow in Power BI:

  • first, the input data is loaded into the model and prepared (given in a form convenient for building visualizations) using Power Query
  • further, based on the prepared data, charts and other visual reports are built by simple drag and drop without complex manipulations
  • generated reports are uploaded to the cloud, where other users can view these reports from a browser or from a mobile application

At the same time, you can work in Power BI without programming skills - to create visualizations, the “button” functionality of the program and built-in DAX formulas (Data Analysis Expressions - data analysis expressions), which are very similar to formulas in Excel, are enough to create visualizations. But for more advanced users, there is a place to turn around - Power BI has its own M programming language, which allows you to expand the standard features well. Plus, it is possible to create your own separate visual elements using the R script editor. And by my visual elements, I mean my own, author's, unique visualizations, and not just some kind of mixed charts (like those in Excel). For those who do not know how or do not want to create it themselves, the collection of such copyright elements is constantly updated on the Power BI website: https://app.powerbi.com/visuals/

Who is Power BI for?
Microsoft believes that Power BI will be useful for a small, medium or large company. I believe that such an application is necessary for everyone who is somehow involved in data analytics and building all kinds of reports.

Table of characteristics and differences between the basic version and Pro:

Power BI Power BI Pro
Basic characteristics
Cloud storage (for storing reports in the Power BI service) per user 1GB 10GB
Create, view, and share personal dashboards and reports YES YES
Create reports in Power BI Desktop YES YES
Creating complex queries in the M query language YES YES
Access to dashboards from iOS, Windows and Android mobile devices YES YES
Pre-installed data processing algorithms from popular services such as Dynamics, Salesforce and Google Analytics YES YES
Import raw data and reports from Excel, CSV, and Power BI Desktop files YES YES
Data update
Scheduled update interval Daily Hourly
Stream data to Power BI dashboards and reports 10,000 lines per hour 1,000,000 lines per hour
Connecting to data sources without manual update and publication of information in Power BI (the data source is accessed when the report is opened and the necessary data is loaded immediately) NO YES
Access to on-premises data sources using a personal gateway (installation on Personal Computer and updating data from Excel and Power BI Desktop) and data management gateway (installed on a server with SQL Analysis Services) NO YES
Collaboration
Collaborate using Office 365 Groups NO YES
Creation, publication and viewing of pre-configured enterprise content sets (creation of a content pack with a set of dashboards that will be available to all users at the organization level or a specific group of users) NO YES
Managing access rights with Active Directory groups NO YES
Sharing queries using the data catalog (ability to share queries through the Power Query add-in in Excel 2013) NO YES
Price Is free 7 450 rubles/year
Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:a lpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

September 2, 2015

You need to prepare a business presentation and you have a couple of days for that. But this can be a very difficult task, even for advanced user, especially when the data is in several sources, in the cloud and on-premises, and your IT department is busy with completely different tasks and cannot help you prepare the presentation.

It would be great to have accessible and understandable tools at hand that would help transform and visualize your data to create a visual presentation to look at the situation with a “fresh eye”.

You're lucky. Power BI is cloud-based business intelligence that helps users visualize and analyze data with greater speed, efficiency, and simplicity. You can sign up on the Power BI website and start using the service for free.

1. Get data in a few clicks

Connect to a data source in minutes. Click the button Getting data on the navigation bar.

Then select the data source: Content Pack, File, Database, or Samples. The data source can be located within your organization, locally or in the cloud, or online services such as Microsoft Dynamics, Salesforce.com, QuickBooks Online, or Google Analytics. Putting data together is very easy.





2. Creation of information panels.

Dashboards allow you to keep track of important information about your organization's activities. The dashboard in Power BI is like a dashboard in a car that displays important information about it: speed, fuel level and engine health, only in this case information about your company is provided.

The dashboard shows local and cloud data in one place. Each tile in the dashboard is a visualization created from data from one or more datasets.

You can see how the information panel is arranged on finished example retail data, which is available in the Power BI service.



How are dashboards created? Everything is simple!

Click on the icon A plus next to the heading Dashboards' and enter a title.


Connect to a data source using data from your reports or datasets that are already available in Power BI, or add a new data source. Pin the tile to the dashboard, change its size and name. Tiles can also be moved around the screen and removed as needed. Experiment with tiles and choose the options that suit you.

3. General access to dashboards



Your colleagues will receive e-mail invitation with a link to the dashboard. When you click on the link, the dashboard is added to your Power BI dashboards.

4. View dashboards on mobile devices

Did a colleague send you a link to a dashboard? Now you can view it on your mobile device. You can explore the data, annotate tiles, and share them with others from anywhere in the world. Power BI is available on devices under iOS control and Android, works for everyone Windows versions 8.1 and Windows 10 devices such as Surface Pro.



Have you heard the expression, “Let the facts speak for themselves”? But sometimes the most fast way to get data is to ask a question. In Power BI, you can query data using natural language.

Enter your request in the field on the information panel. For example: 2014 sales by month and territory as stacked bar.

As you type your query, Power BI selects the best visualizations to display the data for your query, and the visualization dynamically changes as the query changes. Awesome!

6. Create impressive reports

Use Power BI tools to impress your colleagues with an intuitive view of your data. Different visualization options provide an opportunity to take a fresh look at your data and evaluate it in the right way.



To create a new report, select the dataset to open or click right click mouse and select View.

To create clear reports and update them in real time modern business a powerful service for processing information is needed. In the comments to my previous readers, readers have repeatedly asked me to write a manual for working with Microsoft Power BI. In this article, I'll show you how to get started with this platform.

Briefly about the capabilities of Microsoft Power BI

Power BI is a powerful yet free BI platform. Microsoft invests a lot in the development of this product, and therefore updates are often released to expand its capabilities. You can use the following versions of the program:

  • Power BI Desktop designed to develop a data model and reports;
  • Power BI Service- an online analogue that specializes in monitoring and analyzing ready-made reports, so the possibilities for designing and working with data are very limited there.

As a rule, the desktop version is used as a designer, after which the developed file is published to the Power BI Service. For further work, download Power BI Desktop from this link.

For users Microsoft Windows 10 has a standalone version of Power BI Desktop, which you can find by following this link.

We use all the power of Power BI to create analytical tools for business. If you want to save money on building your own business intelligence, please contact:

Where can I download data from?

Power BI has many built-in connectors to various services and databases, with the help of which you can load the required data set from various sources into the program in a matter of minutes, link them together and build consolidated reports and charts. At the time of this writing, all available connectors are divided into four groups:

1. Group "File":

As you can see, in Power BI you can import data from the most famous databases and services using various formats files. After loading information from available sources into Power BI, you have great opportunities for cleaning and transforming data, due to the fact that the platform has a fairly powerful ETL functionality. Downloadable tables can be edited, and based on their columns, create calculated columns and measures - in this way, you can transform information arrays into data sets with the structure necessary to build a visualization.

Having formed a set of data loaded from various sources, many built-in visualization elements will become available to you:

  • stacked bar chart;
  • stacked histogram;
  • grouped bar chart;
  • grouped histogram;
  • normalized bar chart;
  • normalized histogram;
  • schedule;
  • area chart;
  • stacked area chart;
  • linear histogram and stacked histogram;
  • linear histogram and histogram with grouping;
  • waterfall chart;
  • scatter plot;
  • pie chart;
  • tree diagram;
  • map;
  • table;
  • matrix;
  • completed card;
  • funnel;
  • sensor;
  • multiline card;
  • card;
  • key performance indicator;
  • cut;
  • ring chart;
  • r-script visual element (on this moment enabled in the program settings).

All elements have a fairly wide range of settings aimed at changing the desired parameters: color, background, title, borders, and so on. If it's not enough for you standard set, you can upload custom visuals.

Greetings, dear friends, Anton Buduev is with you. we have looked at all the differences in detail free version PowerBI with paid Pro and Premium versions. Let me remind you that the main differences lie in the scaling of the end-to-end analytics system by users or by computing power.

The good news is that you can create a complete end-to-end analytics system for individual use or for small businesses and entrepreneurs with a staff of several people, you can completely in the free version of PowerBI. Which is very, very good. And in continuation of this topic, in this article we will look at the process of free registration and entry into. Since, in order to register there, you need to take into account some of the nuances and subtleties, which we will talk about.

If there are any errors, problems in your formulas, and the results of the formulas are constantly not what you expect and you need help, then the free express course "Quick start in the DAX function and formula language for Power BI and Power Pivot ".

So how do you sign up for PowerBI?

First you need to go to the registration page itself. And here lies the first nuance ... If you go to the main site and click on the button " free registration”, then most likely you will not be able to register, and you will see new page with program download.

Downloading this program is, of course, a good thing. But now we don’t need it and we will consider it in another article, and we will download the program from another place. We, however, now need registration ... And how to register if on the powerbi.com site itself, under the registration button, it comes out completely different (at least at the time of this writing)?

In fact, to register, you need to follow a slightly different link than go to the main PowerBI site. So, to register, follow the link https://powerbi.microsoft.com/ru-ru/landing/signin and go almost to the very bottom of the page to a black banner that says "Sign up and get started":


Next, in the field "Enter the address of the work email. email" You will need to enter your email address. And here lies the second and most important nuance ! Power BI does NOT support email addresses of standard email services such as: mail.ru, list.ru, inbox.ru, yandex.ru, gmail.com, outlook.com, hotmail.com. When entering these emails, you will receive an error:

What email address will work then? All mails from your own domains or sites of your companies in which you work will do..ru in the registration field, then everything will go as it should.

What then to do if you or in your environment do not have any site and, accordingly, there is no mail on a third-party domain? There is an exit! At the time of this writing, registration in Power BI works fine with rambler.ru and bk.ru mails. Register separate emails for yourself there and use them to register in Power BI.

There is another option, to use mail from any other sites. For example, you can take the regional site of the Chelyabinsk region 74.ru or some other sites ... there are actually a lot of them ... and on this site you can also create mail for yourself, like [email protected] Such email address You will definitely already be suitable for registering with Power BI.

So, the last step remains - on the Power BI page, the link to which I gave you above, enter your email address in the registration field and click the "Get Free" button. In a couple of minutes, you will receive the first letter from Microsoft with a verification code, and also, a new page with a form will open in front of you.

In this form on the page you will need to write your data: first name, last name, password (from 8 to 16 characters with the obligatory use of large and small letters of the Latin alphabet, numbers and other symbols), country and verification code that you will receive by email . Press the "Start" button. And the last dialog box will open in front of you, in which you can invite other users to the Power BI service, whose emails are hosted on the same domain as your mail. In this window, the easiest way is to click the "Skip" link in the lower left corner:


That's it, registration in Power BI is completed, you will see the internal cabinet of the free version of the Power BI Service, which we will talk about in the next article.

Sign in to Power BI

In theory, there is nothing to write here. You can sign in to Power BI different ways... From the main site, or secondary ones, simply by clicking on the "Login" or "Login" link in the upper right corner.

But, nevertheless, I decided to write a couple of lines about this for your convenience, perhaps it will be useful to someone. The easiest way to sign in to Power BI is directly through the service's direct link: app.powerbi.com . Make a note of this address or copy it to your browser bookmarks.

By clicking on this direct link, you will either go directly to Personal Area services (if you have already logged in), or you will see a login form with a password, after entering which you will immediately log in to your Power BI account.