Power Query里怎么取SharePoint TermSet数据

紫电青霜_FullStack
104 阅读6分钟

今天讲讲怎么在Power BI/Dataflow里直接连接SharePoint Taxonomy。全网基本上找不到这个方面的文章，我研究出来之后分享给大家。

PowerQuery自带的Source没有现成的SharePoint TermSet这类，我们得选web api然后调用Taxonomy Rest Api去取数据

image.png

SharePoint Taxonomy Rest Api

Sharepoint的Taxonomy Api实际上有两种，一种是Graph Api下的taxonomy api，比如

learn.microsoft.com/en-us/graph…

GET /sites/{site-id}/termStore/groups/{group-id}/sets/{set-id}/terms/{term-id}
GET /sites/{site-id}/termStore/sets/{set-id}/terms/{term-id}

但是你要跑Graph api你得要有对应的TermStore.Read.All的权限，这个得管理员去Azure Portal设置，一般人很难有这个权限。

所以我们这里还是用SharePoint Rest Api里的Taxonomy相关的api,比如https://{yourtenant}.sharepoint.com/sites/{sitename}/_api/v2.1/termStore/termSets/{termsetId}/getlegacychildren 去拿某个termset下面的一级term。 SharePoint Rest Api没有特别的权限要求，只要你有对应的Sharepoint的站点有权限就行。 但随着Graph Api的逐步完善，Sharepoint Rest Api也渐渐淡出了，你很难找到详细的文档，我搜了一下，这篇文章讲Sharepoint Taxonomy Rest Api还是蛮全的。

本文主要就用两种taxonomy api

  1. https://{yourtenant}.sharepoint.com/sites/{sitename}/_api/v2.1/termStore/termSets/{termsetId}/getlegacychildren 去拿某个termset下面的一级term
  2. https://{yourtenant}.sharepoint.com/sites/{sitename}/_api/v2.1/termStore/termSets/{termsetId}/terms/{parentTermId}/children" 去拿某个term下的子项

创建数据源

先拿第一层的terms

好了，理论知识就到这里，我们开始吧。 首先创建一个Web Api的Source，url里填https://{yourtenant}.sharepoint.com/sites/{sitename}/_api/v2.1/termStore/termSets/{termsetId}/getlegacychildren ， termsetId可以直接在Sharepoint Termset的页面找到，然后账号选择Organization Account

image.png

你会得到以下数据，点击value再展开

image.png

点击labels再展开，一个term往往可以有多个别名

image.png

我们在这里把别名给filter掉（就保留isDefault=true的record），就得到了第一层的terms的数据

如果要拿第二层，第三层的term，我们就得对每个有children的term去调用/_api/v2.1/termStore/termSets/{termsetId}/terms/{parentTermId}/children"api，所以得把获取term子项的过程包装成一个自定义函数（Custom Function）

创建Power Query自定义函数

微软的Power Query Custom Function的教程还是蛮冗长的，其实它主要就三部分，input参数，output参数，以及中间的步骤

let
    GetChildrenTerms = (parentTermId as text) as table =>
    {detail steps...}
in
    GetChildrenTerms

以我们这个拿children term的自定义函数为例子，input参数就是parenttermId,ouput就是一个table，中间的步骤比较麻烦，直接撸代码很有难度。

咱可以这样，先用UI界面创建一个获取某个特定term的子项的source，再把它改造成一个custom function。 先创建一个新的web api source，url里面输入 https://{yourtenant}.sharepoint.com/sites/{sitename}/_api/v2.1/termStore/termSets/{termsetId}/terms/{parentTermId}/children ，这里的{parentTermId}可以用某个实际的parent term id，然后重复我们创建第一个数据源的步骤，拿到所有的children term的数据。

然后把所有的代码copy出来，在一头一尾把它包装成函数，把具体的parentTermId替换成input参数

image.png

成品类似这样，你可以看到它的图标变成了“fx”

image.png

就可以在其他地方调用这个custom function了

创建另外一个数据源，获取第二层的term

