Getting newly added sheet data from multiple excel files

[jeongkim]

Hi,

I'm getting data from SharePoint folder for multiple excel fils(same format). 

I have an issue that latest excel file has new sheet added which doesn't exist in other earlier excel files.

I append the sheets into 1 data table. 

e.g.

Sharepoint folder: File A, File B, File C, File D

Sheet:

in File A, File B, File C : 2023, 2024

in File D : 2023, 2024, and 2025(newly added only in File D)

Should I separately add 2025 or new year(future) manually in query every single year? 

Or is there any way to get other sheets altogether(if the formats are the same)

Also more details from previous question:

https://community.fabric.microsoft.com/t5/Desktop/Getting-data-from-sharepoint-folder-when-have-new-...

[Anonymous]

hi @jeongkim ,

try below m query

let    Source = Excel.Workbook(File.Contents("Your SharePoint Folder Path"), null, true),
    Sheets = Source[Sheets],
    LoadSheets = Table.FromList(Sheets, Splitter.SplitByNothing(), {"SheetName"}, null, ExtraValues.Error),
    ExpandedContent = Table.ExpandTableColumn(LoadSheets, "Content", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
    ExpandedContent

[jeongkim]

Hi, let Source part doesn't work, could you rewrite the code with my current code?

let
Source = SharePoint.Contents("https://nokia.sharepoint.com/sites/korea-pmo-pbi-data-repository", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"KR PO Plan" = #"Shared Documents"{[Name="KR PO Plan"]}[Content],
SKT = #"KR PO Plan"{[Name="SKT"]}[Content],
#"CT SK_5G Bottom up plan" = SKT{[Name="CT SK_5G Bottom up plan"]}[Content],
#"Filtered Hidden Files1" = Table.SelectRows(#"CT SK_5G Bottom up plan", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)"))),
#"Sorted Rows" = Table.Sort(#"Expanded Table Column1",{{"Source.Name", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "Project")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column12", "Column24", "Column25", "Column26", "Column27", "Column28"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "Project"}, {"Column2", "Product"}, {"Column3", "Product Detail"}, {"Column4", "Category"}, {"Column5", "Item"}, {"Column6", "LE"}, {"Column7", "Material Code (BoQ level)"}, {"Column8", "Trim Material"}, {"Column9", "GIC"}, {"Column10", "Item Detail"}, {"Column11", "Unit Price(KRW)"}, {"Column13", "2024_1Q_QTY"}, {"Column14", "2024_1Q_OI"}, {"Column15", "2024_2Q_QTY"}, {"Column16", "2024_2Q_OI"}, {"Column17", "2024_3Q_QTY"}, {"Column18", "2024_3Q_OI"}, {"Column19", "2024_4Q_QTY"}, {"Column20", "2024_4Q_OI"}, {"Column21", "2024_FY_QTY"}, {"Column22", "2024_FY_OI"}, {"Column23", "Chk"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns3", "#", 1, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Source.Name", "Project", "Product", "Product Detail", "Category", "Item", "LE", "Material Code (BoQ level)", "Trim Material", "GIC", "Item Detail", "Unit Price(KRW)", "Chk", "#"}, "Attribute", "Value"),
#"Added Index1" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type)
in
#"Added Index1"