No Managed Metadata columns in Power Query

When using Power Query to retrieve data from an O365 SharePoint list, you run into trouble when that list data contains columns connected to the Term Store (managed metadata).

These columns are not retrieved by Power Query, not using the "SharePoint List" connection and also not using the "oData" connection.

So, the managed metadata column "organisatie eenheid" is in this list:

But not in the Power Query result sets (oData):

Or in the "From SharePoint List" resultset:

I've notified Microsoft of this issue here, let's wait and see if there are any workarounds or fixes !
Will keep you posted...

Update 24 June 2014:

Ben Martens from Microsoft searched around and asked the product team about this issue and unfortunately the conclusion is that right now, it's not possible to get MMD data extracted from SharePoint.
Both the ListData.mvc as OData connections won't bring back these columns from your SharePoint lists.
Sorry for you ...
Let's hope these services get modified by the SharePoint team and start exposing this data, there are many scenarios where people really want to work with the Managed Metadata ! (why use it if it's not supported !?!). Thanks Ben for this update !

#PowerPivot refresh schedule disappears after creating new version of #excel file on #SP2013 #help

The challenge is as follows:
When you create a new version of your PowerPivot excel file and you load it to SharePoint 2013, the Data Refresh Schedule you had configured is now gone.. As soon as there is a new version (or a new file with versions disabled) the schedule is gone and you have to re-create it.
This problem only seems to happen on SharePoint 2013, SharePoint 2010 keeps a reference to the selected schedule after updating the excel document.

The problem visual:
A Power Pivot Excel document with a Schedule enabled:

Schedule is enabled:

Make a new version & save document:

Overwrite existing file:

Check PowerPivot Data Refresh:

Gone ...

No fix yet, will update this post when I find it !

(Suggestions are welcome ..) 

4 Feb Update:

I found the Microsoft KB that describes this issue (Sql Bug 1377755)  in the CU6 update for SQL Server 2012:

http://support.microsoft.com/kb/2874957/en-us

I installed the CU8 but so far no luck..the issue is still there!

5 Feb Update:
Posted question on the Microsoft PowerPivot for SharePoint forum

15 April Update:
It seems that "psiodmakFuture-Processing" found the cause of this bug:
We have this problem as well.

I think I found a possible cause. Try the following script:

SPWeb http://yoursharepoint.com).GetFile(path/to/file.xlsx).Properties['ppDRSchedule']

for a working file it should spew out an escaped XML. It's your schedule that was serialized and cached here probably to speed things up. When you save the document with Excel, this value is trimmed to 255 characters, rendering it broken, so the UI (and the timerjob) thinks that the schedule is missing. This might as well be a bug in Excel.

Now we just need a solution from Microsoft :)

23 April Update:
CU 9 for SQL Sever 2012 SP1 is now downloadable, let's give it a try and see if this resolves the outstanding issue...

Installing SharePoint 2013 (preview)

Ok, there are some fabulous blog-posts out there on how to install SharePoint 2013 (preview). One thing most of them are missing though: the fact that your (production) servers don't have internet connection. Articles from Microsoft like these Link are helpful but not complete. To reflect this situation I decided to create a VM with no internet connection and install SharePoint 2013.
This changes the game a bit; I recommend putting on some nice relaxed music like EC 'Old Love' Link which gives you about 8:45 minutes & read trough this blog post to prevent some hassles.




Step 1; download SharePoint 2013
http://technet.microsoft.com/en-us/evalcenter/hh973397.aspx
(2.2 GB of binaries but NO PREREQUISITES !!!)

Step 2: Write down the product key:
6RNT8-XV26M-GWH36-VMGQH-94MMH

Step 3: This is a .img file, my Oracle Virtual Box can't read these.
Solution, using Virtual Clone Drive to mount the .img & then add a virtual drive to the virtual clone drive drive in virtual box, giving the VM access to the SharePoint 2013 goodies.

Step 4: Download the prerequisites.
A nice overview can be found here: http://blogs.msdn.com/b/ocarpen/archive/2012/07/31/sharepoint-2013-preview-pre-requisites-download-links.aspx?CommentPosted=true#commentmessage
Thanks Oliver

Step 5: Deal with the prerequisites issues.
First I thought I could just run each .msi / .exe by hand.. this does not work.
You will have to run the prerequisites installer from the (admin) command prompt to install all prerequisites step by step:






1) Microsoft .NET Framework 4.5
Link
prerequisiteinstaller.exe /NETFX:dotNetFx45_Full_x86_x64.exe

2) Windows Management Framework 3.0 (CTP2)
Link
prerequisiteinstaller.exe /PowerShell:Windows6.1-KB974405-x64.msu

3) Microsoft SQL Server 2008 r2 Native Client
Link
Sqlncli.msi
prerequisiteinstaller.exe /SQLNCli:Sqlncli.msi

4) Windows Identity Foundation (KB974405)
Link
Windows6.1-KB974405-x64.msu
prerequisiteinstaller.exe /

5) Microsoft Sync Framework Runtime v1.0 SP1 (x64)
Link
Synchronization.msi
prerequisiteinstaller.exe /

6) Windows Server AppFabric
Link
prerequisiteinstaller.exe /AppFabric:windowsServerAppFabricSetup_x64.exe

7) CU Package 1 for Microsoft AppFabric 1.1 for Windows Server (KB2671763)
Link
Manually installed (executed .exe)

8) Windows Identity Extensions
Link
MicrosoftIdentityExtensions-64.msi
prerequisiteinstaller.exe

9) Microsoft Information Protection and Control Client
Link
setup_msipc_x64.msi
prerequisiteinstaller.exe /MSIPCClient:setup_msipc_x64.msi

10) Microsoft WCF Data Services 5.0
Link
You have to run: the trusted root certificate update program before this step; then execute the install
X86-all-rootsupd_90f0b77f9f5fbe6c5227f3fac95c926087f53fce.exe

Before Installing, Update for Root Certificates For Windows Server 2008 [April 2012] (KB931125)

WcfDataServices.exe
prerequisiteinstaller.exe /WCFDataServices:WcfDataServices.exe

Step6: WcfDDataServices
The WcfDDataServices comes with it's own problem. It needs a trusted root certificate which the installer can't download from the web. So you need to do it for it.
This can be done by doing a 'search' on 'root update' at the Microsoft update center:
http://catalog.update.microsoft.com/v7/site/Search.aspx?q=root%20update

There you can download the Update for Root Certificates For Windows Server 2008 [April 2012] (KB931125).
Install the file (it doesn't give you any clue if it succeeded), after that you can run the prerequisiteinstaller.exe /WCFDataServices:WcfDataServices.exe command and this install will succeed.


Step 7: Download Service Pack 1 For SQL Server 2008R2
Yep, it's time to upgrade that SQL !
Link

Step 8: Ready to install!!
Yep, now you're all ready & set to install the actual SharePoint 2013 binaries !
You can follow some setup guidance available by great other bloggers like:
Eric Harlan

Step 9: Oh, it seems there are some more prerequisites to install, to prevent problems with your Search Service Application later
http://support.microsoft.com/kb/2554876
http://support.microsoft.com/kb/2708075
http://support.microsoft.com/kb/2472264