我们先复制一下创建的第一个数据源,然后加一个filter来只保留含有chilren的term。

然后你要加一个step，去创建一个新的column来放调用“Get Children Terms”这个自定义函数所返回的table （这一步好像UI界面搞不定，得自己写script）

 #"Invoked custom function" =Table.AddColumn(#"Filtered rows", "ChildrenTerm", each #"Get Children Terms"([value.id])),

这样拿到了ChildrenTerm这个table，然后再对其进行展开

image.png

最后得到一个含有第二层terms的数据源

image.png

然后把它append到第一个数据源就得到第一层第二层的terms了.

因为我的项目这个termset只有两层结构，所以我的探索就到此为止了。

如果有第三层第四层怎么办

我问了一下DeepSeek，Power Query好像是支持递归里调用自定义函数的，但是实现起来不太好搞。 对Power Query的语法熟悉程度要求比较高。

image.png

理论上可以写一个类似的递归去遍历所有的子项，不过我感觉还是先用PnP Powershell把所有的terms导出到一个csv文件里，然后再用Power Query去读这个文件，这样应该会简单很多。PnP Powershell里处理别名，custom property什么的还更方便。

Copilot给的“旁门左道”（taxonomyHiddenList)

前面说了，全网几乎找不到如何用Power Query获取Sharepoint Term，我在写这个文章的时候，灵机一动问了Copilot，我们看看它的回答，全文摘录于下：

Fetching SharePoint terms in Power Query can be a bit tricky, but it's definitely doable! Here’s a step-by-step guide to help you get started:

Step 1: Connect to SharePoint Open Power BI Desktop or Excel. Go to the Home tab and click on Get Data. Select SharePoint Online List. Enter the URL of your SharePoint site and click OK.

Step 2: Select the Term Store List After connecting, you will see a list of available SharePoint lists. Look for the list that contains your term store data. This is often named something like "Term Store" or "Managed Metadata". Select the list and click Load or Transform Data to open the Power Query Editor.

Step 3: Transform Data in Power Query Editor In the Power Query Editor, you will see the data from your selected SharePoint list. Use the Transform tab to clean and shape your data as needed. This might include: Removing unnecessary columns. Filtering rows. Expanding nested tables or records.

Step 4: Extract Term Information If your term data is nested within records or tables, click on the expand icon (two arrows pointing outwards) next to the column header. Select the fields you want to extract and click OK.

Step 5: Load Data Once you have transformed your data to your satisfaction, click Close & Load to load the data into Power BI or Excel.

Example Code Snippet Here’s a basic example of M code that you might use in Power Query to fetch and transform SharePoint term data:

let
    Source = SharePoint.Tables("https://yoursharepointsite.sharepoint.com/sites/yoursite", [Implementation="2.0"]),
    TermStoreList = Source{[Name="Term Store"]}[Content],
    #"Expanded Term Data" = Table.ExpandRecordColumn(TermStoreList, "TermData", {"TermName", "TermID", "ParentTerm"}, {"TermName", "TermID", "ParentTerm"})
in
    #"Expanded Term Data"
This example assumes that your term data is in a column named TermData and that you want to expand fields like TermName, TermID, and ParentTerm.

Feel free to adapt these steps and the code snippet to fit your specific SharePoint setup and data structure. If you encounter any issues or need further customization, don't hesitate to ask!

简单的说，就是从TaxonomyHiddenList里去取数据，这样避开了反复调用api去拿childrenterm的步骤。 TaxonomyHiddenList实际上是Sharepoint的一个隐藏list（SharePoint有好几个隐藏的list，还有一个比较知名的是UserHiddenList），用来放关于taxonomy的一些系统信息。

这个list大致是长这样的

image.png

但是这个list里的数据往往和实际上的termset不一定一致，被删除被修改的term也会继续存在这个list里，假如termset里的某个term被创建出来后从来没被使用过的话，这个list不会有这个term的记录的。

所以我说Copilot的提供这个方法是旁门左道，不建议大家用。

avatar
文章
阅读
粉丝