Wednesday, October 01, 2014

Microsoft Power Query for Excel - Easy access to Office 365 data and other connectors

Today i tested the new version of the "Microsoft Power Query for Excel Plug-in" and have to say "Microsoft did a brilliant work on call External data from Excel".
Here where you can download the "Microsoft Power Query for Excel
http://www.microsoft.com/en-us/download/details.aspx?id=39379

This is a "must-have" in every Excel document.

I used, tested and love it, if you work heavily with excel this is a plug-in to have in Excel Document and will be very useful to call external data.

Access your SharePoint List data stored in Office 365 with Plug-in
Here a little manual on how we can access to sharePoint data using the Plug-in in Excel.

Install the plug-in to Excel 2013 "Microsoft Power Query for Excel" and open the excel.
Select the Ribbon "Power Query" and select the option "From Other Sources > From SharePoint List", this option will open a conection to your Office365.




Select the SharePoint Url where is located the List data you want to manage in the Excel file.


Select the type of authentication since this is Office 365, select tthe option "Organization Account" and "sign in" with your Office365 account. This credential will be stored and used to access Office365 data.


After the authentication is made with success a new option call "Navigator" appear where is listed all Lists from the SharePoint Site.



In this option you are able to select your List datasource, for this example i used a List call "PLXRecyclingBin" where i store my UserCustomActions for maintenance and select the option "Edit" to manage the datasource.


Query Editor

When the Query Edit is selected a new dialog form is open where we have multiple options "select Columns, Grouping, Split, Counts, Merge Query, etc etc" that help the Management of the data, super powerful


Choose Columns

One of the option we can use in the Query editor is the column selector where columns of the List are displayed and we can choose what should be shown. 


Group By 

Another great option is the "Group By", this option provide View where we can group by field and define operations asscoiated for this case was select "Count Rows".



Workbook Query 

After the query editor is finish need to cose and the new view appears in excel with te defined content, easy easy.... even with preview in the Datasource.



Select Multiple Lists

The add-in is so powerfull that is possible call mutliple List and make possible connections between them, but that is another article. 



Power Query Connector Views

Here a view of the existing connectors "SharePoint, Office 365, Active Directory, Odata, Azure, Exchange, Facebook also?!?!...."




Hope you like this article,
Kind regards,
André Lage

Post a Comment