Web and Cloud Services

Web Pages

As a first and extremely simple example, let’s grab some data from a web page. This is made extremely easy by Power Query, as it can read the tables of data present in any web page. Since I want to concentrate on the method rather than the data, I will use a web page that has nothing to do with the sample data used elsewhere in this book. I will not be using this other than as a simple introduction to the process of loading data from web pages using Power Query.

Assuming that you have launched Excel

1. 1.

   In the Data ribbon, click From Web.

    
2. 2.

   Enter the following URL (it is a Microsoft help page for Power Query that contains a few tables of data): http://office.microsoft.com/en-gb/excel-help/guide-to-the-power-query-ribbon-HA103993930.aspx. I am, of course, hoping that it is still available when you read this book. Of course, if you have a URL that you want to try out, then feel free! The dialog will look something like Figure 4-1.

    

1. 3.

   Click OK. The Navigator dialog will appear. After a few seconds, during which Power Query is connecting to the web page, the list of available tables of data in the web page will be displayed.

    
2. 4.

   Click one of the table names on the left of the Navigator dialog. The contents of the table will appear on the right of the Navigator dialog to show you what the data in the chosen table looks like, as shown in Figure 4-2.

    

This simple example showed how you can select tables of data from a web page and load them into Excel.

Advanced Web Options

In step 3 of the previous example, you could have selected the Advanced button. Had you done this, the From Web dialog would have expanded to allow you to build complex URLs by adding URL parts. You can see an example of this in Figure 4-3.

Clicking the Add part button allows you to define multiple URL parts.

If necessary, you can also specify HTTP request header parameters that will be used when submitting the URL. These could be required by certain web pages. A discussion of these is outside the scope of this book.

Viewing the Source Web Page

It can be more than a little disconcerting to see only the tables from a web page—and not the actual page itself—in the Navigator. After all, the Web is a quintessentially visual medium.

So Power Query has a useful addition to the Navigator that applies only to web pages. This is the possibility to see the actual page itself in the Navigator—just as it appears in a web browser. To try this out:

1. 1.

   Follow steps 1 through 3 as described earlier.

    
2. 2.

   Click Web View on the right of the Navigator. You should see something like the page shown in Figure 4-4.

    

You can now scroll down the page—and flip back to viewing only the data in the tables in the page by clicking Table View on the right-hand side of the dialog.

Salesforce

One of the pioneers in the software as a service (SaaS) space—and now, indisputably, one of the leaders—is Salesforce. So it is perhaps inevitable that Power Query will allow you to connect to Salesforce and load into Excel any data that you have permission to view using your Salesforce account.

Briefly, Salesforce objects are the underlying data structures (that you can consider as kinds of tables) that contain the information that you want to access. Salesforce reports are the data that has been collated from the data tables into a more accessible form of output.

Loading Data from Salesforce Objects

Assuming, then, that you have a valid Salesforce account, here is how you can load data from Salesforce objects into Excel:

1. 1.

   Open a new Excel application.

    
2. 2.

   In the Excel Data ribbon, click the Get Data button.

    
3. 3.

   Click From Online Services ➤ From Salesforce Objects. The Salesforce Objects dialog will appear. It should look like the one shown in Figure 4-5.

    

1. 4.

   Select the Production button and click OK. The Access Salesforce login dialog will appear. It should look like the one shown in Figure 4-6.

    

1. 5.

   Unless you are already signed in, click Sign in. The Salesforce sign-in dialog will appear.

    
2. 6.

   Enter your Salesforce user name and password. The dialog should look something like the one shown in Figure 4-7.

    

1. 7.

   If this is the first time that you are connecting to Salesforce from Power Query (or if you have requested that Salesforce request confirmation each time that you log in), you will be asked to verify your identity. The Salesforce Verify Your Identity dialog will appear, as shown in Figure 4-8.

    

1. 8.

   Click Verify. Salesforce will send a verification code to the email account that you are using to log in to Salesforce.

    
2. 9.

   Enter the code in the Verification Code field and click OK. You will see the Allow Access dialog, as in Figure 4-9.

    

1. 10.

   Click Allow. You will return to the Access Salesforce dialog, only now you are logged in.

    
2. 11.

   Click Connect. The Navigator will appear, showing the Salesforce objects that you have permissions to access. You can see an example of this in Figure 4-10.

    

1. 12.

   Select the objects whose data you wish to load into Excel and click Load. The data will be loaded into Excel ready for you to create reports based on your Salesforce data.

    

Tip

To avoid having to confirm your identity to Salesforce every time that you create a new suite of Excel reports using Salesforce data, you can check “Remember me” in the Salesforce sign-in dialog and “Don’t ask again” in the Salesforce Verify Your Identity dialog.

