Forum Discussion

StoryofData's avatar
StoryofData
Helper III
1 year ago
Solved

SharePoint: Read all Files and All Tabs except certain tabs

Hi there,  I am trying to read all files in a sharepoint folder and all tabs within those .xlsx files, except tabs that contain "Summary". All file names are different and all tab names are diffe...
Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    1 year ago

    Something like this

     

    // Downloads
let
    Source = Folder.Files("C:\Users\xxx\Downloads"),
    #"Filtered Rows1" = Table.SelectRows(Source, each Text.StartsWith([Name], "Book") and [Extension] = ".xlsx"),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each Excel.Workbook([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name","Custom" }),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data", "Kind", "Name"}, {"Data", "Kind", "Name.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Name.1] <> "Summary")
in
    #"Filtered Rows"

     

     

     

     

lbendlin's avatar
lbendlin
Super User
1 year ago

Something like this

 

// Downloads
let
    Source = Folder.Files("C:\Users\xxx\Downloads"),
    #"Filtered Rows1" = Table.SelectRows(Source, each Text.StartsWith([Name], "Book") and [Extension] = ".xlsx"),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each Excel.Workbook([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name","Custom" }),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data", "Kind", "Name"}, {"Data", "Kind", "Name.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Name.1] <> "Summary")
in
    #"Filtered Rows"

 

 

 

 

StoryofData's avatar
StoryofData
Helper III
1 year ago

Never mind, I fugred it out! Thank you so much! 🙂