Simple Report
So, when you start I gonna assume you have got some data, there is a get databutton on the top that allows you to connect to the server or local storage. After you pull the data you will see the set of data or fields you have pulled in. In the middle you have got a nice blank canvas, and this is the place where you can start building visualizations, doing your analysis, and you are given this visualization pane which gives you access to the gallery of visuals that it provides like box, custom visuals about which I will talk in a bit more later down the post. Below it, you can control the look of the visuals of your page. and building things is very simple, you find the fields you are interested in and drag them into the canvas, and it will start creating stuff for you, so if you place like something a measure like sales amount, it will give you a chart, so if I want to slice it by style of car, for example, we will get a little visualization showing that we have been selling lots of Diesel Cars, and lots of Sports Cars, very simple!
Canvas [Left], Visualization Pane [Middle], Fields or Dataset Pane [Right]
Visual Interaction
Let’s try some other ones. Let’s take the sales amount again and slice it using time and let us turn it into a line chart. so converting between different visualization is pretty simple, you just select the one you want from the visualization pane and then finally add something new, type of product, model, make, sales margin etc. and here is my report in just ten to twelve clicks. The report is showing really simple visualization that shows me some of the details of the products we have sold. The great thing about these visualizations is that they are all instantly interactive, I don’t have to do anything else, I can just click on these things and assuming data will come from the same place, things get cross filtered, highlighted, the spike adjusts as per the choice of the bar we select.
The other thing I can do here is start formatting and changing the way this report looks like adding titles is really easy and I can just choose to insert a text box. I can also do drawing like shapes and lines like if I want to put an arrow in to highlight a particular time period. I’ve got a bunch of controls that I can just start to embellish and add things to my report and of course I can go and start changing the format of these charts and visuals as well. so this visualization pane also contains under this formatting tab, all of the different options that you can find here to control whether or not we are showing the axis, what those axes look like, do we have a title for it, things like the colour for the data. Power BI provides all these formattings to give you more beautiful reports.
Simple BI Reporting – Car Sales; Spike and Bar Chart style visualization interaction
Conditional formatting with the same number of gradient colour
You can change the colour of your graph dynamically based on the value in your data. Let’s take a sample data from various gamers who play the game Halo or Destiny. So after pulling the data, we have got various fields of data like ‘number_of_kills’, ‘number_of_times_died’, ‘headshots’, etc.
Drag and drop the fields – ‘number_of_kills’ and ‘gamer_name’ on the canvas then select the bar chart from the visualization pane. We can see the name of the gamer and their respective kills represented by bars. The higher the bar the higher the kills.
To make BI Reporting powerful we can create conditional formatting by setting the gradient colour in the formatting tab. By doing this the intensity of the bar colour will change as per the number of kills, more he kills the stronger the colour. Lesser the kills, lower the colour intensity.
Now if we want a different colour to represent a different state, we have a workaround. We select the ‘max’ colour and ‘min’ colour and the gradient will be created by the in-between shades. We can set the value ‘1’ for each ‘max’, ‘medium’ and ‘min’, and select the ‘max’ colour as green and ‘min’ colour as red. So by doing this, you can see the gamer who has performed maximum kills, his bar will be highlighted as green and the gamer whose kills are the minimum will be represented by a red bar. Microsoft is still working on this feature and making it more user-friendly. You can send the feedback on this feature. More the feedback a feature receives, the more they concentrate on implementing and making that feature better.
Gamer Data – Total Kills; Colour Gradient
Gamer Data – Total Kills; Colour Formatting
Alignment
To make the reports neat we can multi-select all the visualization, go to Formattab at the top. Click on align and select the desired alignment to make everything line up. A simple feature but a great time saver and making our reports beautiful. So no more OCD regarding lining up the charts. 🙂
Power BI and Excel
Use Microsoft Excel and Power BI together and build powerful, consistent reports and visuals.
Get Excel data into Power BI
In this section, we’ll first take a look at how you can send an Excel workbook file containing a simple table from local storage into Power BI. You’ll then learn how you can begin viewing that table’s data in Power BI by building a report.
In order for Power BI to get data from your workbook, that data needs to be formatted as a table. It’s simple. In Excel, you can highlight a range of cells, then go to the Insert tab of the Excel ribbon, then click Table.
Excel Database
Your files do not need to be on a local storage, of course. If you have uploaded your files to OneDrive, Google Drive, Box, or any other cloud storage, that’s even better. OneDrive being the best option since it is from Microsoft and thus provides the best integration.
Importing Data from Local Storage or Cloud
Importing…
After importing data into Power BI, you can begin building reports.
What formats is Power BI available in?
- Power BI desktop—for desktop computers
- Power BI service—an online SaaS (software as a service)
- Mobile Power BI apps—for iOS and Android devices
All of these can be used in conjunction. For example, you might create a report on your desktop, and then publish and share it online so that colleagues on mobile devices can read it.
Describe the building blocks of Power BI.
- Visualizations. A visualization is a chart, graph or similar visual representation of data.
- Datasets. A dataset is the group of data used to create a visualization, such as a column of sales figures. Datasets can be combined and filtered from different sources using built-in connectors.
- Reports. A report is a group of visualizations on one or more pages; for example, charts, graphs, and maps can be combined to create a report.
- Dashboards. A dashboard lets you share a one-page visualization with others, who can then interact with your dashboard.
- Tiles. A tile is a visualization on your dashboard or in your report. As the creator, you can move tiles around.
What are the main components of the Power BI toolkit, and what do they do?
- Power Query: lets you discover, access and consolidate info from different sources
- Power Pivot: a modeling tool
- Power View: a presentation tool for creating charts, tables and more
- Power Map: lets you create geospatial representations of your data
- Power Q&A: lets you use natural language to get answers to questions; for example, “What were the total sales last week?”
What is a content pack, and why would you use one?
A content pack is a ready-made, predefined collection of visualizations and reports using your chosen service (for example, Salesforce). You’d use one when you want to get up and running quickly, instead of creating a report from scratch.
Describe DAX.
DAX stands for Data Analysis Expressions. It’s a collection of functions, operators, and constants used in formulas to calculate and return values. In other words, it helps you create new info from data you already have.
What are the three fundamental concepts of DAX?
- Syntax. This is how the formula is written—that is, the elements that comprise it. The Syntax includes functions such as SUM (used when you want to add figures). If the syntax isn’t correct, you’ll get an error message.
- Functions. These are formulas that use specific values (also known as arguments) in a certain order to perform a calculation, similar to the functions in Excel. The categories of functions are date/time, time intelligence, information, logical, mathematical, statistical, text, parent/child and other.
- Context. There are two types: row context and filter context. Whenever a formula has a function that applies filters to identify a single row in a table, row context comes into play. When one or more filters are applied in a calculation that determines a result or value, filter context comes into play.
Why and how would you use a custom visual file?
You’d use a custom visual file if the prepackaged files don’t fit the needs of your business. Custom visual files are created by developers, and you can import them and use them in the same way as you would the pre-packaged files.
What are some of the most common sources for data in the Get Data menu?
Excel, Power BI datasets, web, text, SQL server and analysis services.
What are the categories of data types?
- All
- File
- Database
- Power BI
- Azure
- Online Services
- Other
Name some commonly used tasks in the Query Editor.
- Connect to data
- Shape and combine data
- Group rows
- Pivot columns
- Create custom columns
- Query formulas
What is grouping, and how would you use it?
Power BI Desktop groups the data in your visuals into chunks. You can, however, define your own groups and bins. For grouping, use Ctrl + click to select multiple elements in the visual. Right-click one of those elements and, from the menu that appears, choose Group. In the Groups window, you can create new groups or modify existing ones.
Describe responsive slicers.
On a report page, you can resize a responsive slicer to different sizes and shapes, and the data contained in it will be rearranged to match. If a visual becomes too small to be useful, an icon representing the visual takes its place, saving space on the report page.
In Power Query, what is query folding?
This is when steps defined in the Query Editor are translated into SQL and executed by the source database, instead of by your own device. It helps with scalability and efficient processing.
Explain the term “M language”.
This is the programming language used in Power Query. It’s a functional, case-sensitive language that’s similar to other programming languages and easy to use.
What are the differences between visual-level filters, page-level filters, and report-level filters?
Visual-level filters filter data within a single visualization. Page-level filters work on an entire page in a report, and different pages can have different filters. Report-level filters filter all the visualizations and pages in the report.
How does the Schedule Refresh feature work?
You can configure an automatic refreshing of data daily or weekly, and at different times. You can schedule only one refresh maximum daily unless you have Power BI Pro. In the Schedule Refresh section, simply use the pulldown menu choices to select frequency, time zone and time of day.
What information is needed to create a map in Power Map?
Power Map can display visualizations that are geographical in nature. Therefore, some kind of location data is needed—for example, city, state, country or latitude and longitude.
