Category Archives: Alexandru Dionisie

How to Use Power Query for Excel to Query a SharePoint 2013 List


You may also be interested in: Documentation Toolkit for SharePoint


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

If you want to extract all the data from a SharePoint List to an Excel workbook, an easy solution is to use Export to Excel. SharePoint allows you to export a Web Query which downloads that lists data into a spreadsheet.

Besides that, there is another way to extract data from SharePoint without the need to open a web browser and access the SharePoint site. It’s called Power Query for Excel and it’s designed to help by easing data import from a variety of sources. It allows us to model the data in different ways, like filtering and grouping before we import it into Excel. The Export to Excel feature allows us to do that, after we insert the data into the spreadsheet.

In this article I will use both methods to extract data from a SharePoint List and I will let you see the differences (some of them) between Export to Excel and Power Query.

Export to Excel

From the LIST tab click on the Export to Excel button.

2013-08-11-SharePointPowerQueryExcel-01.png

When we run the Web Query, Excel asks us for permissions, in order to connect to an external data source.

Here we must click on Enable.

2013-08-11-SharePointPowerQueryExcel-02.png

After allowing Excel to connect to SharePoint, all the data from the list is downloaded into a spreadsheet.

2013-08-11-SharePointPowerQueryExcel-03.png

Microsoft Power Query for Excel

According to Microsoft:

 - Power Query provides an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia. -

Download Link | Power Query

From the POWER QUERY tab, click on From Other Sources and then choose From SharePoint List.

2013-08-11-SharePointPowerQueryExcel-04.png

Next, we must specify the URL to our SharePoint site.

2013-08-11-SharePointPowerQueryExcel-05.png

One important step is about the authentication to the SharePoint site.

Here we can connect:

  • Anonymous;
  • Using Windows credentials;
  • Using MOS ID.

2013-08-11-SharePointPowerQueryExcel-06.png

Because I have my Office 365 account connected to Office 2013, in the MOS ID section I just click on Sign In and I am automatically signed in.

2013-08-11-SharePointPowerQueryExcel-07.png

In the below image we can see that the data is downloaded (101 KB when I took the screenshot).

2013-08-11-SharePointPowerQueryExcel-08.png

In the Query Editor we can see the data from our SharePoint List.

Here we can:

  • apply a filter;
  • sort the data;
  • hide the formula bar;
  • etc.

2013-08-11-SharePointPowerQueryExcel-09.png

2013-08-11-SharePointPowerQueryExcel-10.png

2013-08-11-SharePointPowerQueryExcel-11.png

To download the data from the SharePoint List, click on the Done button.

Now, in Excel we have the SharePoint data from the list.

On the right side we can see a settings pane.

In this pane we can:

  1. see the last update time and we can force a new data refresh;
  2. enable or disable the data downloading into the spreadsheet;
  3. load the data into PowerPivot.

2013-08-11-SharePointPowerQueryExcel-12.png

Using the Load to Data Model option, the downloaded data is sent to PowerPivot and from there we can do some more advanced “stuff”, like:

  • create relations between this table and another one in order to create a PivotTable from multiple sources;
  • output a variety of visual data to your Excel worksheet.
    • PivotTable;
    • PivotChart;
    • Chart and Table;
    • Four Charts;
    • Flattened PivotTable;
    • etc.
  • use DAX (Data Analysis Expressions) to to create measures (an upgraded version of Excel formulas);
  • publish dashboards to SharePoint;
  • etc.

2013-08-11-SharePointPowerQueryExcel-13.png

Account Information

At one of the above steps, Excel asked us to connect to the SharePoint site.

The POWER QUERY offers us the option that allows us to change the account or to delete the stored connection data.

From the POWER QUERY tab click on Data Source Settings.

2013-08-11-SharePointPowerQueryExcel-14.png

If we click on Edit Credential, for the selected site, all we can do is sign out and sign in with another account.

2013-08-11-SharePointPowerQueryExcel-15.png

In order to delete a site and its stored credentials, click on the desired site and then click on the Delete option.

2013-08-11-SharePointPowerQueryExcel-16.png

We have to confirm the deletion, by clicking on the Delete option.

2013-08-11-SharePointPowerQueryExcel-17.png

Now, the selected site was deleted along with the credentials.

2013-08-11-SharePointPowerQueryExcel-18.png

SharePoint: How to Restore the Promoted Sites Web Part


You may also be interested in: fpweb.net


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

Sometime ago I wrote about adding a promoted site in the new office 365. One of my blog visitors asked how he can restore the Manage Promoted Sites, because he deleted all the sites and that option was gone. I thought that he deleted the web part or had done something else.

Because it is a challenge for me to fix something that is broken, I did the same thing and noticed that the Promoted Sites section was gone.

2013-08-03-SharePointPromotedSites-01.png

