Can queries be made in bulk?

[Monsignor]

Suppose I have 30 tables to append in Power Query. Does there have to be 30 separate queries made, or is there a way to make 30 queries all at once?

 

[Matt Allington]

Where are the tables? If they are files, you could use something like this. Combine CSV Files with Power Query - Excelerator BI. Depending on the source, you may be able to do so something similar with other sources.

 

[Monsignor]

Let's say the tables are sprinkled across several worksheets in the same workbook.

 

[ImkeF]

If they are real tables, you could do it like this:

Table.Combine({Excel.CurrentWorkbook(){[Name="[B]Table1[/B]"]}[Content], Excel.CurrentWorkbook(){[Name="[B]Table2[/B]"]}[Content], Excel.CurrentWorkbook(){[Name="[B]Table3[/B]"]}[Content]})

So basically calling every single table within curly brackets, no need for a separate query for each.

 

[anvg]

Hi
May simple do than with all tables if they have equal structure?

let
    source = Table.Combine(Excel.CurrentWorkbook()[Content])
in
    source

Regards,

 

[ImkeF]

Very smart!

If you don't want to combine all tables but want to make a pre-selection, then you start with: Excel.CurrentWorkbook()

This will return a list of all tables in your workbook. You can then filter the tables you want and simply click the Expand-arrows in column "Content".

 

[Matt Allington]

Let's say the tables are sprinkled across several worksheets in the same workbook.

I have a blog post for that too. Consolidated Worksheets with Power Query - PowerPivotPro

 

[Monsignor]

I used both M code solutions and they resulted in the tables being brought in together, but staggered. I could add a custom column and move anything that's not null. That's ok with single-column tables but creates other problems with multiple columns.

Is Matt's solution the one to try if I just want these tables appended without making queries one at a time?

 

[Monsignor]

I see why the columns were staggered: the headers weren't all the same. Silly me.

 

[anvg]

Hi
Could you upload your example file to dropbox or other place?
Regards,