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

[ViorelCa]

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

I do this:

1. Import from folder (after removing "Other Columns I remain with 2 columns: Content (Binary) and Name (Name of file). I have 7 rows.

2. Add Cusotm Column with "=Excel.Workbook([Content])". Now I get the 3rd Column called Custom  (Table). I have 7 rows.

3. When I click the double arrow in Custom/Expand and select only Data, each row is tripled, and now I have 21 rows (so the content of each Brand is multiplied by 3).

What can be done or is there another way to do this in Power query?

Many thanks!

Viorel

[v-shex-msft]

HI @ViorelCa,

Please provide more detail contents to help us clarify your scenario.

BTW, you can refer to below video to know how to combine multiple excel files:

Excel Power Query #06: Import Multiple Excel Files From Folder Into One Excel Table or Data Model

Regards,

Xiaoxin Sheng

[ViorelCa · Accepted solution]

Dear Xiaoxin Sheng @v-shex-msft,

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

Kind regards,
Viorel

[Alex_Ooi]

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

I figured another solution out which does not require the use of custom function. In Mike's video, as suggested by @v-shex-msft (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.