After some testing I discovered that the Web Part (because the Promoted Sites is a Web Part) was actually hidden automatically, not deleted.

How to unhide the Promoted Sites Web Part?

On the right side of the page, where the username is shown, click on the username and from the drop-down menu click on Personalize this Page.

2013-08-03-SharePointPromotedSites-02.png

Now, the page is in Edit Mode.

As we can see below, the web part name has the hidden text in round brackets (a).

2013-08-03-SharePointPromotedSites-03.png

To unhide it, we must activate the Properties pane.

  • Click on the button (b) that is next to the check box and from the menu, click on the Edit My Web Part option (c).

After we activate the Properties panel, we must click on the Layout property.

In the Layout section, just uncheck the Hidden property and then click OK.

2013-08-03-SharePointPromotedSites-04.png

Before we exit the Editing Mode we must add a website, in order to be able to see the Web Part after we finish the operation:

  • Click on the Add a promoted site link;

2013-08-03-SharePointPromotedSites-05.png

Now, just fill in the details about the site and click on Save changes.

2013-08-03-SharePointPromotedSites-06.png

After we add the site, we must stop editing the web part:

  • Click on the click here link.

2013-08-03-SharePointPromotedSites-07.png

For the final step, just click on the Stop Editing button from the ribbon and that is all.

Now the Promoted Sites are back.

2013-08-03-SharePointPromotedSites-08.png

SharePoint: How to Use SkyDrive PRO to Sync Document Libraries


You may also be interested in: Creating insightful dashboards in SharePoint - Collabion Charts for SharePoint


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

SkyDrive Pro is the replacement application for SharePoint Workspace which is no longer included within Office 2013. SkyDrive Pro is intended to take its place when working with SharePoint 2013 and provide offline document synchronization.

SkyDrive Pro is separate to any consumer SkyDrive subscription with the Pro version being used only to store work related content.

This is great for offline access and management of files from all your devices.

How to sync a document library

  • Above every document library there is a SYNC button; click on it;

2013-07-22-SharePointSkyDrivePro-01.png

  • Next, we must configure SkyDrive Pro:
    • choose another path;
    • choose a document library;

2013-07-22-SharePointSkyDrivePro-02.png

At this point , the folder has been created. The folder is actually a mapped drive of a SharePoint document library.

We can copy-paste different files which then will be synced with SharePoint.

2013-07-22-SharePointSkyDrivePro-03.png

The result:

2013-07-22-SharePointSkyDrivePro-04.png

What is Focus on Content in SharePoint Online 2013?


You may also be interested in: O’Reilly - SharePoint 2010 at Work


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

In SharePoint Online 2013, in the Promoted Actions toolbar among Share, Follow, Edit, Sync commands, there is another command called Focus on Content.

