Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Condense code to one Excel.CurrentWorkbook statement

  1. #1
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365

    Post Condense code to one Excel.CurrentWorkbook statement



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.)

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by p45cal View Post
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,670
    Articles
    0
    Excel Version
    O365
    Excel.CurrentWorkbook()

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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365
    Thank you both!
    I suspected that would be the case.

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,670
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by Ken Puls View Post
    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.

  7. #7
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    102
    Articles
    0
    Excel Version
    Excel 365
    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

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,670
    Articles
    0
    Excel Version
    O365
    That's a nice emulation for 'In' in a list/array Bill.

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365
    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?

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,670
    Articles
    0
    Excel Version
    O365
    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?

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •