So I have a workbook, with 30 odd pages. Each of the pages represents one day of the month, and each sheet contains one day's data.
Now each sheet has 20 odd columns, of which I am only interested in the first three.
So basically I have 30 sheets of 3 columns, which I would like to append into one long list.
The end result will be say, if 100 rows per sheet, then 100 rows x 30sheets x 3 cols = 3,000 rows of 3 cols.
So, I've got the data prepped, but how do I extract and append the content?
let
Source = SharePoint.Files("https://.......sharepoint.com/sites/.......", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each (Text.Contains([Extension],".xls"))),
#"Lowercased Text" = Table.TransformColumns(#"Filtered Rows",{{"Name", Text.Lower, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Lowercased Text","_"," ",Replacer.ReplaceText,{"Name"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Name", each Text.BeforeDelimiter(_, ".xls"), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date modified", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Text.Contains([Name], "planner")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "SourceFile", each [Folder Path]&[Name]&[Extension]),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Date modified", Order.Descending}}),
#"Part 1 filewords" = Table.AddColumn(#"Sorted Rows", "AllFilenameWords", each Text.Split([Name], " ")),
#"Part 2 MonthNameFound" = Table.AddColumn(#"Part 1 filewords", "MonthNameFound", each List.First( List.Intersect( { [AllFilenameWords], #"MonthNames"[MonthName] } ) )),
#"Part 3 YearFound" = Table.AddColumn(#"Part 2 MonthNameFound", "YearFound", each List.First( List.Intersect( { [AllFilenameWords], #"YearsOfInterest"[YearOfInterest] } ) )),
#"Removed Other Columns" = Table.SelectColumns(#"Part 3 YearFound",{"Name", "Date modified", "SourceFile", "MonthNameFound", "YearFound"}),
#"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"YearFound", Order.Descending}, {"MonthNameFound", Order.Descending}}),
#"Filtered Rows2" = Table.SelectRows(#"Sorted Rows1", each ([YearFound] = "2023" or [YearFound] = "2024") and ([MonthNameFound] <> null)),
#"Added Period" = Table.AddColumn(#"Filtered Rows2", "Period", each Date.EndOfMonth(Date.FromText("1 " & [MonthNameFound] & " " & [YearFound]))),
#"AddedSheetNames" = Table.AddColumn(#"Added Period", "SheetNames", each
let
Source = Excel.Workbook(Web.Contents([SourceFile]), null, true),
#"Filtered Rows1" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each (Text.Contains([Name],"-"))),
NamedSheets = Table.ToList(#"Filtered Rows")
in
#"NamedSheets"
)
in
#"AddedSheetNames"
I now have a table with a column containing a list of sheetnames.
How do I pull the data out?
