Month: September 2016

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
Loading Excel Files from Sharepoint
This is a quick tutorial on how to load Excel files from a SharePoint page. SharePoint is a nice landing place for your data because it can be connected to the PowerBI.com service and thus can be used to schedule refreshes of data within your company (if you already have a SharePoint o365 account).

This tutorial will be a slightly different than my previous tutorials as I don’t have a publicly available SharePoint site that can be used to connect to. So you will have to slightly adapt what I’m presenting to you to fit your particular SharePoint needs.

First you must start off with a SharePoint with a document library that includes an Excel file.

Sharepoint Location

The document library is titled SampleDocs, and the file we want to bring into PowerBI is called SampleData.

Clicking on the Home in the left navigation will take you to the home location of the SharePoint site. Copy down the HTML site address from your browser of this location it should look similar to the following:
```
https://partner.onmicrosoft.com/sites/[Your Site Name]/SitePages/Home.aspx
```
Open up PowerBI Desktop and on the home ribbon click Get Data. Highlight the SharePoint Folder and click Connect to continue.

SharePoint Folder Connection

Upon clicking connect you will be presented with another screen asking for the SharePoint folder location. In the URL window you will add the SharePoint site that we identified above. However, it is important to note that you don’t need the entire web address. Rather PowerBI only needs the specific site name, thus all that needs to be inserted into the URL field is highlighted below in Red.
```
https://partner.onmicrosoft.com/sites/[Your Site Name]/SitePages/Home.aspx
```
The ending “Sitepages/Home.aspx” can be removed.

Enter Shortened Site URL

Clicking ok will present a authentication screen. Depending on your company or SharePoint authentication you will need to enter the credentials to log into the SharePoint Site. You may have to try a couple different connection methods until you are able to properly connect to the SharePoint site. In my example I had to select Organization Account then click the Sign in. I signed in with my credentials given me via my I.T. group. Also, I had to use the drop down to select the proper level to apply the settings. I used the same address as listed above: https://partner.onmicrosoft.com/sites/[Your Site Name]/

User Sign In Page

After signing in click Connect to proceed. PowerBI Desktop will then load all the files from the SharePoint site in a preview window. Click Edit to modify the query.

Query Editor View

We can now see our SampleData File and the folder path. Each document library will be a separate folder path, thus if you have multiple document libraries then you will have all the files in those different folder paths.

Next click the double down arrows to load the excel file.

Load File

Power BI Desktop will then go to the SharePoint site and download the information inside your excel file. For my data I have all the information retained in a table within my excel document. The table name is call MyDataTable. Thus, clicking on the Table link in the MyDataTable row I will be able to open all the data within this table.

Load Table of Data from Excel File

Finally the data is loaded from the excel table. Click Close & Apply on the Home ribbon to load the data into PowerBI.

Note: It is always important to check your columns and verify that your data types are correct. Highlight each column and make sure you select the proper Data Type for each column. Data Type can be found on the Home ribbon.

Final Load Data

Thanks for visiting. Make sure you stop by again for more great tutorials.
September 1, 2016

Month: September 2016

Using Advanced Mapping in ArcGIS Preview

HexBin Plot using R

Digging Deeper with R Visuals for PowerBI

Using R Visuals in Power BI

Loading Excel Files from Sharepoint