none
[Excel Power Query] Getting the same sheet from multipile files in multipile folders? RRS feed

  • Question

  • Hi all

    I have multiple folders and sub folders, i.e.;

    - Financial Year
      - 2020
      - 2019
      - 2018

    In each folder there is a multi-sheet excel file

    "Transactions.xlsx"

    in each of the excel files there are 4 sheets

    - Shop 1
    - Shop 2
    - Shop 3
    - Shop 4

    Before July 2019 the naming convention was

    - Venue 1
    - Venue 2
    - Venue 3
    - Venue 4

    Since there is a difference in the format and column names / order (due to changes across time),  I think I will need to append multiple queries rather than merging them. 

    My question

    How can I Append get all sheets named  "shop 3" and "Venue 3" from all sub folders?

    Thank you for your help!

    Zen

    Wednesday, March 25, 2020 8:52 AM

Answers

  • Hi Zen

    No idea what you want to do after that... The following gives you a List of 4 Tables (= 4 sheets in each of your Transactions file). Each Table contains all sheet names that end with the same number (i.e. Shop 3,Venue 3, Foo 3...), combined/appended together. Hopefully the step names I used will help you understand and adapt

    let
        TopFolderPath = "D:\Downloads\FinancialYears",
        FolderAndSubFoldersContent = Folder.Files(TopFolderPath),
        SelectedTransactionsXlFiles = Table.SelectRows(FolderAndSubFoldersContent,
            each [Name] = "Transactions.xlsx"),
        ExcelBinariesOnly = Table.SelectColumns(SelectedTransactionsXlFiles,"Content"),
        AddedExcelBook = Table.AddColumn(ExcelBinariesOnly, "ExcelBooks",
            each Excel.Workbook([Content],true,null), type table
        ),
        RemovedContent = Table.SelectColumns(AddedExcelBook,{"ExcelBooks"}),
        CombinedExcelBooks = Table.SelectColumns(
            Table.Combine(RemovedContent[ExcelBooks]),
            {"Name","Data"}
        ),
        SheetNamesEndingWithNumCombined = List.Transform({"1".."4"},
            (txtNum)=>
                Table.Combine(
                    Table.SelectRows(CombinedExcelBooks,
                        each Text.EndsWith([Name], txtNum)
                    )[Data]
                )
        )
    in
        SheetNamesEndingWithNumCombined


    Thursday, March 26, 2020 1:09 PM

All replies

  • Hi Zen

    No idea what you want to do after that... The following gives you a List of 4 Tables (= 4 sheets in each of your Transactions file). Each Table contains all sheet names that end with the same number (i.e. Shop 3,Venue 3, Foo 3...), combined/appended together. Hopefully the step names I used will help you understand and adapt

    let
        TopFolderPath = "D:\Downloads\FinancialYears",
        FolderAndSubFoldersContent = Folder.Files(TopFolderPath),
        SelectedTransactionsXlFiles = Table.SelectRows(FolderAndSubFoldersContent,
            each [Name] = "Transactions.xlsx"),
        ExcelBinariesOnly = Table.SelectColumns(SelectedTransactionsXlFiles,"Content"),
        AddedExcelBook = Table.AddColumn(ExcelBinariesOnly, "ExcelBooks",
            each Excel.Workbook([Content],true,null), type table
        ),
        RemovedContent = Table.SelectColumns(AddedExcelBook,{"ExcelBooks"}),
        CombinedExcelBooks = Table.SelectColumns(
            Table.Combine(RemovedContent[ExcelBooks]),
            {"Name","Data"}
        ),
        SheetNamesEndingWithNumCombined = List.Transform({"1".."4"},
            (txtNum)=>
                Table.Combine(
                    Table.SelectRows(CombinedExcelBooks,
                        each Text.EndsWith([Name], txtNum)
                    )[Data]
                )
        )
    in
        SheetNamesEndingWithNumCombined


    Thursday, March 26, 2020 1:09 PM
  • Thanks Ehren !

    Wednesday, May 20, 2020 7:54 AM