Combining data sources with overlapping values

Hi, use FOLDER connection and combine all tables.

Without knowing exactly what your data looks like...

Assuming each system has a separate sheetm each sheet is in a separate workbook, the column names are the same.

I start with excel sheets formatted like this:

Each sheet has all login times of a user.

The result woul look like this:

One row for each user with a column per system showing the last login. The column name is the name of the Excel sheet...

The Powerquery would be like this:

let
    // Get the excel files from a folder
    Source = Folder.Files("C:\Users\keess\OneDrive\Documents\- Tools en Programmeren\Power Query\Fabric Community\Combining data sources with overlapping values"),
    // Filter the sheets YOU are interested in.
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "System")),
    // Add a column with the contents of the workbooko: A table with all the sheets and tables.
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content])),
    // Expand the table, showing a row for each sheet and table in your book.
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    // Filter the sheets and tables you need.
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Sheet")),
    // Select thje minimum number of columns you need
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Custom.Data"}),
    // Rename the column that represents the name of the system.
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "System"}}),
    // Needed if you imported sheets. If you import tables, this step is not needed
    #"Promoted Headers" = Table.TransformColumns(#"Renamed Columns",{{"Custom.Data", each Table.PromoteHeaders(_, [PromoteAllScalars=true])}}),
    // Add the actual data columns. Tis is now all sytems combined
    #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Promoted Headers", "Custom.Data", {"User", "Last Login"}, {"Custom.Data.User", "Custom.Data.Last Login"}),
    // Get the last login date for each system as a separate column
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom.Data", List.Distinct(#"Expanded Custom.Data"[System]), "System", "Custom.Data.Last Login", List.Max)
in
    #"Pivoted Column"