Condense code to one Excel.CurrentWorkbook statement

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,314
Reaction score
38
Points
48
Excel Version(s)
365
I currently have the likes of:
Code:
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:
Code:
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:
Code:
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.)
 
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. :(
 
Excel.CurrentWorkbook()

brings in all of the tables in the workbook, you can filter out any you don't want.
 
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. :)
 
Thank you both!
I suspected that would be the case.
 
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

Code:
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.
 
My proposal
Code:
let
    TblList = {"Table1","Table2"},
    Source = Table.Combine(Table.SelectRows(Excel.CurrentWorkbook(), each not List.IsEmpty(List.Intersect({{[Name]}, TblList})))[Content])
in
    Source
 
That's a nice emulation for 'In' in a list/array Bill.
 
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?
 
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?
 
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.
 
So building on Bill's solution and p45cal's academic desire to get the statement into one line, I propose the following:

Code:
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.
 
Last edited:
Okay Pascal, in the interest of understanding :))), I came up with this
Code:
let
    _tables= {"Table1","Table2"},
    Source = Table.Combine(Table.SelectRows(Excel.CurrentWorkbook(), each List.MatchesAny(List.Intersect({{[Name]}, _tables}), each true))[Content])
in
    Source
 
Thank you all, this has helped.
 
Back
Top