Using Power Query to Get Data From an Excel File in OneDrive Via URL

In this blog entry I'm going to discuss accessing an Excel file that's stored in OneDrive. What I do *not* want to do is associate the files to my local file path such as c:\users\melissa\onedrive... because another person who downloads the workbook and wants to edit or look at the Power Query settings will get an error. So I want to use the URL for OneDrive to make it easier to pass this workbook among other people. This took a bit of trial and error to find the right URL that would work with Power Query.

Storing the Source File in OneDrive

The first thing we want to do is get a file in OneDrive and make sure it's shared. For this purpose I'm just using a little simple file called PowerQueryTest, directly in my Public OneDrive folder. However, this same logic can be applied to a file shared with a set of coworkers.

Getting a Link for the Source File

If you click the Excel file to view or edit it in the browser, the URL looks like this:

Power Query doesn't like that URL. So, the next thing you might think to try is to generating a link with the Share feature. For my file, that URL looks like:

The 2nd URL is a little different from the first one, but the resid that identifies the report is the same so that's good. But, the above format still won't work in Power Query.

What I've found that will work with Power Query is this kind of URL structure:  

 

Basically, it's https://onedrive.live.com/download?&resid= followed by the unique report # which you can obtain from the URL as you view or edit a report in the browser.

Using the URL in Power Query

Even though we're going after contents of an Excel File, we want to use the From Web  menu item on the Power Query ribbon (instead of From File). Paste in the URL in the correct format mentioned just above.

In the Navigator menu on the right pane, select the sheet and then Edit.

 

Now you can carry on with whatever steps you need for your file. For my teensy little set of sample data here, I told it to "Use First Row as Headers" and then gave it a name and description in the query properties.

Credentials to Access to One Drive from Power Query

Now, the next thing you need to know is what will happen when the next person opens this workbook and wants to edit or even just look at how the Power Query steps. So, for this last series of screen shots, we are seeing it from the perspective of person #2 who just opened the file.

The first thing person #2 sees is the normal security warning. It's ok to click Enable Content since it's been shared by our trusty coworker. 

When we hover over the query name in the Workbook Queries pane, we see a warning in the popup box.  Permission Error: Credentials are required to connect to the Web source.  That makes sense, even though this was a file I had in the public share - Power Query just doesn't know it yet.  

Go ahead and click Edit.

Then choose Edit Credentials.

Lastly, select Anonymous access.  For me, the first radio button worked perfectly.