Salesforce objects contain a vast amount of data. However, from the point of view of Power Query, this is similar to accessing a database structure. This means that you have to have some understanding of how the underlying data is stored. Should you wish to learn about the way that Salesforce data is structured, then I suggest that you start with the Salesforce documentation currently available at https://trailhead.salesforce.com/en/modules/data_modeling/units/objects_intro.

Salesforce Reports

If you find that you are simply submerged by the amount of data that is available in Salesforce, you can, instead, go directly to the data that underlies standard Salesforce reports. This will avoid your having to learn about the underlying data structures. The downside is that you cannot easily extend these datasets.

To access Salesforce report data, simply follow the steps outlined in the previous section. However, instead of choosing Salesforce Objects in step 3, select Salesforce Reports instead. The Navigator dialog will, in this case, look something like the one shown in Figure 4-11.

From here you can select and load the reports data from Salesforce that you want to use for further analysis.

Microsoft Dynamics 365

Another online service that contains much valuable enterprise data is Microsoft Dynamics 365 . As you would probably expect, Power Query in Excel can connect easily to Microsoft online sources such as Dynamics. Here is how to do this if your organization uses this specific platform:

1. 1.

   Open a new Excel application.

    
2. 2.

   In the Excel Data ribbon, click the Get Data button.

    
3. 3.

   Click From Online Services ➤ From Dynamics 365 (online)

    
4. 4.

   Click Connect. The Dynamics 365 (online) dialog will appear.

    
5. 5.

   Enter the URL that you use to connect to Dynamics 365 and add /api/data/v8.1 (at least, this was the case as this book went to press). It could look like the one shown in Figure 4-12. Note, however, that this URL will vary depending on where you are in the world.

    

There are a huge number of Dynamics 365 tables—and this number will vary depending on the subscription that your organization has taken out. However, you are, in reality, accessing a database structure. This means that you have to have some understanding of how the underlying data is stored. Should you wish to learn about Dynamics 365 tables, then I suggest that you start with the Microsoft online help at https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/data-entities.

Azure SQL Database

SQL Server does not only exist as an on-premises database. It is also available as a “platform as a service ” (also known as PaaS). Simply put, this lets you apply a pay-as-you-go model to your database requirements where you can fire up a database server in the cloud in a few minutes and then scale it to suit your requirements, rather than buying hardware and software and having to maintain them.

Connecting to Microsoft’s PaaS offering, called Azure SQL Database, is truly simple. If you have the details of a corporate Azure SQL Database, you can use this to connect to. If you do not, and nonetheless want to experiment with connecting Power Query to Azure SQL Database, you can always request a free trial account from Microsoft and set up an Azure SQL Database database in a few minutes. If this is the path that you are taking, then you can find instructions on how to do this (including loading the sample data that you will connect to later in this section) at the following URL: https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-get-started-portal.

To connect from Excel to an Azure SQL Database using Power Query:

1. 1.

   Open a new Excel application.

    
2. 2.

   In the Excel Data ribbon, click Get Data ➤ From Azure ➤ From Azure SQL Database. The SQL Server database dialog will appear (after all, an Azure SQL Database is a SQL Server database—but in the cloud).

    
3. 3.

   Enter the Azure SQL Database server name that you obtained from the Microsoft Azure Management Portal (or that was given to you by a corporate DBA). The SQL Server database dialog will look like the one shown in Figure 4-13.

    

1. 4.

   Click OK. The credentials dialog will appear.

    
2. 5.

   Click Database on the left and enter a valid user name and password. The credentials dialog will look like the one shown in Figure 4-14.

    

Azure SQL Data Warehouse (Azure Synapse Analytics)

Azure has many available platforms to store data. One that is particularly well adapted to Power Query is the Azure SQL Data Warehouse—now known as Azure Synapse Analytics . This is an MPP (massively parallel processing) data warehouse that is hosted in Azure.

Once again I will presume that you have a corporate Azure Synapse Analytics instance at hand and that you have the relevant access rights to Azure Synapse Analytics. Here, too, you need firewall rules to be set up correctly (although this may not be strictly necessary if you have previously set up firewall rules for, say, Azure SQL Database). I imagine that you could need the assistance of corporate IT support to ensure that the connection can be made successfully.

Assuming that you have access to an Azure Synapse Analytics instance:

1. 1.

   Open a new Excel application.

    
2. 2.

   In the Data ribbon, click Get Data ➤ From Azure ➤ From Azure SQL Data Warehouse.

    
3. 3.

   Enter the Azure SQL Data Warehouse server name that you obtained from the Microsoft Azure Management Portal (or that was given to you by a corporate IT). The SQL Server database dialog will look like the one shown in Figure 4-15. This is because Azure Synapse Analytics is, essentially, a SQL Server database.

    

Connecting to SQL Server on an Azure Virtual Machine

More and more databases are now hosted outside a corporate environment by cloud service providers. With a provider such as Amazon (with RDS for SQL Server) or Microsoft (that offers virtual machines —or VMs—for SQL Server in Azure), you can now site your databases outside the enterprise and access them from virtually anywhere in the world.

