Tag: Power BI Desktop

Dynamic Visuals using Buttons
Sometimes, we want the users to see different metrics, but do not want to take up too much space on our page. The scenario we are going to walk through is how to build just one visual (in this case a bar graph). It will include a toggle that allows the user to select their desired calculation, either the sum of Volume, Dollars or Margin.

Final Solution

With buttons, we can change specific visuals on a page. Recently, with the release of conditional formatting on titles and backgrounds, we have some new methods to make this easier for the report author and cleaner for the report consumer.

The Build

Before we start, turn on the selection pane and bookmark pane. They can be turned on by clicking on the View ribbon and checking the correct boxes.

First, we’re going to create our control table. This will be a disassociated table. This table should not have any relationships to any of the other tables in our model. We just need to enter a numeric ID and a description of what we want. Click on the Enter Data button found on the Home ribbon. Enter the following data as shown. Click the OK button to close the Create Table dialog box.

Now that’s set up, we can write our measure. This measure will see what is selected in the Number_ID column of our control table, then return the appropriate calculation. Use a switch statement to select the correct calculation. Create the following measure:
```
Selected Calculation = 
SWITCH(
  SELECTEDVALUE(Control[Number_ID])
   ,1,SUM(Sales[Volume])
   ,2,SUM(Sales[Dollars])
   ,3,Sum(Sales[Margin])
   ,SUM(Sales[Volume])
)
```
Note: See there is a default value listed in the switch statement. The default calculation means that if nothing is selected, SUM( Sales[Volume] ) will be returned. The default value is represented by the last property in the switch statement.

Time to set up our visual. Add a bar graph with Category on the axis and the new measure, Selected Calculation, in the values fields. Then add a slicer for the Number_ID column. The Number_ID column comes from the control table we added earlier.

Switching the slicer can now change the graph to show the different calculations.

The next stage is to add three buttons to the top of the graph. In the Home tab of the ribbon, click Buttons and select Blank. Make sure the outline colors and outline width match on all objects, Buttons and chart outline.

Tip: Make sure you label your buttons in the Selection Pane. The selection pane can be turned on by clicking on the View ribbon and checking the box labeled Selection Pane. To Change the name of the button, double click the name listed in the Selection Pane. Giving a title (such as Button_Volume) will make it easily to see what visual items are on the page.

After this, it’s time to add the bookmarks.

The bookmark pane can be turned on by clicking on the View ribbon and checking the box labeled Bookmark Pane.

Step 1:
- Select a value of 1 in the Number_ID slicer.
- Select the slicer (and only the slicer) in the Selection pane.
- Click “Add Bookmark” in the Bookmarks pane.
Step 2:
- In the Bookmarks pane, right click the bookmark and rename it to Select 1.
- Right click again, and untick “Display” and “Current Page”. Select “Selected Visuals”.
Now repeat step 1 and step 2, but do so with the values of 2 and 3 from Number_ID slicer. Name these bookmarks Select 2 and Select 3. You should finish with three bookmarks, each that filters Number_ID to a different value. You can test the bookmarks by clicking on them once in the bookmark pane.

On Button_Volume, assign the Select 1 bookmark (as Number_ID 1 refers to volume). To do this, click on Button_Volume in the selection pane. In the visualizations pane for this button, go to the property named “Action”. Turn it on, change the type to bookmark, and choose Select 1 in the dropdown.

Repeat for Button_Dollars and assign Select 2. Then for Button_Margin and assign Select 3. Now the buttons can change the graph, but it’s a bit hard to see what is selected.

Add Conditional Formatting

This is where conditional formatting can help us! Select Button_Volume in the selection pane. Then in the visualizations pane, turn on the background property, select the ellipsis and click conditional formatting

Here’s the settings we want:

This is going to apply a rule if the Number_ID selected is 1, to give the button a blue background. As there are no other rules, any other number selected will default to the white.

Now, apply the same steps to the other two buttons, but make the rule “If value is 2” for Dollars, and “If value is 3” for Margin.

To tidy up, hide the slicer and turn the visual headers of all buttons off. You can click on the eye next to the slicer in the selection pane to hide it.

Turn the visual headers off by clicking the button, then in the visualizations pane.

Great! Now the tab shows the selected button and correct measure:

To make it even clearer, apply conditional formatting to the title of the graph. On the graph, open conditional formatting. Set it to field value and use the type field in the control panel.

Using this control table allows for greater flexibility. We can add more calculations, easily edit them or even sync across pages, all without having to re-record any bookmarks.

If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
August 8, 2019
Using Advanced Mapping in ArcGIS Preview
In the September 2016 release of PowerBI, Microsoft introduced a new visual called the ArcGIS Maps preview. For more information on the maps integration you can read the following post from Microsoft. This tutorial will review how to load data using Latitude and Longitude data and map those points on the ArcGIS map.

First, we need to open PowerBI Desktop and then we will load some data. The version of PowerBI Desktop for this tutorial is 2.39.4526.362 64-bit (September, 2016). You can download the latest version of the software here.

On the Home ribbon click on the Get Data button and from the Get Data window select Blank Query. Click Connect to proceed.

Now you will be in the Query Editor, click on the View ribbon and select the Advanced Editor button. The Advanced Editor will now open.

