Import Query - Sample File from Folder doesn't read data

[Anonymous]

Hi I have a folder with excel files that have multiple sheets in them which holds the same data table structure and I need to consolidate them.

I approach this as I do with normally - get data > folder > transfrom data > filter folder > combine files.

However the combine file query for the lastest file as a sample file shows no data.

When I load just that excel file alone data shows up. 

have pressed refresh preview multiple times but doesn't change anything. It doesn't make sense to me that the single excel loads fine but not the sample file for the folder.

Is there another approach I could do so that I can consolidate all the sheets for all excel file?

I'm open for any suggestions as long as it produce desired result.

Thanks in advance. 

[Vera_33 · Accepted solution]

@Anonymous 

I guess you use the button in Content column? If so, add a custom column instead, combine the tables in Custom column. Let me know if it is not the case.

[Anonymous]

Hi there I have done as you mentioned and expanded the custom column. This was good for filtering all the sheets I need. Brillant!

I am use to expanding the Content header and using the sample file to clean up data... so mynext road block is how would i remove rows which are headers and where files meet like so:

[Vera_33]

Hi @Anonymous 

Can you promote the header before combining them? If you filter down to the tables/sheets you need, then promote header first...assume your headers are all the same for all the tables/sheets you want to combine

Or you can filter out the header later

[Anonymous]

I figure just to use the date column and filter out anything with text

[PC2790]

Hello,

Can you please check if the files that your are using from within the folder have same structure for all of them?

Also, please share your Advanced Editor code for both the above cases to take a deeper look.

[Anonymous]

Hi you are correct the order of the sheets have changed over time due because of renaming the "Dalgety" sheet to "Landing" - to reflect real life changes.

Is there a way I can dynamically look for these two sheets and merge them as one in power query?