none
No Managed Metadata Columns in Power Query SharePoint list queries

    Question

  • Hi, 

    when using Power Query to connect to an O365 SharePoint list using either the "From SharePoint List" connection or the "From OData Feed" connection I won't get the Managed Metadata column back in the result set. 

    There seems to be yet another problem with this SharePoint MMD (data from the TermStore) type of information ... anyone suggestions on this ? 

    Thanks,

    Jeroen

    Wednesday, June 18, 2014 7:02 AM

Answers

  • Hello,

    actually it is possible with the SharePoint List-Connector to retrieve MMS-Columns with the latest PowerQuery 2013/2016 and Power BI Desktop.

    As well as SP2013/SP2016 On Premise and Office 365 SharePoint Online. :-)

    But not with SP2010!

    This is not possible with the OData-Connector!

    The second Drawback: after expand a Managed Metadata Column you will get only the TermGUID and LabelID but not the displayfriendly TermLabel :-(

    But please take notice of some additional "Summary Columns" like "FiledValuesAsText".

    Please see http://whitepages.unlimitedviz.com/2016/07/simplified-method-working-sharepoint-data-power-bi/

    When you expand this columns you will get friendly Displaynames of all columns include Managed Metadata Termstore!

    In Addition with the SharePoint-List-Connector you will be able to retrieve the File.Version-Table and expand the detailed minor and minor Version for each file (with the specific CheckIn Comment)...

    Some further Hint: The SharePoint-List-Connector seems to have Trouble with Multivalue-Choice-Fields, avoid Mulivalue-Choices or exclude these fields from the Datasource...


    • Proposed as answer by Dale Hohm Thursday, June 23, 2016 12:12 PM
    • Marked as answer by JeroenSchoenmakers Tuesday, June 28, 2016 12:43 PM
    • Edited by Schneika Monday, July 11, 2016 8:54 AM FieldValuesAsText
    Thursday, June 23, 2016 9:58 AM
  • I searched around a bit and chatted with the team. Here's what we came up with:

    This link says that the OData endpoint for Sharepoint doesn't expose certain column types included managed metadata. http://social.msdn.microsoft.com/Forums/vstudio/en-US/bba6f165-b012-4c5b-8e1b-a6e0093bb333/sharepoint-2013-managed-metadata-field-support?forum=lightswitch
    Going through OData to ListData.svc directly would give you the same result.

    Monday, June 23, 2014 11:32 PM
    Moderator

All replies

  • I searched around a bit and chatted with the team. Here's what we came up with:

    This link says that the OData endpoint for Sharepoint doesn't expose certain column types included managed metadata. http://social.msdn.microsoft.com/Forums/vstudio/en-US/bba6f165-b012-4c5b-8e1b-a6e0093bb333/sharepoint-2013-managed-metadata-field-support?forum=lightswitch
    Going through OData to ListData.svc directly would give you the same result.

    Monday, June 23, 2014 11:32 PM
    Moderator
  • Thanks for your search and answer Ben !

    I'm hoping Managed Metadata columns will be added / supported by the ListData.svc very soon. Not only for the scenarios described in the link you posted but also from a Power Query / Power BI point of view is this type of data very often needed and relevant. As a SharePoint Consultant I'm convincing clients to manage their terms in the Term Store but when I start using this data I have to disappoint them so often !

    Thanks again,
    Jeroen

    Tuesday, June 24, 2014 9:24 AM
  • This is a huge limitation.  Managed metadata columns are used in practically all of our lists, and particularly for that information which has corporate value and therefore needs to be tightly controlled.  Not being able to analyse the data in these fields in Power Query and therefore PowerView in Sharepoint reduces the value of both of these features.   Is this something that is on the roadmap for 2016?

    Friday, November 20, 2015 12:09 AM
  • ARRRRGG!

    Really need this added to SharePoint. Have been building out Document Sets for deals that have some data we would like to report against in Excel and Power Pivot. Could I use a workflow to write the selected value to a hidden text\number field?

    Monday, December 21, 2015 6:08 PM
  • Hello,

    actually it is possible with the SharePoint List-Connector to retrieve MMS-Columns with the latest PowerQuery 2013/2016 and Power BI Desktop.

    As well as SP2013/SP2016 On Premise and Office 365 SharePoint Online. :-)

    But not with SP2010!

    This is not possible with the OData-Connector!

    The second Drawback: after expand a Managed Metadata Column you will get only the TermGUID and LabelID but not the displayfriendly TermLabel :-(

    But please take notice of some additional "Summary Columns" like "FiledValuesAsText".

    Please see http://whitepages.unlimitedviz.com/2016/07/simplified-method-working-sharepoint-data-power-bi/

    When you expand this columns you will get friendly Displaynames of all columns include Managed Metadata Termstore!

    In Addition with the SharePoint-List-Connector you will be able to retrieve the File.Version-Table and expand the detailed minor and minor Version for each file (with the specific CheckIn Comment)...

    Some further Hint: The SharePoint-List-Connector seems to have Trouble with Multivalue-Choice-Fields, avoid Mulivalue-Choices or exclude these fields from the Datasource...


    • Proposed as answer by Dale Hohm Thursday, June 23, 2016 12:12 PM
    • Marked as answer by JeroenSchoenmakers Tuesday, June 28, 2016 12:43 PM
    • Edited by Schneika Monday, July 11, 2016 8:54 AM FieldValuesAsText
    Thursday, June 23, 2016 9:58 AM
  • Some example for "FieldValuesAsText" expands Managed Metadata Columns with userfriendly TermLabel 

    Summary Columns


    Here the expandes Results, TermLabel as Plaintext, very fine...

    • Edited by Schneika Monday, July 11, 2016 11:12 AM
    • Proposed as answer by Schneika Thursday, April 27, 2017 7:15 AM
    Monday, July 11, 2016 8:57 AM
  • Hi,

    Is there a way to build reports in Excel 2010 including "Managed Metadata" from SharePoint 2013 ?

    I don't have any "SharePoint List Connector" with my PowerPivot. Only OData feed...

    Monday, December 19, 2016 1:10 PM
  • You will get more Data Sources with the Power Query Addin for EXCEL 2010/2013 

    https://www.microsoft.com/en-US/download/details.aspx?id=39379

    or with the Power BI Desktop Client.

    Wednesday, December 21, 2016 11:18 AM
  • Awesome, this is just what I was looking for to get the values of Managed Meta Data Fields.

    Tas Ranson

    Thursday, April 27, 2017 5:35 AM