Enter the following code into the Advanced Editor: (you can copy and paste the code directly from this site) Click Done to load the data.
```
let
 Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2016/09/Locations.xlsx"), null, true),
 Locations_Table = Source{[Item="Locations",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(Locations_Table,{{"Event", type text}, {"Attenders", Int64.Type}, {"Zip", Int64.Type}, {"Latitude", type number}, {"Longitude", type number}})
in
 #"Changed Type"
```
Note: this will load an excel file that is hosted on PowerBI.Tips, so make sure you have an internet connection.

Load Query

Re-name your query to Map Data and then on the Home ribbon click Close & Apply.

Load Map Data In PBI

Before working on this tutorial, you will want to make sure you have enabled the ArcGIS map which is in preview.

Click the Menu button to open up the menu options.

PBI Menu Button

This will expose the menu. With the menu open click on Options and Settings and then click on Options.

Selecting Options

Once the Options menu is open, click on Preview Features and then make sure the ArcGIS Maps for PowerBI preview feature is check. Then click OK to close the options menu.

Options Menu

You should now see a new bright blue icon listed in the Visualizations window.

ArcGIS Maps Icon

Click on the ArcGIS visualization and then add the following following columns of data from the Fields window into the visual.

Fields for ArcGIS Map

OK, Wow, seems like a normal map. So, why all the hype? Well, unlike other mapping visualizations, this map enhances the selection methods for points on a map.

By clicking on the square with the black mouse arrow (highlighted with a green box here because the selection tool for the visual uses a red box) You can then click drag a red box across the map to select multiple geographical points on the map.

Highlighting Points on Map

Selecting points on the map will filter other visuals on the page.

Add a Table visual with the following fields:

Table Visual Fields

Now click the Multi-Select button and highlight some points on the map.

Multi-Select Button

Notice how only the selected points are highlighted on the map and the table filters to only those points.

To enhance the map further click the In-Focus Edit Mode button.

In-Focus Edit Mode

Now, the map editor opens. This allows you to change the basemap view, the theme of the map, symbols on the map and adds other data to enhance the coloring of the map.

Click on the Basemap button and then select the Dark Gray Canvas. We have turned the map in to a sort of night mode.

Basemap Change

Have fun here and explore a couple of the other map types.

Next Click on the Map Theme then click on the Heat Map. Alright, this is getting pretty cool.

Heat Map Selection

In the next section Symbol Style you can change the properties of the points on the map. For the heat map you can change the Transparency and the Area of Influence of the points. Each map theme, Location, Heat Map, Size, and Clustering have different Symbol Style properties. So you might want to select a couple different Map Themes and try adjusting the Symbol Styles to see how they change.

Now finally, the best part of the ArcGIS mapping, the Reference Layer. This will blow your mind!

Click the Reference layer button then select a layer to add from the Demographics tab. For this example, I chose the USA Average Household Income.

Household Income Layer

To return to the Report click the Back to Report button in the upper left hand corner of the page view.

Back to Report

The layer feature is by far the most helpful part of this tool. Imagine the time required to collect all that regional demographics data, model it and then to apply it to the mapping visual. The ArcGIS mapping tool is quite impressive.

One other note before we leave. Now that you are back on the report level view. Use your mouse scrolling wheel and zoom in and out on the map visual. Notice the closer you zoom into the data points the more detailed the regional views become. See comparison below:

Zoomed Views

Thanks for following along. Remember to share if you liked this tutorial. See you next week.
September 30, 2016
HexBin Plot using R
Continuing on the theme with R this month, this week tutorial will be to design a hexagonal bin plot. At first you may say what in the world is a hexagonal bin plot. I’m glad you asked, behold a sweet honey comb of data:

Hexagonal Bin Plot

The hexagonal bin plot looks just like a honey comb with different shading. In this plot we have a number of data points with are graphed in two dimensions (Dimension 1, x-axis and Dimension 2, y-axis). Each hexagon square represents a collection of points. Now, if we plot only the points on the same graph we have the following.

Scatter Plot

In the scatter plot, it’s difficult to see the concentration of points and if there is any correlation between the first dimension and the second dimension. By comparison, the hex bin plot counts all the points and plots a heat map. And, if you ask me the hexagonal bin plot just looks better visually. To bring this all together, if we overlay the scatter plot on top of the hexagonal bin plot you can see that the higher concentration of dots are in the shaded areas with darker red.

Plot Overlay

Cool, now lets build some visuals. Lets begin. Tutorial <- Hexagonal Bin Plot (sorry had to interject a bit of R humor here, ignore if you don’t like code humor)

The very first step will be to open the R console and to install a new library called HexBin. Run the following code in the Mircosoft RGui.
```
install.packages("hexbin")
```
This will load the correct library for use within PowerBI.

Install hexbin

Start by opening up PowerBI. Click on the Get Data button on the home ribbon, then select Blank Query. In the Query editor click on the View ribbon and click on the Advanced Editor. Enter the following query into the Advanced Editor:
```
let
 Source = Csv.Document(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2016/09/Hexabin-Data.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
 #"Promoted Headers" = Table.PromoteHeaders(Source),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SampleID", Int64.Type}, {"Xvalues", type number}, {"Yvalues", type number}})
in
 #"Changed Type"
```
This query loads a csv file of data into PowerBI.

Note: For more information on how to open and copy and paste M language into the Advanced Editor you can follow this tutorial, which will walk you though the steps.

