none
Combining all Excel tables whose name follow a certain pattern RRS feed

  • Question

  • Good afternoon,

    I have multiple tables from an excel workbook that I pull into Power Query and combine. The table names all follow the same pattern JB_Sales, LB_Sales, LRB_Sales, etc. Up to this point I have manually updated the M code when I needed to add a new table to the query. However, management wants to make this tool available for others to use, so I am hoping to find a way that I can dynamically add a table to a query when we have a new employee (e.g. another table to add that fits the pattern).

    Initially I was thinking that when a new user table was added, I would just run some VBA and add the table to the query. However, I have few mentions of this in my searches, so I suspect that either I am using incorrect search terms, or it is not a widely used or available practice. 

    I have recently read about some of the complex things people seem to be doing with M, is what I am trying to accomplish something that could be done in M? I have tried a few searches for ways to use M to do this, but I haven't read anything that seems to address my situation.

    Any help that you can provide will be greatly appreciated.

    Thanks for your time.

    Scead

    Tuesday, February 20, 2018 12:16 AM

Answers

  • When you import files from a folder in Power Query, you can easily select all *_Sales workbooks by using the next filter expression:

    Table.SelectRows(Source, each Text.EndsWith([Name], "_Sales.xlsx"))

    or just using "Ends with..." option from "Text Filters" in filtering dropdown menu of the "Name" colum.


    Maxim Zelensky Excel Inside

    Tuesday, February 20, 2018 7:20 AM

All replies

  • When you import files from a folder in Power Query, you can easily select all *_Sales workbooks by using the next filter expression:

    Table.SelectRows(Source, each Text.EndsWith([Name], "_Sales.xlsx"))

    or just using "Ends with..." option from "Text Filters" in filtering dropdown menu of the "Name" colum.


    Maxim Zelensky Excel Inside

    Tuesday, February 20, 2018 7:20 AM
  • Thanks for the advice. My issue isn't with loading in different worksheets, but tables from within one file. While this didn't directly address my issue, I was able to use it as a jumping-off point. I was able combine it with Excel.Workbook and Table.Select to load data from all of the desired tables.

    Thanks again!

    Wednesday, February 21, 2018 3:37 AM
  • I just thought I would post the actual code that I used, in case someone else might find it useful.

    #"All Sales" = Table.Combine(Table.SelectRows(Excel.CurrentWorkbook(), each Text.EndsWith([Name], "_Sales"))[Content])
    Tuesday, February 27, 2018 7:06 AM