Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

https://community.fabric.microsoft.com/t5/Desktop/Expand-data-table-only-for-1st-sheet-without-specifying-sheet/m-p/4673667#M1397036   Please help and rewriting with my code only can be accepted as...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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
    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.


Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.


Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks but this part shown no data..

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

  

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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
    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.