After the clicking Done in the Advanced Editor the data will load. Next rename the query to Hexabin Data and then on the Home ribbon click Close & Apply.

Save Query

Next click on the R visual in the Visualizations bar on the right side of the screen. There will likely be a pop up warning you about enabling R Scripts. Click Enable to activate the R script editor. With the R script visual selected on the page add the following columns to the Values field selector.

R Visual Fields

Notice that the R visual is blank at this time. Next add the following R code in the R script editor window. This will tell PowerBI Desktop to load the ggplot2 library and define all the parameters for the plot. I’ve added comments to the code using # symbols.
```
library(ggplot2) #load ggplot2 package

# define the data inputs to ggplot
 # set data for x and y values x=, and y=
 # set the min and max for both the x and y axis, xmin=, xmax=, ymin= and ymax=
 ggplot(dataset, aes(x=Xvalues,y=Yvalues, xmin=40, xmax=90, ymin=10, ymax=30)) +

# define the color of the outline of the hexagons with color=c()
 # using c(#"809FFF") allows for the usage of hexadecimal color codes
 stat_binhex(bins=15, color=c("#D7DADB")) +

# set the graph theme to classic, provides a white background and no grid lines
 # Change font size to 18 by using base_size = 18
 theme_classic(base_size=18) +

# Apply lables to the graph for x and y
 labs(x = "Dimension 1", y = "Dimension 2")+

# change the gradient fill to range from grey to Red
 scale_fill_gradient(low = "grey", high = "red")
```
Click the run button and the code will execute revealing our new plot.

R Script Code

One area of the code that is interesting to change is the section talking about the number of bins. In the code pasted above the code states there are 15 bins.
```
stat_binhex(bins=15, color=c("#D7DADB")) +
```
Try increasing this number and decreasing this number to see what happens with the plot.

Five Bins
```
stat_binhex(bins=5, color=c("#D7DADB")) +
```
Thirty Bins
```
stat_binhex(bins=30, color=c("#D7DADB")) +
```
Well that is it. Thanks for reading through another tutorial. I hope you had fun.

Want to see more R checkout the Microsoft R Script Showcase. If you want to download the PBIX file used to create this visual you can download the file here.

If you want to learn more about R and the different visuals you can build within R check out this great book which helped me learn plotting with R.
September 27, 2016

Digging Deeper with R Visuals for PowerBI

Back by popular demand, we have another great tutorial on using R visuals. There are a number of amazing visuals that have been supplied with the PowerBI desktop tool. However, there are some limitations. For example you can’t merge a scatter plot with a bar chart or with a area chart. In some cases it may be applicable to display one graph with multiple plot types. Now, to be fair Power BI desktop does supply you with a bar chart and line chart, Kudos Microsoft, #Winning…. but, I want more.

This brings me to the need to learn R Visuals in PowerBI. I’ve been interested in learning R and working on understanding how to leverage the drawing capabilities of R inside PowerBI. Microsoft recently deployed the R Script Showcase, which has excellent examples of R scripts. I took it upon myself to start learning. Here is what I came up with.

This is an area plot in the background, a bar chart as a middle layer and dots for each bar. The use case for this type of plot would be to plot sales by item number, sales are in the dark blue bars, and the price is shown as the light blue dots. The area behind the bars represent a running total of all sales for all items. Thus, when you reach item number 10, the area represents 100% of all sales for all items listed.

If you want to download my R visual script included in the sample pbix file you can do so here.

Great, lets start the tutorial.

First you will need to make sure you have installed R on your computer. To see how to do this you can follow my earlier post about installing R from Microsoft Open R project. Once you’ve installed R open up the R console and enter the following code to install the ggplot2 package.

install.packages("ggplot2")

Once complete you can close the R console and enter PowerBI Desktop. First, we will acquire some data to work with. Click on the Home ribbon and then select Enter Data. You will be presented with the Create Table dialog box. Copy and paste the following table of information into the dialog box.

Item	Sales	Price	Customer
1	100	20	Customer A
2	75	25	Customer A
3	20	30	Customer A
4	18	15	Customer A
5	34	26	Customer A
6	12	23	Customer A
7	20	22	Customer A
8	15	19	Customer A
9	10	17	Customer A
10	8	26	Customer A
1	120	21	Customer B
2	80	24	Customer B
3	62	33	Customer B
4	10	15	Customer B
5	12	26	Customer B
6	60	24	Customer B
7	20	23	Customer B
8	10	20	Customer B
9	8	16	Customer B
10	7	20	Customer B

Rename your table to be titled Data Sample.

Click Load to bring in the data into PowerBI.

Next, we will need to create a cumulative calculated column measure using DAX. On the home ribbon click the New Measure button and enter the following DAX expression.

Cumulative = CALCULATE(  sum('Data Sample'[Sales] ) ,   FILTERS(  'Data Sample'[Customer] ) ,  FILTER( all( 'Data Sample' )  ,  'Data Sample'[Item] <= MAX( 'Data Sample'[Item] ) ) )

This creates column value that adds all the sales of the items below the selected row. For example if I’m calculating the cumulative total for item three, the sum() will add every item that is three and lower.

Now, add the R visual by clicking on the R icon in the Visualizations window.

Note: There will be an approval window that will require you to enable the R script visuals. Click Enable to proceed.

While selecting the R visual add the following columns to the Values field in the Visualization window.

