consolidate data (specific columns) from dynamic table list

[Mario_Passweg]

Dear Community!

I'm working quite a while now on a problem in Power BI Desktop and can't find a solution. As .net developer for me it seemed like a easy problem (if I have to code it), but I can't make it work in Power BI as I'm a newbi to the topic of Power Query and M.

Setup

• In my Dashboard/dataset I have multiple KPI tables (from different sources).
• All of them are containing 3 columns named "RunDate", "Junior" and "Senior" (and additional ones that aren't relevant for this problem).
• All tablenames are starting with "RepDB_".
• Currently I have 56 of those, but the list is steadily growing (1-4 additional ones per month).

Goal

• create a single table at the data stage, in which the data of all "RepDB_" tables are summerizied per RunDate - including the future ones that will get added

A first step I managed to solve - get a dynamic list of all relevant tablenames - with this expression:

let
Source = #shared,
#"Converted to Table" = Record.ToTable(Source),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each (Text.StartsWith([Name], "RepDB_"))),
#"Remove Column" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
#"Remove Column"

But I'm completly stuck on the next step(s) - get the 3 relevant Columns for each "tablename" of the first step.

In my mind I would need to do the following steps:

1. somehow iterate through all the rows of the above table/list
2. query and add the three columns "RunDate", "Junior" and "Senior" of the table named in my current row
3. expand that result (as far as I understood Power Query it will be a table or list or something like that) so I have one row for each row of the target table in my table
4. remove the tablename column from my current table
5. summerize somehow the "Junior" and "Senior" values per "RunDate" (but that most probably can easily be done in DAX/the visual it self too)

--------------------------

Here a visualisation of my tables in case my explaination was a little bit confusing

Table: RepDB_1

RunDate	Junior	Senior
2025-01-01	100	20
2025-01-12	40	60
2025-01-15	20	20

Table: RepDB_2

RunDate	Junior	Senior
2025-01-01	40	10
2025-01-12	50	50
2025-01-17	10	20

Result I hope for:

Table: Sums

RunDate	Junior	Senior
2025-01-01	140	30
2025-01-12	90	110
2025-01-15	20	20
2025-01-17	10	20

I hope someone can help me 🙂

Thanks in advance and best regards,

Mario

[v-tsaipranay]

Hi @Mario_Passweg ,

Thank you for reaching out to the Microsoft Fabric Community.

I wanted to check if you had the opportunity to review the information provided by @SundarRaj which is effectively addresses the requirement for dynamically consolidating tables and summarizing key columns. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[SundarRaj · Accepted solution]

Hi @Mario_Passweg , here's a solution on PQ I could manage to do. Take a look and let me know if I understood your query correctly.

1. If you look at the second table, you'll see the "Content" column containing your tables (before filtering out for the irrelevant columns). This expands and gets added as and when you add more tables to the excel file.

2. Using Table.Combine(Source[Content]) will give you the combined table to work with.

3. What I've done to get to the sum for Junior / Senior is use the grouping function in Power Query.

4. I'll just drop the M code used for better reference. 

Let me know if I solved your query. Thanks very much!

[Mario_Passweg]

it worked! Thank you very much!

[Mario_Passweg]

with this function I encounter 2 problems:

1) the tables aren't related to each other, but completly seperated and if I understood it correctly, for "RELATED" to work, there has to be a relation between the tables

2) I would have to add each new table manually into the measurement. Actually that is the way I currently do it till I managed to create the "Goal" table. Currently I have two measurements with sum(RepDB_1[Junior]) + sum(RepDB_2[Junior] + .... that I expand each time I add another RepDB table.

My goal would be, that I don't have to do anything additional anywhere in a measurement, calculated column, ... if I add another RepDB_ table, as it is added automatically in the "consolidated" table and all my visuals are only based on this big table (it isn't really big, as there is just one entry per day per table maximum).

[Abhiram_ambati]

Try using sumx function it allows us to perform row wise operations 

measure = sumx(RepDB_1, 'RepDB_1'[Junior] + RELATED('RepDB_2[Junior]))

same thing with Senior .