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文件的SharePoint路径

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

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

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

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

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

    RootContents = Table.Buffer(SharePoint.Contents(SharePointSiteUrl)),
    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文件

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

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

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

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

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

    RootContents = Table.Buffer(SharePoint.Contents(SharePointSiteUrl)),
    Source = List.Accumulate(Text.SplitAny(FolderPath,"/\"),RootContents,(r,c)=>r{[Name=c]}?[Content]?),
    ExcelWorkbook = 
        Table.RenameColumns(
            Table.AddColumn(
                Table.SelectRows(Source,each [Extension]=".xlsx"),
                "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})),
    Result = Table.Combine(AddFileAndSheetName)

in
    Result

总结

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