Power Query - external editor or "really advanced" Advanced Editor

[Anonymous]

Hi Everyone!

I have an Excel file with over 100 tables inside and I want to have them in separate queries. I have transformed them using parameters and functions but last thing I have to do is extract them from the nested tables. Now I create a blank query and I enter formula:

= #"transformedQuery"{index}[columnName]

then I duplicate the query and just changing the Index.

It is not a problem to generate the same formula with a different index, but the thing is to create multiple queries faster than click, duplicate, change index, rename query and repeat.

Thank you in advance! 🙂 

[AlexisOlson]

I'd guess this is possible using scripting in Tabular Editor.

https://docs.tabulareditor.com/te2/Advanced-Scripting.html

It seems strange to me that you'd actually want that many queries unless each table has a unique structure. Have you considered combining the tables instead of loading them all separately?

[Anonymous]

I have looked at the Tabular Editor before and it allows to manage the tables actually loaded into PowerBI but my tables are still nested in PQ table. I have to extract them, load and then maybe I could use this tool.

Maybe I am little bit dramatizing with "over 100 tables", I will probably use a half of them but yes, these tables have a unique structure, so combining them won't work unfortunately.