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