Compare all tables under the same name between multiple excels

ingest all these sources into a single table, and include the source name (ie the file name and sheet name) as a column.

Hi Grasswall 

You can achieve this in Power BI by following these steps:

1. Load Multiple Excel Files from a Folder:

• In Power BI Desktop, go to Home > Get Data > Folder.
• Select the folder containing all your Excel files.
• Click Combine & Transform Data to open Power Query Editor.

2. Extract Tables from Each Excel File:

• In Power Query Editor, you'll see a list of files.
• Click on the Combine Files icon next to the Content column.
• In the Combine Files dialog, select a sample file.
• Power BI will automatically combine tables with the same name across all files.

3. Filter and Select Specific Tables:

• If you have multiple tables within each Excel file, you'll need to expand the Data column.
• Use Add Column > Custom Column to extract tables by name:

= Table.SelectRows(Excel.Workbook([Content]), each [Kind] = "Table" and [Name] = "YourTableName"){0}[Data]

• Repeat for each table name you want to compare.

4. Combine Tables into a Single Table:

• Append all extracted tables into one master table.
• Use Add Column to create a custom column that captures the source file name (e.g., experiment ID).

5. Compare Data by Matching Table Names and Parameters:

• Ensure the combined table includes columns for Table Name, Parameter, and Experiment ID.
• Use Pivot or Merge Queries to align data based on Table Name and Parameter.
• This will allow you to see differences in parameters across experiments.

6. Visualize the Differences:

• In Power BI, use a Matrix or Table visual.
• Place Parameter on rows, Experiment ID on columns, and the values you wish to compare in the values section.
• Apply Conditional Formatting to highlight differences.

Additional Tips:

• Automate Refresh: The report will automatically include new Excel files added to the folder upon refresh.
• Consistent Structure: Ensure all tables have the same structure and naming conventions.
• Error Handling: Use Power Query steps to handle any missing tables or data inconsistencies.

By following these steps, you can create a dynamic report that automatically loads and compares tables from multiple Excel files, matching them by table name and parameter.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube