I am trying to figure out how to:
1. Import all of the Zip files in folder
2. Read each zip file which/should have 50 identical excel files in it.
3. Transfrom the "Glance" Worksheet within each file.
I've made some progress, and I can import a folder, read each zip file and get a table of all of the Excel files. But from there I'm sorta stuck on how to then transform and combine just the 'Glance' Worksheet from each excel file (inside the zip file).
So in the below, you will find this line here: #"Custom Content" = #"Added Custom1"{1}[Custom.Content],
I can change that {1} manualy to a differenty row in the table and the remaining code will continue on. But I want to be able to loop through all of the rows in the table and then combine it all. Currently, I have 12 months of zip files, 50 files in each for 600 files and growing. I'm guessing I need to something with List??
let
path = "C:\Users\ptm\Desktop\StarReports",
Source = Folder.Files(path),
#"Filtered Rows to only .zip" = Table.SelectRows(Source, each ([Extension] = ".zip")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows to only .zip",{"Content"}),
#"Added Custom UnzipContents" = Table.AddColumn(#"Removed Other Columns", "Custom", each ZipFunction([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom UnzipContents", "Custom", {"FileName", "Content"}, {"Custom.FileName", "Custom.Content"}),
#"Added Custom" = Table.AddColumn(#"Expanded Custom", "StarID", each Text.BetweenDelimiters([Custom.FileName],"_","-")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Text.BetweenDelimiters([Custom.FileName],"-","-")),
#"Custom Content" = #"Added Custom1"{1}[Custom.Content],
#"Imported Excel Workbook" = Excel.Workbook(#"Custom Content"),
#"Glance!_xlnm Print_Area_DefinedName" = #"Imported Excel Workbook"{[Item="Glance!_xlnm.Print_Area",Kind="DefinedName"]}[Data],
.....Move Transform step.....
in
#"Unpivoted Columns"
I already did similar by having each months zip file extracted and into a folder. And then with a function/template, read all the files and combine. But I'm trying now to learn how to possibly read and transform all the files directly from the zip archives.
Can anyone help with an idea or solution?
Thank you,
PT