Note: After you add the columns to the Values the R visual renders a blank image. Additionally, there is automatic comments entered into the R Script Editor (the # sign is a designation that denotes a text phrase).

Next, enter the following R code into the script editor.

library(ggplot2)   # include this package to use Graphing functions below

ggplot(dataset, aes(xmin=1, x=Item)) +    # Initialize ggplot function, define the x axis with Item data
 geom_ribbon(fill=c("#D7DDE2"),           # Set the color of the Area Plot,
 aes( ymin=0, ymax=Cumulative )) +        # Define the Y-Axis data
 geom_bar(fill=c("#21406D") ,             # Define the color of the Bars
 stat = "identity" ,      # Define the Statatics property of the bars - This is a required field
 width=.6 ,               # Change the bar width to 60% - 1 would be full bar width
 aes( x=Item, y=Sales )) +          # Define the X and Y axis for bars
 geom_point( color=c("#809FFF"),    # Define the color of the dots
 size=4,                  # Define the dot size
 aes( x=Item, y=Price )) +          # Define the X and Y axis values
 theme_classic(base_size=18) +      # Remove unwanted items from plot area such as grid lines and X and Y axis lines, Change font size to 18
 theme( axis.title.x = element_text(colour = "dark grey"),     # Define the X axis text color
 axis.title.y = element_text(colour = "dark grey")) +          # Define the Y axis text color
 labs( x="Item Number", y="Sales")                             # Define the labels of the X and Y Axis

Press the execute R Script button which is located on the right side of the R Script Editor bar.

The R Script will execute and the plot will be generated.

Great, we have completed a R visual. So what, why is this such a big deal. Well, it is because the R Script will execute every time a filter is applied or changed. Lets see it in action.

Add a slicer with the Customer column.

Notice when you select the different customers, either A or B the R script Visual will change to reflect the selected customer.

Now you can write the R script code once and use the filtering that is native in PowerBI to quickly change the data frame supporting the R Visuals.

As always, thanks for following along. Don’t forget to share if you liked this tutorial.

Want to learn more about PowerBI and Using DAX. Check out this great book from Rob Collie talking the power of DAX. The book covers topics applicable for both PowerBI and Power Pivot inside excel. I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.

September 21, 2016

Using R Visuals in Power BI
For those of you who have been hanging around PowerBI for a while you have likely heard about integration with R visuals. No, this isn’t a twisted dream where Power BI now ships with Pirates… Rather, this has been a highly untapped feature.

In a brief summary R or as it is known on its site R Project for Statistical Computing, is a statistical open source software package that enables mathematicians, statisticians, or data scientists to quickly calculate complex analysis. It is the tool of us super nerds. Now R by it’s self isn’t super powerful, it’s the numerous packages that have been developed by people way smarter than me that can do very amazing functions. Packages include functions for forecasting, math functions, statistic functions and best of all charting functions. Well, this may be fine and dandy so what? Well here is the best part. Microsoft has chosen to integrate and support various releases of R into it’s tools. For example R can now be leveraged within SQL server 2016, and now visuals built in R can be leveraged in Power BI Desktop and PowerBI.com. R can also be used to transform and prepare data during a date set load.

The important note here is that Microsoft has released it’s own open version of R. This distribution is called MRAN, and can be found at this site. The MRAN has been slightly tweaked from the R Project. In the Microsoft version of R, (which I will refer to as MRAN) there has been stability fixes and the improved performance (added Multi threaded Performance).

So enough back ground lets fire this thing up.

First you will need to install the latest version of MRAN.

Navigate to the following address https://mran.microsoft.com/ Click the Download button found at the top middle of the page.

mran download page

Note: At the time of this Tutorial the current version of MRAN is 3.3.1, it is likely that this will change since Microsoft is constantly updating this site and releasing new stabilized & enhanced performance versions of R.

Select the platform that you will be using to install MRAN on. I’m using windows, thus I’ll be downloading and installing the top installation version.

Windows Platform of MRAN

Note: If you need additional installation help you can follow / read the documentation provided by Microsoft. It can be found here.

In order to keep this tutorial brief I will assume you know how to install software and have made it through the MRAN installation successfully. Once installed you should have the following program installed in your start menu.

Installation of R

Run the new installation of R. The R installation will open up a console window.

R Console

At the bottom of the console window is a red line where you enter commands. Enter the following code and press enter.

install.packages(“corrplot”)

This will install the proper R package that we will use later in PowerBI. After running this line of code the console will download the correct package and install it on your computer.

Install corrplot Function

At this time you can close the R console program.

Now, open up PowerBI Desktop. Once in PowerBI desktop click on the File Button at the top left hand part of the screen. Next, Click Options and Settings.

PowerBI Options and Settings

Then click on the Options button.

Options Button

Under the Global options menu on the left verify that your new installation of MRAN is listed. PowerBI should automatically detect the installation and show the installation with the current version number in the home directory:

R Home Directory

Seeing the listed installation in the Home Directory verifies that R has been properly installed on your computer. Clicking OK will close the window.

Data Time!! Below is the M Language that can be used in your Query Editor. Copy the code below and enter it into the Advanced Editor found in the Query Editor.
```
let
 Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2016/09/CarDetails.xlsx"), null, true),
 CarData_Table = Source{[Item="CarData",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(CarData_Table,{{"Year", Int64.Type}, {"Make", type text}, {"Model", type text}, {"Liters", type number}, {"Hp", Int64.Type}, {"Cylinders", Int64.Type}, {"MPG City", Int64.Type}, {"MPG Hwy", Int64.Type}})
in
 #"Changed Type"
```
Note: If you want to learn how to enter M language code into the Query Editor follow this Tutorial.

Once you have pasted the code above into the Query Editor it should look like the following:

Advanced Editor

Clicking Done will close the Advanced Editor and you will have data loaded into the Query Editor. You must have an internet connection to connect to this data. Rename your query to Car Data. Then on the Home ribbon click Close & Apply to load the data into the data model.

Car Data in Query Editor

Generate a simple table visual to see our data in table form:

Table Visual

Add an R visual by clicking the R inside the Visualizations bar. When you click on the R visual you will see a pop-up, click Enable to proceed.

Enable R Visuals

Doing this will open up a visual pane on the page and reveal an R script editor at the bottom of the page window.

R Script Editor

While keeping the R visual selected add the following fields to the visual under the Values field:

Add Columns to R Visual

After adding these fields the R Script Editor will update and reveal code which informs you that your data from the selected columns will be added to a dataset.

R Code Script Editor

Next add the following code into the white area below the #dataset <- unique(dataset) statement.

require(“corrplot”)
library(corrplot)

M <- cor(dataset)

corrplot(M, method = “circle”, tl.cex=0.6, tl.srt = 45, tl.col = “black”, type= “upper”, order=”hclust”)

This loads a package called corrplot which allows you to apply a graph that has a correlation plot between metrics. The M <- cor(dataset), takes your data runs a function called cor and then saves the results into a new variable called M.

Next click the Play button icon found on the right of the grey bar on the R Script Editor.

Running the R Script

Success! You have completed a correlation plot using R within PowerBI. Nice job.

Final Plot

Bonus:

If you want to get fancy with this correlation plot you can change the circles to the actual correlation values. Change the last line of the R Script Editor code to the following and press the run script button:

corrplot(M, method = “number”, tl.cex=0.6, tl.srt = 45, tl.col = “black”, type= “upper”, order=”hclust”)

This removes the circles and then populates the correlation plot with numerical values representing the correlation between the various data features.

Correlation Numbers

The blue numbers represent values that have a positive correlation, while the red numbers represent a negative correlation. In practical terms the higher the Horsepower (HP) of the vehicle the lower the Miles per Gallon (MPG) that are realized.
September 15, 2016
Measures – Month to Month Percent Change
I had an interesting comment come up in conversation about how to calculate a percent change within a time series data set. For this instance we have data of employee badges that have been scanned into a building by date. Thus, there is a list of Badge IDs and date fields. See Example of data below:

Employee ID and Dates

Looking at this data I may want to understand an which employees and when do they scan into a building over time. Breaking this down further I may want to review Q1 of 2014 to Q1 of 2015 to see if the employee’s attendance increased or decreased.

Here is the raw data we will be working with, Employee IDs Raw Data. Our first step is to Load this data into PowerBI. I have already generated the Advanced Editor query to load this file. You can use the following code to load the Employee ID data:
```
let
 Source = Csv.Document(File.Contents("C:\Users\Mike\Desktop\Employee IDs.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
 #"Promoted Headers" = Table.PromoteHeaders(Source),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee ID", Int64.Type}, {"Date", type date}}),
 #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
 #"Calculated Start of Month" = Table.TransformColumns(#"Sorted Rows1",{{"Date", Date.StartOfMonth, type date}}),
 #"Grouped Rows" = Table.Group(#"Calculated Start of Month", {"Date"}, {{"Scans", each List.Sum([Employee ID]), type number}})
in
 #"Grouped Rows"
```
Note: I have highlighted Mike in red because this is custom to my computer, thus, when you’re using this code you will want to change the file location for your computer. For this example I extracted the Employee ID.csv file to my desktop. For more help on using the advanced editor reference this tutorial on how to open the advance editor and change the code, located here.

Next name the query Employee IDs, then Close & Apply on the Home ribbon to load the data.

Close and Apply

Next we will build a series of measures that will calculate our time ranges which we will use to calculate our Percent Change (% Change) from month to month.

Now build the following measures:

Total Scans, sums up the total numbers of badge scans.
```
Total Scans = SUM('Employee IDs'[Scans])
```
Prior Month Scans, calculates the sum of all scans from the prior month. Note we use the PreviousMonth() DAX formula.
```
Prior Month Scans = CALCULATE([Total Scans], PREVIOUSMONTH('Employee IDs'[Date]))
```
Finally we calculate the % change between the actual month, and the previous month with the % Change measure.
```
% Change = DIVIDE([Total Scans], [Prior Month Scans], blank())-1
```
Completing the new measures your Fields list should look like the following:

New Measures Created

Now we are ready to build some visuals. First we will build a table like the following to show you how the data is being calculated in our measures.

Table of Dates

When we first add the Date field to the chart we have a list of dates by Year, Quarter, Month, and Day. This is not what we want. Rather we would like to just see the actual date values. To change this click the down arrow next to the field labeled Date and then select from the drop down the Date field. This will change the date field to be viewed as an actual date and not a date hierarchy.

Change from Date Hierarchy

Now add the Total Scans, Prior Month Scans, and % Change measures. Your table should now look like the following:

Date Table

The column that has % Change does not look right, so highlight the measure called % Change and on the Modeling ribbon change the Format to Percentage.

Change Percentage Format

Finally now note what is happening in the table with the counts totaled next to each other.

Final Table

Now adding a Bar chart will yield the following. Add the proper fields to the visual. When your done your chart should look like the following:

Add Bar Chart

To add a bit of flair to the chart you can select the Properties button on the Visualizations pane. Open the Data Colors section change the minimum color to red, the maximum color to green and then type the numbers in the Min, Center and Max.

Changing Bar Chart Colors

Well, that is it, Thanks for stopping by. Make sure to share if you like what you see. Till next week.

Want to learn more about PowerBI and Using DAX. Check out this great book from Rob Collie talking the power of DAX. The book covers topics applicable for both PowerBI and Power Pivot inside excel. I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.
July 14, 2016
Measures – Calculating % Change
In our last post we built our first measure to make calculated buckets for our data, found here. For this tutorial we will explore the power making measures using Data Analysis Expressions (DAX).

When starting out in Power BI one area where I really struggled was how to created % change calculations. This seems like a simple ask and it is if you know DAX.

Alright lets go find some data. We are going to go grab data from Wikipedia again. I know, the data isn’t to reliable but it is fun to play with something that resembles real data. Below is the source of data:
```
https://en.wikipedia.org/wiki/Automotive_industry
```
To acquire the data from Wikipedia refer to this tutorial on the process. Use the Get Data button, click on Other on the left, select the first item, Web. Enter the webpage provided above in the URL box. Click OK to load the data from the webpage. For our analysis we will be completing a year over year percent change. Thus, select the table labeled By Year[edit]. Data should look like the following:

Global Auto Production Wikipedia

This is the total number of automotive vehicles made each year globally from 1997 to 2014. Click Edit to edit the data before it loads into the data model. While in the Query Editor remove the two columns labeled % Change and Source. Change the Name to be Global Production. Your data will look like the following:

Global Production Data

Click Close & Apply on the Home ribbon to load the data into the Data Model.

Add a quick visual to see the global production. Click the Area Chart icon, and add the following fields to the visual, Axis = Year, Values = Production. Your visual should look something like this:

Area Chart of Global Production

Next we will add a table to see all the individual values for each year. Click the Table visual to add a blank table to the page. Add Both Year and Production to the Values field of the visual. Notice how we have a total for both the year and production volumes. Click the triangle next to Year and change the drop down to Don’t summarize.

Change to Don’t Summarize

This will remove the totaled amount in the year column and will now show each year with the total of Global Production for each year. Your table visual should now look like the following:

Table of Global Production

Now that we have the set up lets calculate some measures with DAX. Click on the button called New Measure on the Home ribbon. The formula bar will appear. We will first calculate the total production for 2014. We will build on this equation to create the percent change. Use the following equation to calculate the sum of all the items in the production column that have a year value of 2014.
```
Total 2014 = CALCULATE(sum('Global Production'[Production]),FILTER('Global Production','Global Production'[Year] = 2014))
```
Note: I know there is only one data point in our data but go alone with me according to the principle. In larger data sets you’ll most likely have multiple numbers for each year, thus you’ll have to make a total for a period time, a year, the month, the week, etc..

This yields a measure that is calculating only the total global production in 2014. Add a Card visual and add our new measure “Total 2014” to the Fields. This shows the visual as follows, we have 90 million vehicles produced in 2014.

2014 Production

Repeat the process above instead use 2013 in the Measure as follows:
```
Total 2013 = CALCULATE(sum('Global Production'[Production]),FILTER('Global Production','Global Production'[Year] = 2013))
```
This creates another measure for all the production in 2013. Below is the Card for the 2013 Production total.

2013 Production

And for my final trick of the evening I’ll calculate the percent change between 2014 and 2013. To to this we will copy the portions of the two previously created measure to create the percent change calculation which follows the formula [(New Value) / (Old Value)]- 1.
```
% Change = CALCULATE(sum('Global Production'[Production]),FILTER('Global Production','Global Production'[Year] = 2014)) / CALCULATE(sum('Global Production'[Production]),FILTER('Global Production','Global Production'[Year] = 2013)) - 1
```
This makes for a long equation but now we have calculated % change between 2013 and 2014.

Percent Change

Wait you say. That seems really small, 0.03 % change is next to nothing. Well, I applaud you for catching that. This number is formatted as a decimal number and not a percentage, even though we labeled it as % change. Click the measure labeled % Change and then Click on the Modeling ribbon. Change the formatting from General to Percentage with one decimal. Notice we now have a percentage.

Change Format to Percentage

Thanks for working along with me. Stay tuned for more on percent change. Next we will work on calculating the percent change dynamically instead of hard coding the year values into the measures.

Want to learn more about PowerBI and Using DAX. Check out this great book from Rob Collie talking the power of DAX. The book covers topics applicable for both PowerBI and Power Pivot inside excel. I’ve personally read it and Rob has a great way of interjecting some fun humor while teaching you the essentials of DAX.

Please share if you liked this tutorial.
May 2, 2016
Map it, Map it Real Good
This tutorial is a real simple mapping exercise. I was talking with a colleague today about Power BI and I was challenged to map something using latitude and longitude. I had played with mapping before but not using latitude or longitude.

I’d have to say if you want to impress someone with your PowerBI skills adding a map is a good way to do so. Typically this a functionality that you can’t add into excel, well at least not with out some serious effort.

Alright, here we go..

Resources for this project are:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
- Excel file with a table in it with our location information that can be downloaded here: Locations Data Set
After downloading the Locations Data Set, Open up PowerBI and load the Excel file into Power BI. If you need to learn how to load Excel files you can follow the loading excel tutorial.

Click the Get Data on the Home ribbon. Select the first option Excel and click Connect at the bottom of the Get Data window.

Navigate to the downloaded file called Locations.xlsx and open the file by clicking Open in the bottom right hand corner.

Next, the navigator window will open. Select the table (denoted with the grid with a blue top header) called Locations. Then Click Load to load the data into the data model.

Navigator Window Selection

Note: there are two different icons in the Navigator window. One is called Locations which is a Table within the Excel document. While the other is called Sheet1, which is simply the first sheet in the excel workbook. For Future references it is much easier to make tables in excel and use them to load data in to PowerBI than using just a worksheet. So whenever possible try to form your data in Excel into Tables. When loading a table the headers of the table automatically load into the column names in the PowerBI data models.

We now have loaded the data into a new Table in PowerBI called Locations.

To make the map check the boxes for Latitude and Longitude. Power BI intelligently understands that latitude and Longitude are mapping functions and we are now presented with a map with tiny blue dots.

Map from our Data

Lets add some more data to enhance the map. We can change the size of the circles at each location by dragging the column called Attenders over to the Values field for this visual.

Change Bubble Size

We have now changed the size of the circles relative to each other to show the number of people that we saw at each location. To add color to the map drag the column called Event to the Legend option of the visual. This yields a map that now has each circle with a different color according to the event name.

Colored Bubbles on a Map

To enhance our visual further we will add a bar chart with the total count of attenders per event. To do this click any where on the visual page (this will de-select the map visual on the page). Now click the Event column and then the Attenders column. This will present you with a table list of events and the corresponding attendees. Leaving the table visual highlighted click the Stacked Bar Chart which is in the upper left hand corner of the Visualizations window.

Adding a Bar Chart

I circled the triple dots on the bar chart. Click the triple dots and a menu will appear. First click Sort By, then click Attenders. This will sort the attenders in descending order from the largest amount at Kohl’s Corp. down to Harley Davidson. Drag the column labeled Event to the visualization option called Legend. This colors the bar chart.

Colored Bar Chart

Note: The colors in the bar chart match the colors in the map we made earlier. This build uniformity in your reports and when your filtering items colors across visuals make sense.

Take some time to click on each of the bars on the bar chart. Notice how the map re-draws with only the data for that selected item. To select multiple bars on the bar chart hold the CTRL button and click on the multiple bars.

Nice job. We have finished the mapping tutorial. Share if you liked it below.
April 14, 2016
Manually Enter Data
There are often times when you need a small data set in order to make a visual behave exactly how you want it to. This may mean you need a small table to represent a range of numbers or text values.

Here are the Resources for this tutorial:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
To enter your own data Click the Enter Data button on the Home ribbon.

Enter Data Button

Next you are prompted with the Create Table window. In this window you are given the layout of a unfilled table. To begin entering data you can click in the first cell in Column one and start entering data. By pressing enter a new cell will populate below. You can Rename the column by double clicking the column name. To add a second column you Click on the * symbol next to your existing column. Finally to edit the table name you can type in the desired table name in the Name input box in the bottom left hand portion of the window.

Create Table Window

Finally, you can either to choose to Load the data as is or Edit the data to make additional changes (this can be useful to edit the data types of each column or to populate equations in subsequent columns). For the sake of this tutorial we will simply load the data. Click Load to load the data into the data model.

Now drag over the columns into the page view to begin generating visuals. By default PowerBI makes a table of data to show you the values you just entered.

Visual of Sales Table

Select the table visual (you know it is highlighted when it has the trim boarder as shown above) and Click the Doughnut Visual. This transforms the data into a doughnut, and who doesn’t like a nice data doughnut? Click anywhere in the page to de-select the new doughnut visual. Add a second table by dragging over he Region and Sales columns. We can now see the pretty graphic and the numbers supporting that visual.

Visuals Made with our Custom Data

I bet you didn’t notice that something changed here. Look closely at the data we see now vs. what we entered earlier. Go ahead, scroll up, I’ll wait… Did you catch it?

We now have 5 rows of data but we entered 6 before. That is because the Sales column is a number column and can be aggregated. Look in the fields column and you see there is a little sum symbol in front of the Sales column. This means that this column has a default summarization associated. To see what is the default summarization highlight Sales by clicking on the column name in the grey area. Then Click the ribbon titled Modeling, and there it is in the properties section the Default Summarization is Sum. Every time you use the Sales column it will be summarized in the tables and visuals views. Our visual table shows Brazil with a total sales of 600, because we had two Regions labeled as Brazil 500 and 100.

Now you can click on any of the data points in the doughnut. Notice the table automatically filters down to only show the areas you selected.

Data Filtered to only Brazil

ProTip: you can select multiple selections by holding down CTRL and selecting multiple items in the visual. You can only do this inside of one visual. As soon as you click another visual all filtering will disappear.

Again, I hope you enjoyed this quick tutorial. If you liked it make sure you share it below.
April 13, 2016
Query Settings – Fixing a Missing File
One of the most important concepts to learn within Power BI Desktop is how to build a Data Model.

Note: In simple terms the Data Model is data that is collected from the get data function. In your data model you can build multiple queries. This data is stored in the file. The data storage is very efficient as the data compressed down to approximately a 4:1 ratio. 1000 KB file will compact down to approximately 250 KB when loaded into Power BI. From my current understanding all data is loaded into the memory of the computer. Thus, if you are having performance issues it could be in part due to the RAM of your computer.

As you begin to craft more data models you will learn little tips and tricks along the way to make an efficient Data Model for your visualizations. I have found that the most challenging part of building the data model is structuring the data in a way that will make your selected visual make sense. This may mean you need to add a measure or a calculated column or a ranking to a data set. Alright lets get started.

Here are the Resources for this tutorial:
- Power BI Desktop (I’m using the March 2016 version, 2.33.4337.281) download the latest version from Microsoft Here.
- We are going to work through the Power BI Desktop file that we built in the Loading Excel Files Into Power BI Tutorial. You can follow the link to create the Power BI Desktop (pbix) file in the tutorial. For convenience, the completed file can be downloaded here: Import Excel Tutorial.
I’m going to start off by extracting the Import Excel Tutorial.zip file to my desktop. Once the file has been extracted we can open the containing folder. In this folder there are two files the source data in the excel file and the Power BI Desktop file.

Note: A Power BI Desktop file has a .pbix file format ending.

Open the Import Excel.pbix file. First click the Home ribbon and then click the Refresh button. Most likely there will be an error similar to the following message.

Message box when file can’t be found

This type of message occurs when you refresh a query and the file is missing or can’t be found. This is because when i originally built the Power BI Desktop tutorial the excel file that is supply the information was located on my desktop. This is a common problem when you build connections to local files stored on your computer. If you move a file into a different folder then the connection will break.

To resolve this close the message window by clicking Close. on the Home ribbon click the Edit Queries button. The Query Editor window will be presented. In a large yellow bar in the data view portion of the window (circled in red) is the error message.

Note: Circled in blue is the Query Settings window. This window is the window for all the applied steps to transform the data. You can change the name of the query in the name box. From the view we have selected we can see that the step entitled Changed Type is currently selected (seen circled in blue).

Click the grey button labeled Go To Error which is found in the yellow error box.

Error seen inside Query Editor

Upon clicking the Go To Error button the selection in the Query Settings button to the Source Step. This is where the query has failed. More information about the failure is shown in another yellow error box. This time click Edit Settings in the error box.

Edit Settings in Error window

Now we have the Load Excel file window prompt open. In this window Click Browse, navigate to where you extracted all the files downloaded earlier in the tutorial and select the excel document entitled Book1. Click Open and the new file location will be loaded into the Load Excl Window. Click OK to complete the settings change.

New File Location

Now the data is correctly loaded into the data model. Notice we are still on the step called Source. Take some time to click through each step, Source – Navigation – Promoted Headers – Changed Type. As you click on each step you can see how the data is transforming.

To see the code that is being used to make each step click the View ribbon and check the little box entitled Formula Bar. This will make a formula bar appear. When you click on a step the formula bar will reveal the code needed to complete the selected step.

Toggling on the Formula Bar

We can now see the equation, which is similar to how you would write an equation in excel. The code in the Changed Type step is here:
```
= Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Sales", Int64.Type}, {"Category", type text}})
```
The equation is using the M language to transform the data. More information on the usage of the M language can be found here.

Note: Couple of pointers about the data shown in the formula. The function is called Table.TranformColumnTypes. The source of the data is a variable called #”Promoted Headers”. The pound sign and the words following in quotations is how the M language passes variables that have a space contained in the language. Since the prior step has the name “Promoted (space) Headers” the program has to add the pound sign and the quotation marks. If there is no space in the naming convention such as “PromotedHeaders” then only the PromotedHeaders would be seen in the code and the pound sign and quotes will be gone. See modify coded when I remove the space from the Promoted Headers applied step.
```
= Table.TransformColumnTypes(PromotedHeaders,{{"ID", Int64.Type}, {"Sales", Int64.Type}, {"Category", type text}})
```
Notice the the pound sign and quotations are missing.

The second part of the formula is an array which has been written out in curly brackets:
```
{
{"ID", Int64.Type}, 
{"Sales", Int64.Type}, 
{"Category", type text}
}
```
I changed the code by adding line returns to make it easier to read. The coded array has beginning bracket and an ending bracket. Each parameter is contained in it’s own curly brackets and separated with a comma. The array is a 2 x 3 array, it has 3 rows and two data points on each row, just like a matrix. The first data point is the column name. In the first row the column that is being address is called ID. The data transformation parameter is called Int64.Type. This means that the data is an integer type 64 bit. This repeats for each row until all parameters have been addressed.

So there you go, we have opened up a query repaired it and learned a little about the formula bar.

As a side note, as you build queries each button press that you make on the various ribbons in the Query Editor will make a minimum of one step the in the Query Editor.

Hope you enjoyed this short tutorial about the Query Editor. Make sure you share below if you liked it.
April 11, 2016

Tag: Power BI Desktop

Final Solution

The Build

Add Conditional Formatting