Forum Discussion

ViorelCa's avatar
ViorelCa
Helper I
8 years ago
Solved

Power Query - duplicates table data after Custom Expand Data (import files from Folder)

Dear Coleagues,   I have to import 7 excel files (each with identical data structure, one for each Brand) from a folder and combine them into one single table, while maintaining the file name into ...
Need Help
ViorelCa's avatar
ViorelCa
Helper I
8 years ago

Dear Xiaoxin Sheng Anonymous,

 

Thanks a lot for your reply.

 

I have finally managed to solve the issue by applying different steps (by creating a function when importing one Excel file and then applying this function for importing the Folder with all Excel files), based on this solution:

 

http://datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/

 

Kind regards,
Viorel

  • Alex_Ooi's avatar
    Alex_Ooi
    Helper IV
    5 years ago

    Hi ViorelCa I just found out that Excel creates a hidden sheet each time you filter on a sheet and all though this sheet should not be available when retrieving the sheet names.

     

    Relevant discussion here: https://stackoverflow.com/questions/23034296/multi-sheet-import-with-oledb-netting-xlnm-filterdatabase-as-sheet-names

     

    I figured another solution out which does not require the use of custom function. In Mike's video, as suggested by Anonymous (https://www.youtube.com/watch?v=a7E29H5ZUmE), if you follow through the steps by expanding the function you created (Excel.Workbook) just like whatMike did at 2:36, you will see in your table something like xlnm.filterdatabase where the sheet is hidden.

     

    What I did was to simply filter where [kind] = "sheet" and the hidden filter is removed.

     

    Below is my code:

     

    let
    Source = Folder.Files("C:\Users\lenovo\Desktop\GQ GL"),
    #"Added Custom" = Table.AddColumn(Source, "GetData", each Excel.Workbook([Content], true)),
    #"Expanded GetData" = Table.ExpandTableColumn(#"Added Custom", "GetData", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered xlnm.filterdatabase" = Table.SelectRows(#"Expanded GetData", each ([Kind] = "Sheet")),

// after filtering them out, I remove other columns before expanding the table I exported from Excel
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered xlnm.filterdatabase",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", { ... }

     

     I hope others reading this thread in future would find this useful.