Create many tables from table of tables

Power Query
1

Here is my pbix:
tableoftables.pbix (21.0 KB)

I have a table of tables. Can I invoke one function to create/expand each of the tables as new individual tables? I want to end up with the original (table of tables) alongside all of the individuals.

Currently the built in Expand All function creates one enormous table and joins them but this isn’t what I’m hoping to do.

For example see example1 and example2:
20211116_1720_tableoftables

2

Hi @izzleee,

The only way I’m aware of where multiple queries are created in “one go” is with selections from a database or excel file…

I’m not sure if C scripts in Tabular Editor will allow you to do this, maybe you could look into that.

I hope this is helpful

3

Thankyou Melissa,

I think everyone agrees - if Melissa doesn’t know, nobody does!

I might try to find a way of doing joins and transforms elsewhere. My problem is I don’t have access to this database (yet). In terms of performance is there a general rule of what is faster:

  1. Expanding big flat table and trimming down into smaller tables (referencing)
  2. Expanding individual tables and grouping

Thanks,

Isaac

4

Hi @izzleee,

Good question and hard to answer at the same time, performance can be impacted by several things. Best approach when in doubt is built both, run diagnostics a couple of times to measure transformation- and/or overall speed to confirm.

I hope this is helpful

5

Hi @Melissa,

Good news is we got access to the database so much easier to pick out tables and do transforms there.

I stumbled across this solution which looks to be asked and solved originally by Imke Feldman.

This doesn’t answer my original question but might be of interest to others on Enterprise DNA.

1 Like