The best way to build that 40-row table is Power Query, and the reason is straightforward: you want each row to pull data from different tables, not to perform complex calculations across them. Power Pivot excels at relational modeling and aggregation, but what you are describing is a data assembly task, combining specific values from separate sources into a single, flat structure. Power Query is built for exactly this kind of transformation.
Think of your 40 rows as a master index. Each row needs to reference one or more values from other tables, and you want that reference to be dynamic and repeatable. In practical terms, the most efficient approach is to use Power Query's **Merge** function. You can start with a small "lookup" table, just the row identifiers or keys, and then merge it with each of your other Power Query tables, one at a time. Each merge brings in the relevant columns you need for that row. After all merges are complete, you expand the columns to build your final table. This keeps your logic clean, your data source connections intact, and your 40-row output automatically updatable when the source tables change.
A common mistake here is to try to do this inside Power Pivot with DAX formulas, treating each row as a calculated measure. That works for summary reports, but it becomes brittle when you have 40 distinct rows pulling from different places. Power Query gives you a visual editor and a step-by-step audit trail. If one of your source tables adds a new column or changes a name, you can see exactly where the transformation breaks and fix it in one place. Power Pivot, by contrast, hides that logic inside measures, making it harder to troubleshoot and maintain.
The real insight here is that the tool should match the task. You have a straightforward collection problem, not a modeling problem. By choosing Power Query, you keep your workflow transparent, your table easy to update, and your options open if you later decide to add more rows or sources. Start with a blank query that defines your row structure, merge in each source table, and let Power Query handle the rest. That is the most efficient path to a table that stays reliable as your data evolves.