Using R in Power BI to check if file exist

I have done a series of blog posts on how R can be used in Power BI. I have also created quick videos for each of those posts and I will do the same for this post. I find it easier and more efficient to share this information via video than through a narrative with screenshots. In this blog post, I’m going to share the code that can be used to check if a file exist prior to processing of that file. I will record a video that goes in more depth and include that in the blog post later.

Check if file exist

As someone who comes from an enterprise BI background, I am always looking for ways to handle things I would have previously done with SSIS. With R integration we have a lot of new possibilities.

The Code:

fileexists <- FALSE
fileName   <- “C:\\Backup\\Blogs\\R – Check if File Exist\\CustomerSales1.csv

while (fileexists == FALSE)
    {
   #fileexists = TRUE ##Test Expression
   fileexists <-
            if(
                file.exists(fileName))
               {TRUE} else {FALSE}
   print(fileexists)
   ## Add 3 second Delay
   Sys.sleep(3)   
    }
   
    data  = read.csv(fileName)
    head(data)

PASS Summit–Lightning Talk on R

I’m excited to have the opportunity to speak at PASS Summit 2018. I am doing a quick 10 minute lightning talk on R integration with Power BI. Somehow I was able to sneak in with this incredible group of speakers! SHHHHH, don’t tell anyone lest they find out!

Speakers

I have loaded my slide deck here, this includes what you need to get started with R! Slide Deck

In this talk I am focusing on how R can be used to extend the capabilities and functionality of Power BI. More specifically, I will be using R to show the following four demos:

  • How to extract data from Power BI using R
  • How to unzip and process files with R
  • How to Download, unzip and process files
  • How to check if file exist before processing the file

Also, I have created blog posts with corresponding YouTube videos already on 3 of the 4 demos listed above!

YouTube

My YouTube channel is simply MitchellSQL

Blog: MitchellPearson.com

Below are the three blog posts that I have previously posted:

Export Data from Power BI using R

Unzip and Process files into Power BI with R

Download, unzip, and process with R

2 things you need to know about Power BI Security

In this blog post, I want to quickly discuss 2 things you need to know about Power BI Security as it relates to the Power BI Service. Many of the clients that I visit and perform training for are just getting into an implementation of Power BI and are not aware of these two items. I will probably come back and update this list as there are a couple more items I want to add, but those will take slightly more explanation! Smile

Publish to Web should be disabled

The publish to web feature provides an Iframe that allows a user to take a report and then embed that anywhere an Iframe can be accepted. The problem with this is that now the report is 100% security free and anyone who has access to the report has access to everything in it.

If you choose not to disable this feature it should, at the very least, be limited to specific people within the organization who have received proper training. To disable or manage this feature you must perform the following steps:

  • You must be a Power BI Administrator
  • Next, select Admin Portal from the settings wheel found in the Power BI Service.

image

  • From the admin portal, select Tenant Settings and then expand Publish to Web.
  • Click on the button to disable Publish to Web feature.

image

Direct Query to a relational database is not a RLS enabled data source

Many users, especially users on later versions of SQL Server, assume incorrectly that a direct query connection to SQL Server will use the security restraints applied at the database level. Unfortunately, this just isn’t the case.

Any user connecting to a report with Direct Query is using the stored credentials in the enterprise data gateway and therefore will have access to whatever the data gateway has access to. So if you have enabled row level security on the tables for your database, this is not going to work with direct query.

However, you can and should add row level security to your power bi data model (pbix) file. This row level security will work and limit access as it is intended!

Not sure how to set up Row Level Security in Power BI? Thanks ok! Click Here

Download and unzip files using R in Power BI

In a previous blog, I wrote about how you could use R to unzip files. Well, that raises the question, can I download a zip file and then unzip it using R?

You can view the previous blog post on how to unzip files here.

Update: YouTube video can be found at the bottom of this post!

Download files using R

In general, downloading and processing a file can be done in Power BI very easily using “web” as the data source and you never have to worry about using R. However, if you need to do something more complex then R can be very helpful. For example, you may want to download a zip file, unzip it, and then process it.

In this example, I am going to download a zip file that contains names by state, this zip file is stored in the following location:

https://www.ssa.gov/oact/babynames/state/namesbystate.zip

This zip file has data for each of the different states, for this example I only want to look at the data for Florida. So we are going to unzip the file and only extract the data from the single file that we need data from.

Let’s take a look at the code in R Studio and break it down:

image

  1. First, I want to store the destination location and filename in a variable, this is done in lines 1-2.
  2. Next, I want to download the zip file and store it locally, this is done in lines 4-6.
  3. Thirdly, now the file will be unzipped and the file FL.TXT will be stored in the variable FLnames, lines 8-9.
  4. Lastly, we will read the text file and store it in the variable babynames.

The only thing we added in this example that was not in the previous blog post was the ability of downloading the code. If you, missed the previous blog and want to know how to import the data into Power BI, you can check that our here.

 

Thanks, for checking out my blog!

Using Composite model with Dynamic Security in Power BI

Like many others, I have not been an advocate of using Direct Query, it has long had many concerns diminishing its’ overall usefulness with one of the major key issues being performance. The Microsoft Power BI team has made major strides to mitigate the concerns around performance by adding two new key features: Composite Model and Aggregations. This blog will not discuss aggregations. However, we will briefly discuss the composite model and how we can use this to make Dynamic Security better.

The composite model simply allows users to import data and use Direct Query in the same data model. This has long been a point of contention for Power BI users because combining these two features into one model adds a lot of value. In this blog, I am going to briefly explain how this model helps to improve dynamic row level security.

I was sure I had previously blogged about dynamic row level security but I don’t see a post so I am going to re-direct you elsewhere. If you are interested in learning exactly how to implement dynamic security you can check it out here: Dynamic Security implementation

I am a huge advocate for Dynamic Security. Dynamic security allows you to create a single role in Power BI and then you can manage all your user security in a single table. Take a look at the following table which I am now storing in SQL Server:

image

So why would the composite model make dynamic security better?

Great question. Historically, I would always import data into Power BI, including the dynamic security table. The problem with this method is what happens with the security of the data if the model is not being refreshed? For example, I may reassign which users have access to which sales territories in my security table but if data models in the service are not being refreshed then the security is not being refreshed and thus users will have access to data they should not have.

Now, with the composite model I can still import all my reporting data but then I can set up a direct query connection to my dynamic security table. This means that all of Power BI models using this table will always be up to date!

image

Hopefully you can find many more useful scenarios for the composite model! Thanks for reading my blog!

Power BI Resources

This blog post is a quick post to outline some of the resources around Power BI that I have found most useful, I will update this list accordingly.

Power BI Learning Tour

Brand new to Power BI? If so, this Microsoft led training is a great place to start:

https://docs.microsoft.com/en-us/power-bi/guided-learning/

Custom Visuals – Series by Devin Knight

Devin Knight has, at the time of this blog, recorded 117 videos on his blog dedicated to demonstrating different custom visuals created by the community. If you are not already aware of this blog series, take a look at the link below!:

https://devinknightsql.com/category/power-bi-custom-visuals/

PowerBI.tips – Custom Theme Generator

My personal favorite from powerbi.tips is the custom theme generator. They also have pre-built layouts, tutorials and many other valuable resources.

https://powerbi.tips/

DAX Guide

The DAX Guide is a great place to browse and explore available DAX functions.

https://dax.guide/

Have something that just has to be on this list? Let me know!

How to add Data Labels to maps in Power BI

Recently, I was asked if the values for maps could be shown on the map and of course the first thing I thought was to just turn on data labels. Well, if you’re reading this then you already know there isn’t currently a way to add data labels. Now, I say currently because the Power BI team is always making changes and it could one day be there.

image

First, this was not my idea. When I realized there was not a data label option I knew someone in the community would have figured out a work around, and I was right!

Thanks to the awesome people who contribute at community.powerbi.com the answer was only a “google kungfu” search away. If you would like to see the original forum post where Sean “Super Contributor” helped out another Power BI user then check out the following URL:

https://community.powerbi.com/t5/Desktop/Data-Labels-on-Maps/td-p/79118

This blog is going to take a look at the following  three items:

  • Why this workaround works
  • How to set this up and configure it
  • Concerns and issues with this method

Why this workaround works

The map visual in Power BI will allow you to display the value of whatever is allocated to the Location field of the map. Generally you would simply put the country, state, city,  or some other valid location in this field. The limitation is that this field can only be populated with a calculated column. Therefore, Sean suggested combining the value you wanted to display into a calculated column field. Well, that allows you to now display the results, but it doesn’t map the data. The next step is you will need to have the latitude and longitude for each geographical location that needs to be mapped. The latitude and longitude coordinates will be added to the map visual to determine location and the location field will be used to display the data label.

Setup and configuration of Data Labels on Maps in Power BI!

Now, let’s dig in and build this out.

First, make sure your geography table has the required latitude and longitude columns. My geography table goes down to the zip code level of detail but I only brought in the Latitude and Longitude at the State level since I knew this is how I wanted to view the data in my map visual.

image

Next, it’s time to build the calculated column that will help you display the data.

  • Create a new calculated column on the Geography table.
  • The following DAX expression returns Total Sales by State combined with the state name.
  • I also added the FORMAT function to add some formatting to the values.
  • The part highlighted in the red box returns the Total Sales by State.

image

Now that the you have your latitude and longitudes and the calculated column has been built, it’s time to add this to a map! In the below screenshot I highlight where each of your fields should be assigned.

image

Finally, it’s time to display our Location on the map!

  • Under formatting options turn on Category Labels
  • Feel free to change the properties for color, text size, show background and transparency.

image

Concerns and Issues

First of all, you must store your values in a calculated column in order to display them.

  • This means that your values will be static, they won’t change as slicers change.
  • You need to know exactly what and how your end users will use the data, which is nearly impossible.
  • This column will take up resources in your data model.

