Condense code to one Excel.CurrentWorkbook statement

[p45cal]

I currently have the likes of:

let
    Table1= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2= Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Table3= Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Table4= Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Table5= Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Table6= Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Source = Table.Combine({Table1, Table2, Table3, Table4, Table5, Table6}),
…

Is it possible to condense the six CurrentWorkbook lines into one CurrentWorkbook statement so that ultimately I might end up with (I know it's wrong) the likes of:

Source = Table.Combine(Excel.CurrentWorkbook(){Table1,Table2,Table3,Table4,Table5,Table6}[Content]),

I realise I could use Excel.CurrentWorkbook() on its own and later filter the list and expand. Also I could probably:

Source = Table.Combine({Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Excel.CurrentWorkbook(){[Name="Table2"]}[Content], Excel.CurrentWorkbook(){[Name="Table3"]}[Content], Excel.CurrentWorkbook(){[Name="Table4"]}[Content], Excel.CurrentWorkbook(){[Name="Table5"]}[Content], Excel.CurrentWorkbook(){[Name="Table6"]}[Content]}),

I was wondering whether there was a syntax for mutliple, hard-coded table names to be included in one Excel.CurrentWorkbook statement? (The Power Query's functions Help is no help on this, not even mentioning the single table syntax.)

 

[Ken Puls]

I was wondering whether there was a syntax for mutliple, hard-coded table names to be included in one Excel.CurrentWorkbook statement? (The Power Query's functions Help is no help on this, not even mentioning the single table syntax.)

Not that I'm aware of, no.

 

[Bob Phillips]

Excel.CurrentWorkbook()

brings in all of the tables in the workbook, you can filter out any you don't want.

 

[Ken Puls]

Hey Bob,

Yeah, I suppose I answered a small piece of that, but maybe not the real issue.

P45cal,

Like Bob says, just using Excel.CurrentWorkbook() will bring you a list of all the tables in the workbook. At that point you can filter out the ones you don't want (and you should if you're going to create a table as an output, or it will become part of a Power Query circular reference.) Once you've filtered your list, expanding the Content column will automatically append the tables you have left.

So far as I'm aware of, though, you can't feed anything in between the parens or create a list to limit the tables being returned. You need to either import and append the specific tables you want (like you've suggested), or connect, filter and expand, like I just described.

Hope that's a bit more complete.

 

[p45cal]

Thank you both!
I suspected that would be the case.

 

[Bob Phillips]

So far as I'm aware of, though, you can't feed anything in between the parens or create a list to limit the tables being returned.

As Ken says, but you could build a list of tables to retain in the filter, something like this

let
    _tables = {"Table1", "Table3"}, 
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains(_tables, [Name])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"Name", "Amt"}, {"Content.Name", "Content.Amt"})
in
    #"Expanded Content"

You could pass that list form Excel, that would be a trivial addition.

 

[Bill Szysz]

My proposal

let
    TblList = {"Table1","Table2"},
    Source = Table.Combine(Table.SelectRows(Excel.CurrentWorkbook(), each not List.IsEmpty(List.Intersect({{[Name]}, TblList})))[Content])
in
    Source

 

[Bob Phillips]

That's a nice emulation for 'In' in a list/array Bill.

 

[p45cal]

I'm probably trying to run before I can walk; I've been trying to create a .MatchesAny version of Bill's proposal and failed miserably - am I (mixing metaphors now) barking up the wrong tree?

 

[Bob Phillips]

You have 2 good solutions, List.Contains and List.Intersect, which return a table with your required tables. .MatchesAny returns a logical comparing values to a condition, why would you want to go this way?

 

[p45cal]

You have 2 good solutions, List.Contains and List.Intersect, which return a table with your required tables. .MatchesAny returns a logical comparing values to a condition, why would you want to go this way?

Only academic, in process of exploring/experimenting/learning and getting my head around the functions and M language - that's all.
Bill's proposal's List.IsEmpty also returns a logical but itself contains a List.Intersect, I was wondering if it could be done in one step with .MatchesAny.

 

[cyborgski]

So building on Bill's solution and p45cal's academic desire to get the statement into one line, I propose the following:

let
        Source = Table.Combine(Table.SelectRows(Excel.CurrentWorkbook(), each List.Contains({"Table1","Table2","Table3"},[Name]))[Content])
in
    Source

I expect that it could also be done with a MatchesAny.

 

[Bob Phillips]

Okay Pascal, in the interest of understanding )), I came up with this

let
    _tables= {"Table1","Table2"},
    Source = Table.Combine(Table.SelectRows(Excel.CurrentWorkbook(), each List.MatchesAny(List.Intersect({{[Name]}, _tables}), each true))[Content])
in
    Source

 

[p45cal]

Thank you all, this has helped.