Combining data sources with overlapping values

[BromBromBrom]

I want to combine 4 different Excel reports with username + last login.

Not all users are in every report.

How can I make a long list of all usernames + last login per system?

In Excel I would do this with xlookup but I want to automate this so I can place the reports in a folder every period and refresh the Power BI to reflect the new complete list of all users with 4 columns that show the last login in each system.

Thank you!

[dufoq3 · Accepted solution]

Hi, use FOLDER connection and combine all tables.

[PwerQueryKees · Accepted solution]

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"

[dufoq3 · Accepted solution]

Hi, use FOLDER connection and combine all tables.