Secondly, you must have the latitude and longitude for every location you want to put on your map.

  • It may be difficult to obtain a quality list of latitudes and longitudes for each address.
  • These additional columns in your data model will have a high level of uniqueness and will definitely consume valuable memory resources.

Enjoy!

Quick Tips–Updating Parameters from the PBI Service

In this quick tip I want to share how you can update Parameters in Power BI from the service. Previously, this was not an option and Parameters could only be updated from Power BI Desktop.

Updating Parameters in Power BI

To update parameters, navigate to the Datasets section in the Power BI Service. Next, click on the schedule refresh icon. Updating parameters is done from the schedule refresh window.

SNAGHTML20f21a8d

Click on Parameters from the schedule refresh window. Once expanded you will see available parameters, simply update the parameters and you’re done!

image

Thanks for reading!

Quick Tips – Connecting to a SharePoint Folder in Power BI

Recently, when teaching the Advanced Power BI Course by Pragmatic Works, I had a student ask how to use the SharePoint Folder in Power BI. She had been struggling with getting it to work correctly. After trying a few different URLS I could see why she was having a problem. Since you’re reading this, you are probably having the same kind of issues. The error message is actually pretty good, but a little confusing. When the error says “the site’s root URL only.” I took this to mean SharePoint’s URL, however, it actually means the URL of the site within SharePoint.

The URL isn’t valid. Please enter the site’s root URL only.

image

Let’s take a look at how you can properly connect to a SharePoint Folder in Power BI and extract the files.

Connecting to a SharePoint Folder in Power BI

In my first attempt I tried connecting to simply the root of my SharePoint site.

https://pragmaticworksonline.sharepoint.com

image

This only allowed access to the Shared Documents folder on the root and therefore didn’t work.

image

As you can see in the above image, this only allowed access to the Shared Documents folder on the root of the SharePoint site.

Connecting to a specific SharePoint site

Next, I tried connecting directly to the root of a particular site. I connected to the training site on SharePoint using the following URL:

image

This worked, the SharePoint folder option will traverse subfolders, just like the default from folder option. Therefore, if you are looking for a particular directory then you can simply apply a filter in the Power Query Editor on the Folder Path property.

Thanks for reading this blog!

Unexpected Totals in DAX (Part 1)

DAX is an awesome language and when paired with properly created relationships, DAX can add significant analytical value to your data models with minimum effort. But, you already knew that! So why are we here? Well, sometimes, DAX can produce results that are unexpected and this is usually very noticeable when looking at the totals row.

image

So why do we get unexpected results at the total row? Why doesn’t the total row simply return the sum of all the rows in a specified column? The simple answer is your DAX calculation is also computed for the total row and operates within the contexts of the total row. Let’s take a look at the previous screenshot.

In this example, the total row is returning all homes listed for all years in the filter context, this is in fact the sum of all the rows in the column. However, you may expect to only see the homes newly listed on the market for the latest time period, in this case that would be how many homes were listed in 2016. The result of 8,691 homes listed in the total row is not wrong or incorrect, it depends on what you are specifically looking for, it could definitely be unexpected depending on your analytical needs. If you do not wish to see the total of all homes ever listed in the current filter context then you have a couple of options available to you.

There are generally 3 ways you could address incorrect / unexpected totals.

  1. Pretend the problem doesn’t exist. (ProTip: Don’t ignore problems.)
  2. Identify when the DAX calculation is being evaluated for the total row and return a BLANK value.
  3. Identify when the DAX calculation is being evaluated for the total row and perform a different calculation.

In this blog post you are going to learn how to return blank to eliminate any confusion. In a future blog post you will learn how to use DAX to change the value of the total row.

HASONEVALUE function in DAX

In this simple example, the total row is the sum of all the rows, but this is not what we want. What we would want to show at the total row is how many homes have recently been listed for sale, not the total of all homes that have ever been listed. The first thing we must do is identify if we are at a total row. The way I do this is by using the function HASONEVALUE.

MSDN Definition:

HASONEVALUE: Returns TRUE when the context for the columnName has been filtered down to one distinct value only. Otherwise FALSE.

In the screenshot below I created a new measure called “Totals” and in this measure the function HASONEVALUE is used to correctly identify which row is the total row. This works because at the total level the filter context is all years, so the function returns FALSE.

image

The final step is to now use conditional logic and replace the Total row with a blank value.

BLANK() function in DAX

New Homes on Market (2)=
IF(
    HASONEVALUE(‘Date'[Year]),
    [New Homes on Market],
   BLANK())

SNAGHTMLde25f4f

Now the total row is no longer confusing or misleading. In part two of this series I am going to take this a step further and show how to use DAX to return your expected results at the total row instead of just returning blank!

Further Reading: Rob Collie over at PowerPivotPro has a great blog post on HASONEVALUE vs. ISFILTERED vs. HASONEFILTER:
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Thanks!