What it does

  • when activated, it closes the left panel (the one with the picture, recent documents, navigation links, etc.

More exactly, it closes the left Navigation Pane for the current page.

If we exit the page and the Focus on Content is On, when we return to that page, the Navigation Pane will still be closed.

Before:

2013-07-15-SharePointFocusOnContent-01.png

After:

2013-07-15-SharePointFocusOnContent-02.png

How to Access the SharePoint Site and the Public Website


You may also be interested in: Mobilize SharePoint Webinar by Sitefinity


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

In the new Office 365, the Sites section contains shortcuts (links) to the external website and to the internal SharePoint site.

Of course, a user can add other sites to this section.

How to access the sites?

  • from the Office 365 account click on the Sites menu;
  • from the Sites page click on the desired tile.

2013-07-09-SharePointSitePublicWebsite-01.png

How to Add a Custom Logo in the Title Section of a SharePoint Site


You may also be interested in: O’Reilly - SharePoint 2010 at Work


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

In the site settings menu you will find options to add a custom logo, title or description.

If you want to add a logo somewhere else, you won’t find any option that allows you to do that.

Fortunately, all you have to do is edit the master page and add a custom code that will include a new table column with a logo, description and a link.

How to do that

  • upload a logo in the Site Assets library and copy the link;
  • go to Site Actions – Site Settings – Master Pages and download a copy of v4.master;
  • use a browser that has Developer Tools or Firebug (for this example I used Chrome with Developer Tools)

I will add a logo on the right side of the social tags.

  • right click on that area - Inspect Element.

2013-06-17-SharePointCustomLogo-01.png

In the Developer Tools panel we see that the class of the social tags area is <td>

2013-06-17-SharePointCustomLogo-02.png

Open the v4.master with a text editor (I propose Notepad++) and locate s4-socialdata-notif.

Add the following code:


<td><a title=”ITSpark – Your IT Adventure !” href=”http://itspark.ro” target=”_blank”><img style=”border:none” src=”https://officetube.sharepoint.com/SiteAssets/ITSpark_Logo_Header.gif” alt=”" width=”150″ height=”46″ /></a>
</td>

2013-06-17-SharePointCustomLogo-03.png

Save the file and then uploaded tothe Master Pages site.

Now, return to the home page and you will see the logo.

2013-06-17-SharePointCustomLogo-04.png

If you want to add a logo above the ribbon, locate the ID s4-workspace

The code will like this:


<div id=”s4-workspace”>
<div id=”s4-bodyContainer”>
<div id=”s4-titlerow”>
<div>
<div>
<img src=”https://officetube.sharepoint.com/SiteAssets/ITSpark_Logo_Header.gif” width=”1280″ height=”116″ />
<table cellspacing=”0″>

The bold line represents the extra line that will add an image in the background.

How to Add a Favicon to a SharePoint Site


You may also be interested in: Mobilize SharePoint Webinar by Sitefinity


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

Adding a favicon to a SharePoint site is not such a difficult operation.

First, what is a favicon ?

"Is a file containing one or more small icons, most commonly 16×16 pixels, associated with a particular Web site or Web page."

Note: this operation requires administrative wrights.

How to add a favicon ?

  • upload your favicon into the Site Assets library and copy the link;
  • go to Site ActionsSite SettingsMaster Pages;
  • download a copy of v4.master;
  • open it with Notepad or Notepad++;
  • do a search for favicon;
  • replace the Icon URL with the URL copied after you’ve uploaded the favicon into Site Assets;

Example:
<SharePoint:SPShortcutIcon runat=”server” IconUrl=”http://itspark.sharepoint.com/TeamSite/SiteAssets/favicon.ico” />

  • save v4.master and upload it in the Master Pages site.
  • go on the main page and see the new changes.

2013-06-12-SharePointFavicon-01.png

2013-06-12-SharePointFavicon-02.png

How to Add a Video to a SharePoint Site


You may also be interested in: fpweb.net


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

  • Note 1: To add a video into a SharePoint site using the default SharePoint tools, you need to have an E plan.

  • Note 2: To view a video you need to have Silverlight installed.

  • Note 3: The video must be uploaded in SharePoint.

How to do it

  • Go to Site ActionsSite Settings;

  • In the Site Collection Administration menu go to Site Collection Features;

  • Enable SharePoint Server Publishing Infrastructure feature.

2013-05-21-SharePointAddVideo-01.png

After enabling this feature in the Editing Tools tab – Insert – Media a new command appears: Video and Audio.

If you click on Video and Audio in the active area a video player will be inserted.

This player can then be customized as needed (width, height, name, etc.).

2013-05-21-SharePointAddVideo-02.png

By default you will have three options for inserting a video:

  • Computer;

  • SharePoint;

  • Web.

2013-05-21-SharePointAddVideo-03.png

If you choose to insert a video from SharePoint or Address, that video must be uploaded into a SharePoint library, or on a video sharing website (for the Address option).

The final result is:

2013-05-21-SharePointAddVideo-04.png

How to Open SharePoint Library Files in the Client Application


You may also be interested in: ViewPoint for SharePoint


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

There are cases when you would want users to open Office files in the client application.

SharePoint Online 2010 offers a very easy solution for this “problem”.

How?

  • access the desired document library;
  • from the Library tab click on Library Settings;

2013-04-30-SharePointLibraryClientApp-01.png

  • from the settings menu, click on Advanced settings;

2013-04-30-SharePointLibraryClientApp-02.png

  • in the Opening Documents in the Browser section add a tick on Open in the client application option and then save all changes.

2013-04-30-SharePointLibraryClientApp-03.png

From now on, every time a user tries to open a document from this library, the document will be opened in the client application.

How to Embed a Workbook in SharePoint Using Excel Web Access


You may also be interested in: SharePoint Conference.ORG 2013


 

Editor’s note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @AlexDionisie

According to Microsoft:

- Excel Services is a Microsoft Office SharePoint technology that makes it simple to use, share, secure, and manage Microsoft Office Excel 2007 workbooks (.xslx, xslb) as interactive reports in a consistent way throughout the enterprise. -

To be more specific; you can use this service to interact with various workbooks hosted in SharePoint.

Unlike SkyDrive, where we can get an embed code, here everything is simpler.

Of course, we can use embed code, but for this example we will use a web part.

How it’s done

  • upload a workbook into a site library
  • access the desired page
  • go into Edit Mode
  • from Insert click on More Web Parts
  • from the Business Data category insert the Excel Web Access Web Part

2013-04-03-EmbedWorkbook-01.png

  • now we have to access the Tool Pane.

2013-04-03-EmbedWorkbook-02.png

  • in this panel, depending on the requirements, we will alter the desired properties
  • for this example we will specify a workbook, a Name Range and change the width and height.

2013-04-03-EmbedWorkbook-03.png

2013-04-03-EmbedWorkbook-04.png

After we complete all these changes, we save and then exit the Edit Mode so we can view the result.

2013-04-03-EmbedWorkbook-05.png