Forum Discussion
consolidate data (specific columns) from dynamic table list
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!
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).