Forum Discussion

Peter_Yaacoub's avatar
Peter_Yaacoub
Frequent Visitor
4 years ago
Solved

Combine selected worksheets from workbook in Folder

Hi everyone!  New to power query and I need some help please. I am currently connected to a folder and I want to apply my transformations to only a certain number of worksheets in that workbook tha...
General Comment
Need Help
Tips and Tricks
  • jennratten's avatar
    jennratten
    4 years ago

    To select multiple sheets by name, you could do it like this:

        #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ([Name.1] = "CPMI" or [Name.1] = "Sheet2" or [Name.1] = "Sheet3") ),

    or like this:

        #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ( List.Contains({"CPMI", "Sheet2", "Sheet3"}, [Name.1] ) ) ),
jennratten's avatar
jennratten
Icon for Super User rankSuper User
4 years ago

Hello - you would need to edit the step below in your script with the criteria that selects the sheets to be included.  

    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ([Name.1] = "CPMI")),

For example, if you want to include all sheets that have names beginning with CPMI, regardless of whether the letters CPMI were uppercase or lowercase, you could do this:

    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and Text.Contains ([Name.1], "CPMI", Comparer.OrdinalIgnoreCase) ),

On your #"Expanded Data" all the sheets will be combined and expanded.  

 

 

  • Peter_Yaacoub's avatar
    Peter_Yaacoub
    Frequent Visitor
    4 years ago

    Thank you for your response Jenn.

    I would like to select multiple worksheets by name referring to column Name.1 instead of referring to certain characters if that is possible.

     

    Thanks again.

     

    Peter

    • jennratten's avatar
      jennratten
      Icon for Super User rankSuper User
      4 years ago

      To select multiple sheets by name, you could do it like this:

          #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ([Name.1] = "CPMI" or [Name.1] = "Sheet2" or [Name.1] = "Sheet3") ),

      or like this:

          #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ( List.Contains({"CPMI", "Sheet2", "Sheet3"}, [Name.1] ) ) ),