So, to extend the panoply of data sources available to Power Query, we will now see, briefly, how to connect to SQL Server on an Azure Virtual Machine. Admittedly, connecting to SQL Server on an Azure Virtual Machine is nearly the same as connecting to SQL Server in a corporate environment—as you saw in Chapter 3. However, it is worth a short detour to explain, briefly, how to return data to Excel from a SQL Server instance in the cloud using Power Query.

Once again, if you do not have a SQL Server instance that is hosted on an Azure Virtual Machine in your corporate environment, then you can always test this process using an Azure trial account. I cannot, however, explain here how to set up a SQL Server instance on a VM, as this is a large and separate subject that is outside the scope of this book. There are, however, many resources available that can explain how to do this should you need them.

To connect to SQL Server on a Virtual Machine in Azure:

1. 1.

   Open a new Excel application.

    
2. 2.

   In the Excel ribbon, click the small triangle at the bottom of the Get Data button and then click SQL Server. The SQL Server database dialog will appear.

    
3. 3.

   Enter the full string that describes the server in the Server text box. Either this will be given to you by a corporate DBA or, if you are using your own Azure account, you can find it in the Azure Management Portal.

    
4. 4.

   Enter the database name corresponding to the database that you have the right to access. The dialog will look like Figure 4-16.

    

1. 5.

   Click OK. The Access a SQL Server Database dialog will appear. Select Database as the security mode and enter the user name and password, as shown in Figure 4-17. If you are using your own Azure account, these can be the user name and password that you specified when setting up the virtual machine.

    

Azure Blob Storage

The final Azure data source that I want to introduce you to in this chapter is Azure Blob Storage . To all intents and purposes, you can consider this, as far as Excel is concerned, as a file share in the cloud. So if you need to access data that is stored as files, you can connect to them via Azure Blob Storage.

Once again, you will need either corporate access to Azure Blob Storage or an Azure trial account. In either case, you need to copy the two sample files that are in the folder C:PowerBiDesktopSamplesMultipleIdenticalFiles into a container in your Azure Blob Storage. Downloading the sample files is explained in Appendix A.

Once the source data is available in Azure Blob Storage, you can carry out the following steps:

1. 1.

   Open a new Excel application.

    
2. 2.

   In the Data ribbon, click Get Data ➤ From Azure ➤ From Azure Blob Storage.

    
3. 3.

   Enter the account name that you are using to connect to Azure Blob Storage. The Azure Blob Storage dialog will look like the one shown in Figure 4-18. If you are using a corporate Azure Blob Storage account, then your system administrator will provide this. In a test scenario, you can find this in the Azure Management Portal by opening the Storage Account blade and copying the Blob Service Endpoint.

    

1. 4.

   Click OK. The Azure Blob Storage Account Key dialog will appear.

    
2. 5.

   In the Azure Management Portal, copy an account key. These can be found in the Azure Management Portal by clicking the Storage Account blade and then clicking Access Keys. If you have been sent an account key by a system administrator, then use that instead. You can see this dialog in Figure 4-19.

    

Azure Security

All cloud service providers take security extremely seriously. As you have seen in this chapter, you will always be obliged to enter some form of security token and/or specify a valid user name and password to connect to cloud-based data.

Other Source Types

Clearly it would be impossible to explain how to use all of the potential data sources without devoting a considerable number of pages to the subject. Moreover, not everybody will need to access every place where external data is stored. Indeed, most users only ever need to ingest data from two or three external data sources at most.

Inevitably, this means that there are several data sources available in Power Query in Excel that you will not be seeing in this book. However, as I have mentioned, one of the fundamental advantages of Power Query is that once you have learned how to connect to a couple of data sources, you have learned how to use pretty nearly all of them.

Connecting to SharePoint, Exchange, Microsoft Dynamics, Azure HDInsight, or Hadoop will mean, inevitably, that these are used by your organization—and that you have the necessary access rights.

I prefer to add a word of warning if you are using Power Query in Excel to connect to Azure HDInsight or Hadoop. These are “big data” sources and, as the moniker implies, they can contain vast amounts of data. The sheer size of the data that they contain can easily swamp Excel. Consequently, I advise you to read the next few chapters that explain how to select and filter data before loading it before you attempt to connect to big data sources.

Conclusion

In this chapter, you saw, briefly, how to retrieve data that you access using the Internet. This can range from a table of data on a web page to a massive Azure Synapse Analytics Data Warehouse. You may even need to access data held in Azure Blob Storage or in an Azure Data Lake. Alternatively, perhaps you need to create reports based on your Salesforce or MS Dynamics 365 data. In any case, Power Query can connect and access the data available in these services and repositories.

Given the number of online sources, this chapter could only scratch the surface of this range of potential data repositories. However, as Power Query is rigorous about standardizing access to data, you should be able to apply the approaches you have learned in this chapter to many other data services, both current and future.