使用PowerBI读取或合并SharePoint上的Excel文件

SharePoint作为微软生态的一部分，能实现团队协作，在团队之间共享文件与数据，因此SharePoint也经常作为PowerBI的数据源。虽然PowerBI里提供了原生的SharePoint接口，但还是具有一定的难度，因此本篇文章将介绍如何读取并合并SharePoint上的Excel文件。

读取单个Excel文件

使用Excel文件的网络路径

1、在SharePoint上找到对应Excel文件，查看其详细信息，然后点击路径隔壁的按钮即可获取到网络路径，如下图所示：

2、在PowerQuery中新建空查询，将以下代码粘贴到高级编辑器，并修改相关参数即可

let
    ExcelFileUrl = "Excel文件的网络路径",
    SheetName = "需要读取的工作表名称",

    ExcelWorkbook = Excel.Workbook(Web.Contents(ExcelFileUrl),true,null),
    Result = ExcelWorkbook{[Item=SheetName,Kind="Sheet"]}[Data]
in
    Result

注意：该方式只需要有对应Excel文件的访问权限即可，是读取单个Excel时的最佳方案。

---

使用Excel文件的SharePoint路径

1、在SharePoint上找到对应Excel文件，然后从浏览器上方地址栏处截取Excel文件所在的SharePoint站点的链接，SharePoint站点链接的格式为："https://TenantName.sharepoint.com/sites/SiteName"

2、查看Excel文件的详细信息，记录SharePoint路径，路径从文档开始，如下图所示：

最后，再把文档替换成英文，即：Shared Documents，再把分隔符改为斜杠或反斜杠即可得到完整路径。如上图所示的Excel文件的完整路径应该为：Shared Documents/TestFolder/文件名1.xlsx

3、在PowerQuery中新建空查询，将以下代码粘贴到高级编辑器，并修改相关参数即可

