Forum Discussion
Expand data table only for 1st sheet without specifying sheet name_Re-post
Please help and rewriting with my code only can be accepted as solution since I'm not advanced user.. so hard to understand.
Hi Anonymous ,
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
CleanedRename 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.
6 Replies
Hi Anonymous ,
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.Hi Anonymous ,
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
CleanedRename 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.
Hi Anonymous ,
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.