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

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.

Need Help

Anonymous
5 years ago
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.

6 Replies

PC2790
Community Champion
5 years ago
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
  Not applicable
  5 years ago
  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?
Anonymous
Not applicable
5 years ago
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
  Not applicable
  5 years ago
  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:
  - Anonymous
    Not applicable
    5 years ago
    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