使用Power BI查询SharePoint多子站点中状态为Closed的同名List A数据

Hey there! Let's walk through exactly how you can pull all those closed project entries from every SharePoint subsite's List A and turn them into the clean table/Excel report you need—even if you're totally new to Power BI. I've broken this down into simple, actionable steps tailored to your scenario:

Power BI is perfect here because it can handle all 90+ subsites at once, and you can easily refresh the report later if new closed items are added.

1.1 Connect Power BI to Your Root SharePoint Site #

• Open Power BI Desktop, click Get Data > SharePoint Online List from the home tab.
• Paste your root SharePoint site URL (like https://yourtenant.sharepoint.com/sites/yourmainprojectsites) and hit OK.
• Sign in with your SharePoint credentials—make sure this account has read access to all 90+ subsites and their List A lists.

1.2 Load List A from All Subsites (The Key Step) #

Instead of manually adding each subsite one by one, we'll use a bit of code to automate this:

• After connecting to your root site, go to the Home tab and click Advanced Editor (this opens the Power Query editor where we can write a loop for all subsites).
• Replace the default code with this snippet—just update the subsite URLs and list name to match your setup:

let
    // List out all your subsites here (or use dynamic logic if you want to auto-pull subsites)
    Subsites = Table.FromRecords({
        [SiteURL = "https://yourtenant.sharepoint.com/sites/yourmainprojectsites/subsite1"],
        [SiteURL = "https://yourtenant.sharepoint.com/sites/yourmainprojectsites/subsite2"],
        // Keep adding all 90+ subsites here
    }),
    // Function to load List A from a single subsite and filter closed items
    LoadClosedListA = (SiteURL as text) =>
        let
            Source = SharePoint.Tables(SiteURL, [ApiVersion = 15]),
            ListA = Source{[Name="List A"]}[Items], // Match your list's exact name!
            FilterClosedItems = Table.SelectRows(ListA, each [Status] = "Closed"),
            // Add a column to track which subsite each entry came from
            AddSiteContext = Table.AddColumn(FilterClosedItems, "Subsite URL", each SiteURL)
        in
            AddSiteContext,
    // Apply the function to every subsite and combine all data
    CombinedClosedData = Table.Combine(List.Transform(Subsites[SiteURL], LoadClosedListA))
in
    CombinedClosedData

• Hit Done—Power BI will now pull all closed List A entries from every subsite and combine them into one table.

1.3 Clean Up and Export to Excel #

• Once the data loads, you can remove any unnecessary columns, rename messy field names, or add notes using the Power Query editor.
• When you're happy with the data, go to Home > Export > Export to Excel—this saves your combined closed items as a clean Excel file.

If you'd rather skip Power BI and use OData with Excel's Power Query:

• For a single subsite, the OData filter URL would be:
  https://yourtenant.sharepoint.com/sites/yourmainprojectsites/subsite1/_api/web/lists/getbytitle('List A')/items?$filter=Status eq 'Closed'
• In Excel, go to Data > Get Data > From Other Sources > From OData Feed, paste the URL, and repeat for every subsite. Then use Power Query to merge all the tables into one.
• Note: This is tedious for 90+ sites, so Power BI's automated loop is way more efficient.

• Permissions Double-Check: Make sure your account can access every subsite—if some fail to load, it's almost always a permission issue.
• Case Sensitivity: SharePoint cares about exact names—if your list is actually "List a" (lowercase a), update the code to match.
• Automate Refreshes: In Power BI, you can set up a scheduled refresh so your report updates automatically without manual work.

内容的提问来源于stack exchange，提问作者jaredjohn