Can we get data from multiple excel files in sharepoint folder regardless of sheet name when formats

[jeongkim]

Hi,

Can we get data from multiple excel files in sharepoint folder regardless of sheet name when formats are the same?

Below is my current M code and this doesn't fetch data when new sheet name is added. 

My files have new sheet with every year number so hope to get them dynamically without manual add.  

*the files have many of hidden sheets(old year) so need to filter only shown sheets. 

How can we exclude hidden sheets? below code get all the sheets including hidden ones which not necessary.

FYI, my current M code below

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)"))),

...

Transform File (6)

[lbendlin]

yes, you can enumerate the sheets and then combine the data from all of the sheets in a single result, You can expand that idea across Excel files as well.

please post at least two sample Excel files.

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...