You might want to let your coworkers know to go ahead and choose anonymous credentials if they see the warning and want to edit, view, or refresh Power Query. (Of course, if it's not anonymous then let them know which organizational account to sign in with instead.)

Power Query Saved Data Source Settings

On the Power Query ribbon, there's a Data Source Settings item in the Machine Settings section. After person #2 set their credentials in the previous step, their machine now stores this setting for OneDrive.

That's it! Found a different way? Leave a note in the comments.

As always, Power BI is constantly evolving and changing. This information is correct as of Aug. 2014.

You Might Also Like...

Power Query Data - Should It Be Loaded to the Worksheet?

Where to Find Q&A Optimizations in Power BI

Just a quick tip about where to locate the options in Power BI for doing Q&A optimizations. In the Power BI site, these are not where you might first think to look.

What is Q&A?

First, just a quick bit on what Q&A is and why you should care.  Q&A is also referred to as natural language querying, and essentially amounts to using a Bing-like search box in order to return a report.  This is interesting and important for your report consumers because it means the report author doesn't have to publish every single permeation of a report. 

This behavior of constructing reports on the fly relies upon certain things in the underlying data model (Power Pivot) for it to return results properly.  So the first thing you can do for Q&A is to have a proper data model and use Synonyms in the Power Pivot model. (See the link below for Part 2 for more info.)

In addition to proper modeling techniques, there are some optimizations that are now available in Power BI - i.e., available in the browser in the Power BI site. When done in the browser, these optimizations are written back to the original workbook. However, where to locate Q&A optimizations isn't quite as obvious as you might think.

Finding Q&A Optimizations in the Power BI Site

1. Go to the main page of Power BI.

2. Select the Q&A page within Power BI Site Settings.

Q&A_SiteSettings2.png

3. Locate the workbook you wish to optimize, click the ellipses, and choose "Optimize for Q&A."

Q&A_SiteSettings3.png

At this point, you have 4 options: the Overview, Synonyms, Phrasings, and Usage.

 

Synonyms can be done in either the Power Pivot workbook (assuming you have the correct version of Excel, that is...I discuss that more in this blog entry:  Getting New Power Pivot Features), or in the Power BI site. If you start doing Q&A optimizations in the Power BI site, make sure you re-download a new copy of the workbook before making edits and re-uploading it! (If this becomes an issue, you might think about turning on versioning in the document library to keep 1, or maybe 2, versions in change history.)

Phrasings, however, can only be done currently in the Power BI site. I would say that the Q&A functionality available in the cloud only, along with the new licensing model for Power Map, is one of Microsoft's ways of encouraging customers to purchase Office 365 and Power BI licenses.

Who Has Access to Q&A Optimization?

From what I understand, you will see the Q&A optimization page if (a) you are a Power BI Administrator, or (b) you are the one who uploaded the file. Now that doesn't cover a situation where Suzie uploads the file but John is taking over management of it. I figure in that case John can download it, delete it, and re-upload it. However, I'm hoping the permissions for allowing optimization become a bit more granular.  In my mind, the person who developed the model is the best person for optimizing it, but that of course depends on the optimizations.

Cloud Modeling?

In the documentation for Q&A from Office.com, they refer to Q&A optimizations also as cloud modeling. I personally have a very different definition of modeling. However, it's helpful to know what the lingo is if you are searching for help. Usage of the term cloud modeling makes me wonder if that means something more along those lines is coming at some point?  Hmmm.

Finding More Information

Introduction to Power BI Q&A (and Cloud Modeling)

Demystifying Power BI Q&A - Part 1  < An introduction

Demystifying Power BI Q&A - Part 2  < Discusses good modeling techniques & synonyms

Demystifying Power BI Q&A - Part 3   < Discusses phrasings

 

Creating a SharePoint Server Farm in Azure - Part 2

In Part 1 of Creating a SharePoint Server Farm in Azure, we went through the screen shots and steps available for using the gallery resources available in Azure for setting up a SharePoint Farm. In Part 2, we're going to look at the next steps to determine the state of this new environment.

When we created the farm in Part 1, there were not any options for what services, apps, or functionality to be pre-configured. That makes sense since this is an IaaS environment, not PaaS. So what we have at this point is SQL Server 2014 installed on the SQL VM which is running Windows Server 2012 R2.  A SharePoint site ready to be configured on the SharePoint VM which is running Windows Server 2012. 

As a reminder, this is a demo environment for me so some of the settings are reflective of that and may not be consistent with what you would want to do in a production environment.

Pin the Resource Group to the Startboard

The first thing you'll want to do is pin your new resource group to the Startboard.  When it's done being configured, you should see a Notification.  Selecting the notification will open the resource group. Right-click the tile on the Startboard and pin it for easy access later.

Reviewing Details of the Resource Group

On the blade for the Resource Group, select the 2 more ... ellipses. That'll expand out to another blade which lists each individual resource.

Let's take a look further at the SharePoint VM.  Selecting that resource displays another blade out with more details about it + links to take further actions.

Connecting to the Virtual Machine

Now I'm ready to connect to one of the virtual machines. To connect, there's a button at the top of the blade for the virtual machine. (Note this is also where you can shut down, start, & restart the services. Since this is a demo environment for me, I'll be shutting down the VMs when they're not in use.)

 

After clicking the Connect button you'll be presented with a Open/Save/Cancel dialog from the browser.

What I usually do, because I'm far too lazy to come into the Azure portal and connect every time, is do a Save.  Where I choose to save it is a folder on my desktop called RDP which has various connections with their preferences saved.

In Windows Explorer, right-click the RDP file you just saved and choose Edit. There's 2 main things I'm interested in changing.  One is the pixel size for the monitor (on the Display tab). The other is under Local Resources; click the More button and choose all the checkboxes - especially drives. It's really helpful to get to your local drive when in a VM.  (Sidenote: I often need to edit the file with a text editor instead to get the pixel size exactly right. I talked about that a bit more here:  How to Perfect the Resolution of a Remote Desktop Session.)

When you're done, before you hit Connect, don't forget to click the Save button on the General tab. That'll save these preferences for the next time you launch the RDP session from your saved file.

 

Now let's connect to the virtual machine. When prompted, use the administrator ID and PW (i.e., the you provided on the "Create a SharePoint Farm" blade during setup).

Add Central Admin Shortcut to Desktop

Call me finicky, but the first thing I really want to do is get the shortcut to Central Admin placed on the desktop of my SharePoint VM. So let's bring up the Start menu from the lower left hand corner.

On the Start menu, Central Admin isn't set to appear there yet. So let's right-click and go to All Apps.

Locate the Central Administration tile.  Right-click once and choose Pin to Start.  Then right-click again and choose Open File Location.

Now we have a window where the shortcut is located. Let's right-click to copy it, and then paste it on the Desktop so it's handy for future use.

WindowsServer2012CentralAdminShortcut.jpg

Now after all that we are ready to launch Central Admin and see what we've got!  It'll take it a bit of time to fire up because it's doing its just-in-time compiling thing.

In Central Admin, let's take a look at the web applications.  Click the "Manage web applications" hyperlink under Application Management.

On the web applications page, we'll see two addresses. One is our default URL, and the other is for Central Admin.

CentralAdmin_WebApps.jpg

When we launch the default address, we are presented with a template selector. Your choice will depend, of course, on what you want to do with the site.  I'm going to choose the BI Center template.

AzureTemplateSelector.jpg

The other thing you can do to check out the SharePoint environment initially is to go to Site Contents. There's a link for it on the left-side menu.

At this point you are ready to install and configure the SharePoint services and database instances you need for this environment. If you're interested in a BI environment, check out some of the suggestions here:  Resources for Installing and Configuring BI with SharePoint 2013 and SQL Server 2012.  (Even though the links in that blog entry all relate to on-premises installations, the majority of the information will still be relevant in Azure since this is an IaaS type of situation.) I also highly recommend Rob Kerr's SharePoint 2013 series of videos on MSBIAcademy.

Creating a SharePoint Server Farm in Azure

This morning at the Worldwide Partner Conference, the new SharePoint Server Farm in Azure was announced. As a BI developer with limited experience with administration and configuration, this option to fire up a multi-server SharePoint Farm in Azure piqued my interest.

Below is Part 1 my experience setting up the farm in Azure. (Perhaps this will prove to my husband that I'm not really playing Farmville as he seems to suspect!)  These steps assume you already have an Azure account created - if you don't, start here https://account.windowsazure.com/Home/Index first.

Part 2 is continued here:  Creating a SharePoint Server Farm in Azure - Part 2.

Finding the Link to Create a SharePoint Farm in Azure

Initially I thought maybe it wasn't available publicly yet because I could not find the correct choice when choosing New > Virtual Machines in Azure. That's because it's available in the new Azure Portal that's in preview mode currently.  Turns out that's because, among other things, the new Azure Portal has new capabilities to manage multi-tier applications using a Resource Group

First, click your ID at the top right of the browser window and choose "Switch to new portal." 

This launches a window for the new Azure Portal which is addressed like this: https://portal.azure.com/<YourAccount> and you will be logged out of the old portal.

Now we want to click the New button at the bottom left of the main page.

At this point since it's new, the SharePoint Server Farm is listed right away. However, let's go "the long way" just for fun.  Next click the Everything arrow.

Now we're looking at the gallery of resources that can be created in Azure. (Note that if you have the Gallery pinned to your Startboard, i.e., the home page in the new Azure Portal, you could have gotten to this point that way too.)

Select Virtual machines from the Gallery menu and locate SharePoint Server Farm in the list.

Creating a New SharePoint Farm in Azure

After you've clicked on the link for SharePoint Server Farm, its "blade" slides out to the right. From here you can take a look at the useful links they've posted and when you're ready hit Create.

At this point we get one more blade to slide out to the right which is where all the options are.

Options in Azure When Creating a SharePoint Farm

Resource Group

This creates a new Resource Group which will contain all related resources so they can be managed together. I'll name mine SharePointResourceGroup.

Choose this name carefully especially if you're not going to be using a custom domain name. Because of this choice, the URL address for my default web application is SharePointResourceGroup.cloudapp.net/. There are, however, options to manage the domain name in Azure.

User Name and Password

This will be your initial domain & local administrator. This is not your Microsoft account or Organizational account; it's just a user ID without the @company.com.

Domain Controllers

Here you set a Host Name Prefix (default = SharePoint), your Forest Root Domain Name (default = contoso.com), and choose a Pricing Tier (default = Standard A1) for the domain controllers.

I have left the Host Name Prefix at its default of SharePoint. I've also set a Domain Name and adjusted the pricing tier for the DC down to Basic A1 because this is just a demo for my personal use.  My demo doesn't need redundancy so I left the High Availability checkbox unchecked - normally you want to check this as that's a great built-in feature of Azure. 

SQL Servers

Here you have options for selecting a Host Name Prefix (default = SharePoint), a Pricing Tier for the SQL Servers (default = Standard A5), and the password for the service account.

This Host Name Prefix here defaults to SharePoint, even if you selected a different prefix for the domain controllers in the earlier configuration step. I've kept mine using SharePoint for consistency.

The default pricing tier of A5 includes 4 data disks, 2 cores, 14 GB of memory, and currently costs about $218.74 a month.  (Check here for Azure pricing.) Since mine is a demo, I'll scale it back to a basic plan since I don't need load balancing and auto-scaling. Most companies setting this up for anything other than trivial use like my demo will need beefier SQL Server specs.

The "Use the Administrator password" is asking if you want the service account to use the same password as the User Password that was specified on the first blade "Create a SharePoint Farm." For normal business operations, this would be no but for my demo purposes I've left this option checked.

Lastly, you specify what the name for the SQL Server Service Account will be for running the MSSQLSERVER and SQLSERVERAGENT services. This is in the format of <ServiceAccountName>@<DomainName>.com. The @<DomainName> uses the Forest Root Domain Name that was specified in the Domain Controllers section.

SharePoint Servers

In this blade you set another Host Name Prefix (default = SharePoint), Pricing Tier for the SharePoint Servers (default = A2), and two domain accounts.

As with the previous two sections, I kept the Host Name Prefix as SharePoint for consistency.

The "Use the Administrator password" is asking if you want the two new service account to use the same password as the User Password that was specified on the first blade "Create a SharePoint Farm." For normal business operations, this would be no but for my demo purposes I've left this option checked. If you choose to set individual passwords, you'll also need to set a pass phrase which will be used to join other machines to the farm.

The Setup User Account is the domain account that will be used to execute the SharePoint setup operations. The Server Farm Account is a domain account which will be used to configure and manage the SharePoint server farm, act as the application pool identity for Central Administration, and run the SharePoint timer service.

Optional Configuration:  Virtual Network

Under Virtual Network, the options are a Name (default = the Resource Group name) and Address Space CIDR Block (default = 10.0.0.0/26).

Optional Configuration:  Storage Account

For the Storage Account, the default is to create a new storage account. Or, you can connect to an existing storage account. The default name for a new storage account is <ResourceGroupName>.core.windows.net.

You can also set a Pricing Tier for the storage account. Default = G1 which is Geo-Redundant with 3 local replicas and 3 geo-distributed replicas.

Optional Configuration:  Diagnostics

Here is where you set if diagnostics will be sent to the storage account.

Subscription

The Subscription blade is where to specify which Azure subscription to use for these. I only have one to choose from.

 

Location

This last option relates to where the farm will be created.

 

The last option near the bottom is to Add to Startboard. Keeping this box checked (which is the default) will create a live tile on the main dashboard when you log in to the new Azure portal.

And with that, all the options should be configured. You might want verify each is correct, then click the Create button. Now the Startboard will display "Creating SharePoint Farm" until setup is complete.

After about 10 minutes, the deployment is complete. Where to go from this point will be discussed in Part 2.

Update 7/17:  The Azure team looked at the correlation ID for my deployment and determined why the error was being shown on the Startboard (in the screen shot just above). They'll be getting it fixed in their next release. From my end, I can connect to the servers and connect to Central Administration so it looks like I can safely proceed with the next steps to get things set up.

Part 2 is continued here:  Creating a SharePoint Server Farm in Azure - Part 2.

Finding More Information

TechNet Blog:  Step-by-Step: Deploy a Highly Available SharePoint Server Farm in the Cloud in Only 8 Clicks

Azure Documentation:  SharePoint Server Farm