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


I am trying to accomplish this in advanced editor: 

let

    Source = SharePoint.Files("https:Path ", [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https:Path")),

   

    KeepCol = Table.SelectColumns(Source,{"Name"}),

    Sheet_Info = Table.AddColumn(KeepCol, "Personalise", each Excel.Workbook(File.Contents("https:Path" & [Name])),

 

    ExpandSheet = Table.ExpandTableColumn(Sheet_Info, "Personalise", {"Name", "Data"}, {"Name.1", "Data"}),

    Test = Table.AddColumn(ExpandSheet, "Flag", each Text.Contains([Name.1],"Summary")),

    KeepTrue = Table.SelectRows(Test, each ([Flag] = true)),

    Develop = Table.ExpandTableColumn(KeepTrue, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}))

 

    in

     #"Filtered Rows"

 

but my syntax is not working, can someone please help me? 

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"

     

     

     

     

3 Replies

  • 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! 🙂

  • StoryofData's avatar
    StoryofData
    Helper III
    1 year ago

    lbendlin Thank you for your reply 🙂 I added your syntax in, with the exception of last #Filtered Rows was changed to #Filtered Rowss because it gave me an error of "The variable named 'Filtered Rows' is already defined in scope. 

    I am experiencing issue at Filtered Rows1 step, after this,table shows up empty.