Expand data table only for 1st sheet without specifying sheet name_Re-post

[jeongkim]

https://community.fabric.microsoft.com/t5/Desktop/Expand-data-table-only-for-1st-sheet-without-speci...

Please help and rewriting with my code only can be accepted as solution since I'm not advanced user.. so hard to understand. 

[v-pagayam-msft]

Hi @jeongkim ,
I hope the suggested solution worked for you. If your issue is resolved, kindly accept the post as a solution — it helps the community identify helpful answers more easily.
Thank you.

[v-pagayam-msft]

Hi @jeongkim ,
Thank you for reaching out to us on Microsoft Fabric Community Forum!

1.Get the excel files. And add a new column that extracts the first sheet from each Excel file.

ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx") or Text.EndsWith([Extension], ".xls"))

AddFirstSheet = Table.AddColumn(ExcelFiles, "FirstSheet", each try Excel.Workbook([Content], null, true){0}[Data] otherwise null)

2.Remove any files where the first sheet couldn’t be accessed.

RemoveNulls = Table.SelectRows(AddFirstSheet, each [FirstSheet] <> null)

3.Expand the contents of the first sheet into your main table.

ExpandedData = Table.ExpandTableColumn(RemoveNulls, "FirstSheet", Table.ColumnNames(RemoveNulls{0}[FirstSheet]))

4.Select the sheet data and file name for traceability.

FinalTable = Table.SelectColumns(ExpandedData, {"Name"} & Table.ColumnNames(RemoveNulls{0}[FirstSheet]))

Now, you may get one combined table showing the contents of the first sheet from each Excel file, along with the file name. 

I hope this helps you in better understanding.If so ,give us kudos and consider accepting it as solution.If still require further assistance,fell free to reachout!

Regards,
Pallavi G.

[jeongkim]

Thanks but this part shown no data..

AddFirstSheet = Table.AddColumn(ExcelFiles, "FirstSheet", each try Excel.Workbook([Content], null, true){0}[Data] otherwise null)

[v-pagayam-msft]

Hi @jeongkim ,
Thank you for the follow-up.I would be happy to assist you!

Please use the below in transfrom data ->advance editor then close and apply.

let
// Replace this with your folder path
Source = Folder.Files("C:\Users\v-pagayam\OneDrive - Microsoft\New folder"),


ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx") or Text.EndsWith([Extension], ".xls")),


AddFirstSheet = Table.AddColumn(ExcelFiles, "FirstSheet", each 
let
wb = Excel.Workbook([Content], null, true),
visibleSheets = Table.SelectRows(wb, each [Kind] = "Sheet" and [Hidden]? <> true),
firstSheet = if Table.RowCount(visibleSheets) > 0 then visibleSheets{0}[Data] else null
in
firstSheet),


RemoveNulls = Table.SelectRows(AddFirstSheet, each [FirstSheet] <> null),


Expanded = Table.ExpandTableColumn(RemoveNulls, "FirstSheet", Table.ColumnNames(RemoveNulls{0}[FirstSheet])),


FinalRaw = Table.SelectColumns(Expanded, {"Name"} & Table.ColumnNames(RemoveNulls{0}[FirstSheet])),


Cleaned = Table.SelectRows(FinalRaw, each [Column1] <> "Sales" and [Column2] <> "Quantity")
in
Cleaned

Rename the columns as per the requirement. Please refer the screenshot and file for detailed understanding.

If this answer meet your requirement,consider accepting it as solution.If still facing difficulty in resolving the issue, feel free to reachout us!

Regards,
Pallavi.