let
    SharePointSiteUrl = "https://TenantName.sharepoint.com/sites/SiteName",    // 此处填写SharePoint站点链接
    FolderPath = "Shared Documents/TestFolder/文件名1.xlsx",    // 此处填写Excel文件的SharePoint路径
    SheetName = "需要读取的工作表名称",

    RootContents = Table.Buffer(SharePoint.Contents(SharePointSiteUrl,[ApiVersion="Auto"])),
    Source = List.Accumulate(Text.SplitAny(FolderPath,"/\"),RootContents,(r,c)=>r{[Name=c]}?[Content]?),
    ExcelWorkbook = Excel.Workbook(Source,true,null),
    Result = ExcelWorkbook{[Item=SheetName,Kind="Sheet"]}[Data]
in
    Result

注意：该方式需要有对应Excel文件及其所有上级文件夹的访问权限，仅有Excel文件的访问权限并不够。

合并文件夹下的所有Excel文件

使用文件夹的网络路径

1、在SharePoint上找到对应文件夹，然后从浏览器上方地址栏处截取文件夹所在的SharePoint站点的链接，SharePoint站点链接的格式为："https://TenantName.sharepoint.com/sites/SiteName"

2、查看文件夹的详细信息，然后点击路径隔壁的按钮即可获取到网络路径，如下图所示：

3、在PowerQuery中新建空查询，将以下代码粘贴到高级编辑器，并修改相关参数即可

let
    SharePointSiteUrl = "https://TenantName.sharepoint.com/sites/SiteName",   // 此处填写SharePoint站点链接
    FolderPath = "https://TenantName.sharepoint.com/sites/SiteName/FolderPath",    // 此处填写文件夹的网络路径

    RootContents = Table.Buffer(SharePoint.Files(SharePointSiteUrl,[ApiVersion="Auto"])),
    Source = Table.SelectRows(RootContents,each [Folder Path]=Uri.Parts("a?a="&FolderPath&"/")[Query][a]),
    ExcelWorkbook = 
        Table.RenameColumns(
            Table.AddColumn(
                Table.SelectRows(Source,each [Extension]=".xlsx" or [Extension]=".xls" or [Extension]=".xlsm"),
                "Workbook",each Excel.Workbook([Content],true,null)
            )[[Name],[Workbook]],
            {{"Name","FileName"}}
        ),
    #"Expanded {0}" = Table.ExpandTableColumn(ExcelWorkbook, "Workbook", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each [Kind]="Sheet"),  // 如果只合并Excel中的某个工作表，则可以再加一个限制条件：[Item]="SheetName"
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Name", "SheetName"}}),
    #"Removed Other Columns" = Table.Buffer(Table.SelectColumns(#"Renamed Columns",{"FileName", "SheetName", "Data"})),
    AddFileAndSheetName = Table.ToList(#"Removed Other Columns",each Table.AddColumn(Table.AddColumn(_{2},"FileName",(x)=>_{0},type text),"SheetName",(x)=>_{1},type text)),
    CombineTable = Table.Combine(AddFileAndSheetName),
    #"Removed Blank Rows" = Table.SelectRows(CombineTable, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(Record.RemoveFields(_,{"FileName","SheetName"})), {"", null}))),
    Result = #"Removed Blank Rows" 
in
    Result

注意：该方式只需要有对应文件夹的访问权限即可，适合文件数量较少的站点或只有对应文件夹权限时使用。

---

使用文件夹的SharePoint路径

1、在SharePoint上找到对应文件夹，然后从浏览器上方地址栏处截取文件夹所在的SharePoint站点的链接，SharePoint站点链接的格式为："https://TenantName.sharepoint.com/sites/SiteName"

2、记录文件夹的SharePoint路径，路径从文档开始，如下图所示：

最后，再把文档替换成英文，即：Shared Documents，再把分隔符改为斜杠或反斜杠即可得到完整路径。如上图所示的文件夹的完整路径应该为：Shared Documents/TestFolder

3、在PowerQuery中新建空查询，将以下代码粘贴到高级编辑器，并修改相关参数即可

let
    SharePointSiteUrl = "https://TenantName.sharepoint.com/sites/SiteName",   // 此处填写SharePoint站点链接
    FolderPath = "Shared Documents/TestFolder",    // 此处填写文件夹的SharePoint路径

    RootContents = Table.Buffer(SharePoint.Contents(SharePointSiteUrl,[ApiVersion="Auto"])),
    Source = List.Accumulate(Text.SplitAny(FolderPath,"/\"),RootContents,(r,c)=>r{[Name=c]}?[Content]?),
    ExcelWorkbook = 
        Table.RenameColumns(
            Table.AddColumn(
                Table.SelectRows(Source,each [Extension]=".xlsx" or [Extension]=".xls" or [Extension]=".xlsm"),
                "Workbook",each Excel.Workbook([Content],true,null)
            )[[Name],[Workbook]],
            {{"Name","FileName"}}
        ),
    #"Expanded {0}" = Table.ExpandTableColumn(ExcelWorkbook, "Workbook", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each [Kind]="Sheet"),    // 如果只合并Excel中的某个工作表，则可以再加一个限制条件：[Item]="SheetName"
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Name", "SheetName"}}),
    #"Removed Other Columns" = Table.Buffer(Table.SelectColumns(#"Renamed Columns",{"FileName", "SheetName", "Data"})),
    AddFileAndSheetName = Table.ToList(#"Removed Other Columns",each Table.AddColumn(Table.AddColumn(_{2},"FileName",(x)=>_{0}),"SheetName",(x)=>_{1})),
    CombineTable = Table.Combine(AddFileAndSheetName),
    #"Removed Blank Rows" = Table.SelectRows(CombineTable, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(Record.RemoveFields(_,{"FileName","SheetName"})), {"", null}))),
    Result = #"Removed Blank Rows" 
in
    Result

注意：该方式需要有对应文件夹及其所有上级文件夹的访问权限，仅有对应文件夹的访问权限并不够，适合具有整个站点权限时使用。

总结

如果需要读取与合并的不是Excel文件，而是CSV等其它文件，那么只需要修改解析函数即可，比如将Excel.Workbook改成Csv.Document即可读取CSV与TXT文件。