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. 

[Anonymous]

Hi @jeongkim ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.

[Anonymous]

Hi @jeongkim ,
I wanted to follow up on the previous suggestions regarding the issue. We would like to hear back from you to ensure we can assist you further.
If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.

Regards,
Pallavi.

[Anonymous]

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.

[Anonymous]

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)

[Anonymous · Accepted solution]

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.