cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ladyhaley
Resolver I
Resolver I

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.

Folder.PNG

 

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

 

Excel Import.PNG

 

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. 

 

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

@ladyhaley 

 

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.

 

Vera_33_0-1614909057620.png

 

View solution in original post

6 REPLIES 6
Vera_33
Super User
Super User

@ladyhaley 

 

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.

 

Vera_33_0-1614909057620.png

 

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:

ladyhaley_0-1614910395680.png

 

 

Hi @ladyhaley 

 

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

 

Vera_33_0-1614911447745.png

Or you can filter out the header later

Vera_33_1-1614911524193.png

 

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

PC2790
Super User
Super User

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.

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.

 

ladyhaley_0-1614908795054.png

 

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors