Reporting

Generic Pivot Tables easy intoduction
Generic tutorial on getting data from files in SharePoint into Excel or PowerBI

All xlsx files in the folder are consolidated into 1 result set.
Duplicates are filtered out on the primary key columns (Name, Path, FileAction) and the row with the newest datetime in the JobDate column is kept.

“M” code for Power Query Advanced Editor

*Use the code in the Power Query Advanced Editor to load the data into Excel table, Excel Data Model or PowerBI
Note that Power Query with SharePoint cannot consolidate more than 5000 Excel files from any one folder.

Report column layout

let
    // Connect to SharePoint folder
    Source = SharePoint.Files("https://MyTenant.sharepoint.com/sites/MySite", [ApiVersion = 15]),

    // Filter to the specific folder
    FilteredFolder = Table.SelectRows(Source, each Text.Contains([Folder Path], "Report/MyFolder")),

    // Filter for Excel files
    FilteredFiles = Table.SelectRows(FilteredFolder, each Text.EndsWith([Extension], ".xlsx")),

    // Function to extract data from each file
    ExtractData = (file) =>
        let
            Source = Excel.Workbook(file, true),
            Sheet = Source0[Data]
        in
            Sheet,

    // Add a custom column to extract data
    AddedCustom = Table.AddColumn(FilteredFiles, "ExcelData", each ExtractData([Content])),

    // Remove unnecessary columns
    RemovedOtherColumns = Table.SelectColumns(AddedCustom, {"ExcelData"}),

    // Expand the data
    ExpandedData = Table.ExpandTableColumn(RemovedOtherColumns, "ExcelData", Table.ColumnNames(RemovedOtherColumns0[ExcelData])),

    // Change data types
    ChangedType = Table.TransformColumnTypes(ExpandedData,{{"SizeMB", type number}, {"Created", type datetime}, {"Modified", type datetime}, {"ModifiedReplaced", type datetime}, {"NameLen", Int64.Type}, {"SourcePathLen", Int64.Type}, {"DestinationPathLen", Int64.Type}, {"FileShareAccessed", type datetime}, {"JobDate", type datetime}}),

    // Remove duplicate rows based on primary key columns and keep the newest JobDate
    GroupedRows = Table.Group(ChangedType, {"Name", "Path", "FileAction"}, {
        {"AllRows", each Table.Sort(_, {{"JobDate", Order.Descending}})},
        {"TopRow", each Table.FirstN(Table.Sort(_, {{"JobDate", Order.Descending}}), 1)}
    }),
    RemovedOtherColumns2 = Table.SelectColumns(GroupedRows, {"TopRow"}),
    ExpandedTopRow = Table.ExpandTableColumn(RemovedOtherColumns2, "TopRow", Table.ColumnNames(ChangedType))
in
    ExpandedTopRow

Copyright © 2024 MigrateDMS